MySQL Forums
Forum List  »  Connector/Node.js

extract attribute (with special character in name) value from json-typed column
Posted by: harry sheng
Date: October 25, 2015 07:38PM

Hi,

I need to store json object in a json-typed column in MySQL 5.7.9.
One of the json object attribute name is "@xsi:type", here is the script to create the table:

create table samo_objects (
connector_id bigint,
oid varchar(255) generated always as (json_extract(object, '$.objectFullName')),
type varchar(128) generated always as (json_extract(object, '$.@xsi:type')),
displayedName varchar(80) generated always as (json_extract(object, '$.displayedName')),
description varchar(80) generated always as (json_extract(object, '$.description')),
object json,
unique key pk_samo (oid),
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

But the following insert statement gives me an error:

insert into samo_objects (object) values ('{"objectFullName": "svc-mgr:2314", "displayedName": "epipe 1", "description": "epipe 1", "@xsi:type": "epipe.Epipe"}');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 11 in '$.@xsi:type'.

What is the correct syntax to create the virtual column on "@xsi:type" attribute?

Options: ReplyQuote


Subject
Written By
Posted
extract attribute (with special character in name) value from json-typed column
October 25, 2015 07:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.