Mysql: where in returns no rows when used with json_extract

Question

I am somewhat puzzled with the following simple statements.

create table Test(id integer, data json);
insert into Test(id, data) values(1, '{"name": "vova"}');

select * from Test
where  json_extract(data, "$.name") IN ("vova", "mark");

Here select returns nothing. However, the query returns the expected row if i leave a single element in the array:

select * from Test
where  json_extract(data, "$.name") IN ("vova");

'json_extract' and 'where in' dont seem to like each other? Or i'm probably missing something?

Here is a link with an example. Behaviour is the same when i run the queries locally.

study here, you're missing something database.guide/…

Ericgit
– Ericgit

2019-12-19 03:07:28 +00:00
Commented Dec 19, 2019 at 3:07 — Ericgit
– Ericgit, Commented Dec 19, 2019 at 3:07

Ed Bangga · Accepted Answer · 2019-12-19 03:14:40Z

1

if you try to evaluate

json_extract(data, "$.name")

-> this will result to "vova"

meaning is with double quotes, treat your IN operator for this scenario as to select the string values, add single quotes.

select * from Test
where  json_extract(data, "$.name") IN ('"vova"', '"mark"');

edited Dec 19, 2019 at 3:14

answered Dec 19, 2019 at 3:07

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StackExploded Over a year ago

oo, yeas, of course... Wonder why it correctly matches/not matches if there is a single element

Collectives™ on Stack Overflow

Mysql: where in returns no rows when used with json_extract

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related