SQL-сопоставление столбцов по JSON в другой таблице

В моей базе данных SQL у меня есть таблица со столбцом типа JSON со строкой JSON, содержащей идентификаторы для другой таблицы. Я хочу вернуть совпадающие строки из этой другой таблицы.

Учитывая, что clubhouse.id=55...

SELECT members FROM clubhouse WHERE id=55;

Итак, столбец members в таблице clubhouse № 55 возвращает: ["7","8","9"].

Мне нужны три строки из membersТаблица с идентификаторами 7, 8, 9.

Я думаю что-то вроде этого, хотя я знаю, что это не сработает:

SELECT * FROM members WHERE id=[FOR EACH JSON(SELECT members FROM clubhouse WHERE id=55)];

...Что для этого подойдет?


Мне нужны все строки из одного запроса, чтобы я мог выполнять базовую разбивку на страницы. К вашему сведению, я использую mariadb, если это имеет значение.

🤔 А знаете ли вы, что...
SQL позволяет оптимизировать запросы с помощью индексов и подсказок (hints).


1
46
2

Ответы:

Решено

мы можем попробовать использовать функцию json_contains для фильтрации значения JSON с помощью подзапроса.

SELECT *
FROM members m
WHERE EXISTS (
    SELECT 1
    FROM clubhouse c
    WHERE JSON_CONTAINS(c.members, CONCAT('"', m.id, '"'))
    AND c.id = 55
)

sqlfidde


Вы можете использовать функцию json_table (требуется MariaDB 10.6 или более поздней версии), чтобы преобразовать json в строки, а затем присоединиться:

SELECT members.*
FROM clubhouse
CROSS JOIN json_table(clubhouse.members, '$[*]' columns(
    id INT PATH '$'
)) AS j
INNER JOIN members ON j.id = members.id
WHERE clubhouse.id = 55