MySQL, Select records based on values in JSON arra

问题:

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...

回答1:

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

回答2:

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.

回答3:

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

回答4:

Wrap your select to JSON_ARRAYAGG

Like:

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