mySQL WHERE IN из массива JSON

mysql arrays json

7173 просмотра

2 ответа

882 Репутация автора

У меня есть таблица с данными JSON и оператором, который извлекает массив идентификаторов для каждой строки ...

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

Это приводит к чему-то вроде ..

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

Далее я хочу выбрать из другой таблицы, где идентификатор этой другой таблицы находится в массиве, аналогично, WHERE id IN ('1,2,3')но с использованием массива JSON ...

Что-то вроде ...

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

но это нуждается в некоторой магии JSON, и я не могу решить это ...

Автор: Matt Bryson Источник Размещён: 18.07.2016 09:15

Ответы (2)


2 плюса

46 Репутация автора

Решение

Ниже приведен полный ответ. Вы можете захотеть сделать '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), '$')

Вы должны увидеть следующие результаты:

Результаты

Автор: Simeon Bartley Размещён: 27.07.2016 06:42

0 плюса

1730 Репутация автора

Позаботьтесь о том, чтобы в принятом ответе не использовались индексы, tmp_other_itemsведущие к медленным результатам для больших таблиц.

В таком случае я обычно использую integersтаблицу, содержащую целые числа от 0 до произвольного фиксированного числа N (ниже, около 1 миллиона), и присоединяюсь к этой таблице целых чисел, чтобы получить n-й элемент JSON:

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

CREATE TABLE `integers` (`n` int NOT NULL PRIMARY KEY);
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 PRIMARY KEY, `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 } ] }')
;

-- Put a lot of rows in integers (~1M)
INSERT INTO `integers` (`n`) 
(
    SELECT 
        a.X
        + (b.X << 1)
        + (c.X << 2)
        + (d.X << 3)
        + (e.X << 4)
        + (f.X << 5)
        + (g.X << 6)
        + (h.X << 7)
        + (i.X << 8)
        + (j.X << 9)
        + (k.X << 10)
        + (l.X << 11)
        + (m.X << 12)
        + (n.X << 13)
        + (o.X << 14)
        + (p.X << 15)
        + (q.X << 16)
        + (r.X << 17)
        + (s.X << 18)
        + (t.X << 19) AS i
    FROM (SELECT 0 AS x UNION SELECT 1) AS a
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS b ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS c ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS d ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS e ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS f ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS g ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS h ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS i ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS j ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS k ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS l ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS m ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS n ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS o ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS p ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS q ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS r ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS s ON TRUE
        INNER JOIN (SELECT 0 AS x UNION SELECT 1) AS t ON TRUE)
;

-- Insert normal rows (a lot!)
INSERT INTO `tmp_other_items` (`id`, `text`) 
    (SELECT n, CONCAT('text for ', n) FROM integers);

Теперь вы можете повторить запрос принятого ответа, выполнение которого занимает около 11 секунд (но это просто):

-- Show join working (slow)
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), '$')
;

И сравните его с более быстрым подходом - преобразовать JSON во (временную) таблицу идентификаторов, а затем выполнить JOIN поверх него (что приведет к мгновенным результатам , 0,000 сек в соответствии с heidiSQL):

-- Fast
SELECT
    i.json_table_id,
    t2.id AS joined_table_id,
    t2.`text` AS joined_table_text
FROM (
    SELECT 
        j.json_table_id,
        -- Don't forget to CAST if needed, so the column type matches the index type
        -- Do an "EXPLAIN" and check its warnings if needed
        CAST(JSON_EXTRACT(j.ids, CONCAT('$[', i.n - 1, ']')) AS UNSIGNED) AS id
    FROM (
        SELECT 
            st1.id AS json_table_id,
            st1.data->'$.matrix[*].id' as ids,
            JSON_LENGTH(st1.data->'$.matrix[*].id') AS len
        FROM `tmp_items` AS st1) AS j
        INNER JOIN integers AS i ON i.n BETWEEN 1 AND len) AS i
    INNER JOIN tmp_other_items AS t2 ON t2.id = i.id
    ;

Самое внутреннее SELECTизвлекает список идентификаторов JSON вместе с их длиной (для внешнего соединения).

2-й внутренний SELECT принимает этот список идентификаторов, и JOIN для целых чисел, чтобы получить n-й идентификатор каждого списка JSON, приводя к таблице идентификаторов (вместо таблицы jsons).

Самый внешний SELECT теперь должен только объединить эту таблицу идентификаторов с таблицей, содержащей данные, которые вы хотели.

Ниже приведен тот же запрос с использованием WHERE IN для соответствия заголовку вопроса:

-- Fast (using WHERE IN)
SELECT t2.*
FROM tmp_other_items AS t2
WHERE t2.id IN (
    SELECT 
        CAST(JSON_EXTRACT(j.ids, CONCAT('$[', i.n - 1, ']')) AS UNSIGNED) AS id
    FROM (
        SELECT 
            st1.data->'$.matrix[*].id' as ids, 
            JSON_LENGTH(st1.data->'$.matrix[*].id') AS len
        FROM `tmp_items` AS st1) AS j
        INNER JOIN integers AS i ON i.n BETWEEN 1 AND len)
    ;
Автор: Xenos Размещён: 20.09.2019 09:33
Вопросы из категории :
32x32