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.
sp_id must matchin php it would look like this:
if(count(array_intersect($needle, $haystack)))
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)
)
JSON_SEARCH can do exact match of [1], [1, 2], [1, 2, 113] but not [1, 9].JSON_TABLE will look like in my case?
u_idin your JSON, which makes your question rather unclear.