Re: Query have Null find by virtual column. Why?
Posted by: Alfredo Kengi Kojima
Date: December 02, 2016 12:26AM

Hi

JSON_EXTRACT() returns a JSON value, which means that the value returned in your example is actually '"Parto"'. However, the = operator will automatically promote both sides of the operation to the same type, so your comparison works as you expect.

OTOH in the generated column, you're casting the JSON value directly to a string, which means that the value you get there is '"Parto"'. To make that work, you would need to use JSON_UNQUOTE() to do the proper conversion:

CREATE TABLE `test` (
`emp_no` int(11) NOT NULL,
`data` json NOT NULL,
`name` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,'$.first_name'))) VIRTUAL,
PRIMARY KEY (`emp_no`)
)

You can also use the inline syntax:

CREATE TABLE `test` (
`emp_no` int(11) NOT NULL,
`data` json NOT NULL,
`name` varchar(30) GENERATED ALWAYS AS data->>'$.first_name') VIRTUAL,
PRIMARY KEY (`emp_no`)
)

--
Alfredo Kojima
MySQL Developer Tools

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query have Null find by virtual column. Why?
186
December 02, 2016 12:26AM


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.