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

Answer 1

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