0

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.

StackExploded
  • 539
  • 7
  • 17
  • study here, you're missing something https://database.guide/json_extract-return-data-from-a-json-document-in-mysql/ – Ericgit Dec 19 '19 at 03:07

1 Answers1

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"');
Ed Bangga
  • 12,879
  • 4
  • 16
  • 30