MySQL query WHERE IN for json array

Question

I have saved data for a column in json form:

{"sp_id": [1, 2,113],"spd_id": [],"spclg_id": []}

I would like to use WHERE IN to check if the sp_id field contains the values specified in the array

i.e. I want to check whether there is at least one occurrence of the given values in the u_id array.

At least one element for sp_id must match
must be in mysql
mysql version: 5.7.40

in php it would look like this:

if(count(array_intersect($needle, $haystack)))

There is no `u_id` in your JSON, which makes your question rather unclear. — GMB, May 25 '23 at 13:35
Please share more details, like the table structure, sample input data, the expected output, and your attempts to resolve the problem — Nico Haase, May 25 '23 at 13:38
You want to do this directly in mysql using `where` clause? and all items must match e.g. `[1, 2, 113]` -> match, `[1, 2]` -> no match? — Salman A, May 25 '23 at 13:41
@SalmanA at least one element must match, must be in mysql, cannot be in a loop in php — ninka436, May 25 '23 at 13:45
Think you are looking for https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains — user3783243, May 25 '23 at 13:51
@user3783243 checks if there is only one value for a field, I need to check if there is at least one value from the given ones — ninka436, May 25 '23 at 13:55
Out of curiosity, why did you store these data in JSON? It would have been easy to query if you had stored multi-valued attributes as normal rows and columns. — Bill Karwin, May 25 '23 at 14:11
@ninka436 That's the same thing, no? If you have 1 value than you have at least 1 value.... or you have `$haystack` which is an array of ids? — user3783243, May 25 '23 at 14:12

Salman A · Accepted Answer · 2023-05-25T14:19:25.110

0

You can use JSON_CONTAINS function which, unfortunately, has some limitations. You need to check items one by one:

-- e.g. when you need to check if [1, 9] intersects

WHERE JSON_CONTAINS('{"sp_id": [1, 2, 113], "spd_id": [], "spclg_id": []}', '1', '$.sp_id') -- true
OR    JSON_CONTAINS('{"sp_id": [1, 2, 113], "spd_id": [], "spclg_id": []}', '9', '$.sp_id') -- false

The better solution is to use JSON_TABLE which requires MySQL 8 or later:

SELECT *
FROM t
WHERE id IN (
    SELECT id
    FROM JSON_TABLE(t.json, '$.sp_id[*]' COLUMNS(
        val INT PATH '$'
    )) AS x
    WHERE val IN (1, 9)
)

DB<>Fiddle

edited May 25 '23 at 14:19

answered May 25 '23 at 13:54

Salman A

262,204
82
430
521

1

only I can have a lot of these values, that's why I wanted to avoid using JSON_CONTAINS... And what would the use of OPENJSON look like? I can't find this function in the documentation, this function is available for mysql? – ninka436 May 25 '23 at 13:59
Sorry it was JSON_TABLE https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html, but your hands are tied. `JSON_SEARCH` can do exact match of `[1]`, `[1, 2]`, `[1, 2, 113]` but not `[1, 9]`. – Salman A May 25 '23 at 14:02
We are currently planning to upgrade the company – ninka436 May 25 '23 at 14:03
You will show me what ```JSON_TABLE``` will look like in my case? – ninka436 May 25 '23 at 14:08
See revised answer @ninka436 – Salman A May 25 '23 at 14:19
@ninka436: Note that `JSON_TABLE()` is not implemented in MySQL 5.7. You said you're currently using MySQL 5.7, so you would have to upgrade. But MySQL 5.7 will reach end of support in October of this year, so you should probably be planning to upgrade anyway. – Bill Karwin May 25 '23 at 14:38
thank you very much, now I only have a performance problem, jsons don't work for INDEX, won't such a query kill the database? – ninka436 May 25 '23 at 14:54
1

I'd rather not store the data as json if it is used in where clause. – Salman A May 25 '23 at 16:23

MySQL query WHERE IN for json array

1 Answers1