MySQL Forums
Forum List  »  InnoDB

Re: How to extract "@xsi:type" attribute from json-type column
Posted by: harry sheng
Date: October 26, 2015 12:00PM

OK, here is the correct syntax (quote the attribute name) to use:

select json_extract(json_object, '$."@xsi:type"') from json_table;

But I have another question,

CREATE TABLE `test_table` (
`type` varchar(255) GENERATED ALWAYS AS (json_extract(object, '$."@xsi:type"')) VIRTUAL,
`oid` varchar(255) GENERATED ALWAYS AS (json_extract(object, '$."objectFullName"')) VIRTUAL,
`serviceId` varchar(20) GENERATED ALWAYS AS (json_extract(object, '$."serviceId"')) VIRTUAL,
`displayedName` varchar(80) GENERATED ALWAYS AS (json_extract(object, '$."displayedName"')) VIRTUAL,
`description` varchar(256) GENERATED ALWAYS AS (json_extract(object, '$."description"')) VIRTUAL,
`object` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I insert data,

insert into test_table (object) value ('{"@xsi:type": "double", "objectFullName": "oid-132", ...}');

I can find the above inserted record with this statement:

select * from test_table where json_extract(object, '$.objectFullName')='oid-132';

but not this statement:

select * from test_table where oid='oid-132';

I have to use this statement to find it (have to double-quote the json value):

select * from test_table where oid='"oid-132"';

This is incorrect, IMO.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to extract "@xsi:type" attribute from json-type column
1126
October 26, 2015 12:00PM


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.