Join JSON array field with string field in mysql

Question

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.

Answer 1

Try this,

SELECT * FROM table1 as t1
    LEFT JOIN table 2 as t2 on JSON_CONTAINS(t1.id->'$[*]', CAST(t2.id as JSON))