mySQL WHERE IN from JSON Array

Question

I have a table with JSON data in it, and a statement that pulls out an array of ID's for each row...

SELECT items.data->"$.matrix[*].id" as ids
FROM items

This results in something like..

+------------+
|    ids     |
+------------+
| [1,2,3]    |
+------------+

Next I want to select from another table where the ID of that other table is in the array, similar to the WHERE id IN ('1,2,3') but using the JSON array...

Something along the lines of...

SELECT * FROM other_items 
WHERE id IN ( 
  SELECT items.data->"$.matrix[*].id" FROM items
);

but it needs some JSON magic and I cant work it out...

Answer 1

Below is a complete answer. You may want a 'use <db_name>;' statement at the top of the script. The point is to show that JSON_CONTAINS() may be used to achieve the desired join.

DROP TABLE IF EXISTS `tmp_items`;
DROP TABLE IF EXISTS `tmp_other_items`;

CREATE TABLE `tmp_items` (`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT, `data` json NOT NULL);
CREATE TABLE `tmp_other_items` (`id` int NOT NULL, `text` nvarchar(30) NOT NULL);

INSERT INTO `tmp_items` (`data`) 
VALUES 
    ('{ "matrix": [ { "id": 11 }, { "id": 12 }, { "id": 13 } ] }')
,   ('{ "matrix": [ { "id": 21 }, { "id": 22 }, { "id": 23 }, { "id": 24 } ] }')
,   ('{ "matrix": [ { "id": 31 }, { "id": 32 }, { "id": 33 }, { "id": 34 }, { "id": 35 } ] }')
;

INSERT INTO `tmp_other_items` (`id`, `text`) 
VALUES 
    (11, 'text for 11')
,   (12, 'text for 12')
,   (13, 'text for 13')
,   (14, 'text for 14 - never retrieved')
,   (21, 'text for 21')
,   (22, 'text for 22')
-- etc...
;

-- Show join working:
SELECT 
    t1.`id` AS json_table_id
,   t2.`id` AS joined_table_id
,   t2.`text` AS joined_table_text
FROM 
    (SELECT st1.id, st1.data->'$.matrix[*].id' as ids FROM `tmp_items` st1) t1
INNER JOIN `tmp_other_items` t2 ON JSON_CONTAINS(t1.ids, CAST(t2.`id` as json), '$')

You should see the following results: