Match columns by values stored as JSON in another table

Question

I have a table having a JSON datatype column which contains JSON array consisting of IDs for another table. I want to return matching rows from that other table.

For example:

SELECT members
FROM clubhouse
WHERE id = 55;

And it returns:

["7","8","9"]

I want the three rows from the members table with IDs 7, 8 and 9.

I'm thinking something like this, tho I know it won't work:

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

How can I do this?

Note that I need all rows from a single query so I can do basic pagination. I am using MariaDB if that matters.

Answer 1

we can try to use json_contains function to filter JSON value with subquery.

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

sqlfidde

Answer 2

You can use json_table function (requires MariaDB 10.6 or later) to convert the json to rows, then join:

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