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

Answer 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