SQL json extract where in array

Question

I have users table with json type attributes column:

Example value:

attributes: {"connection": "HTTP"},
attributes: {"connection": "API"}

When I tried get using WHERE IN() with one param all works correctly:

SELECT * FROM users WHERE JSON_EXTRACT(attributes, '$.connection') IN ("HTTP");

But when I tried with multiple value it's doesn't work for me:

SELECT * FROM users WHERE JSON_EXTRACT(attributes, '$.connection') IN ("API", "HTTP");

In this case return nothing. How I can get users which has one of defined connections inside WHERE IN() values?

seems to work here: dbfiddle.uk/… have you tried selecting JSON_EXTRACT(attributes, '$.connection') from the records you think should match and verified you are getting what you expect? — ysth
– ysth, Commented Dec 1, 2020 at 5:57
Thanks for answer! Really your code work on MySQL 8 but my version is MySQL 5.7 and therefore doesn't work. How can this be done in this version? @ysth — Andreas Hunter
– Andreas Hunter, Commented Dec 1, 2020 at 6:29

ysth · Accepted Answer · 2020-12-01 13:08:03Z

3

In MySQL 5.7 (possibly only later 5.7 versions; I saw some indications that some JSON_EXTRACT behavior changed in 5.7.10 or 5.7.11), it does not appear to cast the JSON value returned by JSON_EXTRACT to a string when used as the left operand to IN. This will work in both 5.7 and 8.0:

SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.connection')) in ('HTTP','API');

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ea3e311a775cf30e393b6ac07b7fde30

answered Dec 1, 2020 at 13:08

ysth

99.1k6 gold badges126 silver badges219 bronze badges

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

Collectives™ on Stack Overflow

SQL json extract where in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related