Mysql how to join related table by '[id, anotherId]' format-CodePudding

I have table with field related_table_ids (example value ["1110663703","1958153258"]). Tried join related table by query -

SELECT * 
FROM db.table 
INNER JOIN related_table ON related_table.id IN (
  SUBSTRING(table.related_table_ids, 2, (LENGTH(table.related_table_ids) - 2))
)

But that doesn't work, returns 0 rows. help pls!

table

id | related_table_ids
---------
1  | ["1110663703","1958153258"]
2  | ["2032453865"]
3  | ["1304031696"]

related_table

CodePudding user response：

related_table_ids looks like a json string. Therefore, you can use the JSON_CONTAINS function.

SELECT *
FROM mytable t
JOIN related_table rt ON JSON_CONTAINS(related_table_ids, CONCAT('"', rt.id, '"'))

db<>fiddle