behaviour of MySQL 'IN' operator when fed with JSON values

Question

This statement

select 'foo' in ('foo', 'bar');

returns 1 as expected.

This

select json_extract('["foo"]', '$[0]') = 'foo';

also returns 1 as expected.

However, this

 select json_extract('["foo"]', '$[0]') in ('foo', 'bar');

unuexpectedly returns 0. Even more strangely, if written with additional quotes

select json_extract('["foo"]', '$[0]') in ('"foo"', '"bar"');

it returns 1 instead. But this does not work with simple equality, as

select json_extract('["foo"]', '$[0]') = '"foo"';

returns 0.

So the question is: why "IN" and "=" behave differently only when fed with JSON results?

select json_extract('["foo"]', '$[0]') in ('foo', 'bar'); returns 1 on my system.... (MySQL8.0.25/Windows) — Luuk
– Luuk, Commented Jun 29, 2021 at 8:56

Akina · Accepted Answer · 2021-06-29 09:06:46Z

3

Version-dependent. MySQL 5.7 needs in additional JSON_UNQUOTE().

answered Jun 29, 2021 at 9:06

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

1 Answer 1