I am looking for records in table 2 whose id exist in the values of nums field (JSON) in table 1.
table1
id | nums (JSON)
---+-------------------
1 | ["1","2","3","4"]
2 | ["7","8","5","6"]
3 | ["9","10","3","4"]
table2
id |
---+
1 |
2 |
53 |
63 |
I would like to get the next result.
rows desired
id |
---+
1 |
2 |
I am using 5.7 mysql version.
Try this,
SELECT * FROM table1 as t1
LEFT JOIN table 2 as t2 on JSON_CONTAINS(t1.id->'$[*]', CAST(t2.id as JSON))
If I understand correctly:
select t2.id
from table2 t2
where exists (select 1
from table1
where json_contains(nums, t2.id)
);
You may need to cast the second argument to a string.
CAST(t2.id as CHAR(50)) also.Another option would be to just cast your JSON arrays to text, and then use REGEXP to search for matching ids:
SELECT *
FROM table2 t2
WHERE EXISTS (SELECT 1 FROM table1 t1
WHERE CAST(t1.nums AS CHAR(50))
REGEXP CONCAT('[[:<:]]', CAST(t2.id AS CHAR(50)), '[[:>:]]');
