MySQL, Select records based on values in JSON array

Question

I'm still pretty new to handling JSON fields in MySQL. All the solutions I've come across deal with objects that have key/values; unable to find one that handles JSON arrays.

Anyways, what I want to do is to be able to select all rows where the interestIds contain 2 in them. How do I do that? Thanks.

Users table

+----+-------------+
| id | interestIds |
+----+-------------+
|  1 | [1, 2]      |
|  2 | [3, 2]      |
|  3 | [2, 4]      |
+----+-------------+

Sample test query:

SET @userId = 2;
SELECT * FROM Users
WHERE @userId IN JSON_CONTAINS(@user, interestIds, '$[1]');

I am confused as how to use the JSON_* functions; not sure what to put for the 3rd parameter...

Sebastian Brosch · Accepted Answer · 2019-09-15 10:56:40Z

5

You can use the following solution, using JSON_CONTAINS:

SELECT * 
FROM Users
WHERE JSON_CONTAINS(interestIds, '2') = 1;

The third (optional) paramater path gives you the posibility to use this function only on a specific part of your JSON value. So the following example checks if 2 is the second value of the array:

SELECT *
FROM test
WHERE JSON_CONTAINS(interestIds, '2', '$[1]') = 1;

demo on dbfiddle.uk

edited Sep 15, 2019 at 10:56

answered Aug 16, 2018 at 9:17

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

Comments

Kamil Gosciminski · Accepted Answer · 2018-08-16 09:22:46Z

4

Use JSON_SEARCH which returns path to element you are searching, or null if not found:

SELECT *
FROM users
WHERE JSON_SEARCH(interestids, 'one', '2') IS NOT NULL

Live Demo

If you're storing many-to-many relationship using simple JSON array, there are better ways to do it. Consider creating user_interest table and doing it the right and simpler way. That is if your JSON actually looks like you have shown us and does not contain dynamic key-value pairs.

edited Aug 16, 2018 at 9:22

answered Aug 16, 2018 at 9:17

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

1 Comment

timmah.faase Over a year ago

The question contains JSON where the interestIds are array of integers not strings. JSON_SEARCH does not work on integers

Dave Stokes · Accepted Answer · 2018-08-16 17:53:43Z

1

SQL> select id 
from users 
where JSON_CONTAINS(interestIds, "2","$");
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.0015 sec)

answered Aug 16, 2018 at 17:53

Dave Stokes

8235 silver badges11 bronze badges

Comments

Andrey Mashukov · Accepted Answer · 2019-01-15 22:22:27Z

0

Wrap your select to JSON_ARRAYAGG

Like:

SELECT JSON_ARRAYAGG(JSON_OBJECT(....)) FROM table....

answered Jan 15, 2019 at 22:22

Andrey Mashukov

117 bronze badges

1 Comment

袁文涛 Over a year ago

only suppor above 5.7.22

Collectives™ on Stack Overflow

MySQL, Select records based on values in JSON array

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related