SQL match cols by JSON on other table-CodePudding

In my SQL database, I have a table with type JSON column with a JSON string containing IDs for another table. I want to return matching rows from that other table.

Given that clubhouse.id=55...

SELECT members FROM clubhouse WHERE id=55;

So, the members col on the clubhouse table no 55 returns: ["7","8","9"].

I want the three rows from the members table with IDs 7, 8, 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)];

...What will work for this?

I need all rows from a single query so I can do basic pagination. FYI, I am using mariadb if that matters.

CodePudding user response：

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

CodePudding user response：

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