CODEWITHSUNDEEP
mysqljsondatabaseselectmysql-5.7
Davina Leong
Davina Leong

Reputation: 767

MySQL, Select records based on values in JSON array

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

Upvotes: 7

Views: 14632

Answers (4)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

Upvotes: 5

Andrey Mashukov
Andrey Mashukov

Reputation: 11

Wrap your select to JSON_ARRAYAGG

Like:

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

Upvotes: 0

Dave Stokes
Dave Stokes

Reputation: 823

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

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 4

Related Questions