MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(id int, json VARCHAR(200));
INSERT INTO t VALUES
(1, '{"sp_id": [1, 2, 113], "spd_id": [], "spclg_id": []}'),
(2, '{"sp_id": [9, 2, 113], "spd_id": [], "spclg_id": []}'),
(3, '{"sp_id": [7, 8, 113], "spd_id": [], "spclg_id": []}');

Records: 3  Duplicates: 0  Warnings: 0


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


id
json


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

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

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