Search code examples
mysqlwhere-injson-extract

Mysql: where in returns no rows when used with json_extract

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.

Solution

  • 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"');