来自 JSON 数组的 mySQL WHERE IN

Question

我有一个包含 JSON 数据的表，以及一个为每一行提取 ID 数组的语句...

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

这会导致类似...

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

接下来我想从另一个表中选择另一个表的 ID 在数组中，类似于 WHERE id IN ('1,2,3') 但使用 JSON 数组...

类似...

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

但它需要一些 JSON 魔法，我无法解决...

Answer 1

下面是一个完整的答案。你可能想要一个 'use <db_name>;'脚本顶部的语句。重点是表明 JSON_CONTAINS() 可用于实现所需的连接。

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), '$')

您应该会看到以下结果: