CODEWITHSUNDEEP
mysqlsqljson
J. Ordaz
J. Ordaz

Reputation: 423

Join JSON array field with string field in mysql

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.

Upvotes: 3

Views: 9455

Answers (3)

Crysis
Crysis

Reputation: 418

Try this, 

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

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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)), '[[:>:]]');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Related Questions