MySQL JSON_OVERLAPS ... how do I compare json from one table to a group of ID values from another ta-CodePudding

I have 2 MySQL tables that look like this:

first_table: the "json_field" values are just lists of integers, but the field type is "json":

id   json_field
1    [1,2,3]
2    [2,3]
3    [1,3]

second_table: just a simple table with 2 fields (integer, varchar):

id   name
1    bob
2    sam
3    mary

I need a single query that gives me all rows from first_table for which json_field values intersect with any "id" value from second_table that satisfies a simple query condition of "where name = 'sam'" (e.g. using a subquery)

Here's what I have so far:

select * from first_table where 
    JSON_OVERLAPS(json_field, (select group_concat(id) from second_table where name = 'sam'))

This query gives this error, however:

"Invalid JSON text in argument 1 to function json_overlaps: "The document root must not be followed by other values." at position 1."

I also tried this thinking that I had to convert the result of group_concat to json first, but got an SQL syntax error:

select * from first_table where 
    JSON_OVERLAPS(json_field, JSON_OBJECT(select group_concat(id) from second_table where name = 'sam'))

The correct result should give me the following 2 rows, since those rows contain id = 2, which is the id that corresponds to "sam" in second_table

1   1,2,3
2   2,3

Please assume that I must use a json field type for "first_table". The schema that I outlined above cannot be changed.

CodePudding user response：

Don't use GROUP_CONCAT(), because it returns a string, not a set of rows. It can't be used to compare to a JSON document, and it can't be used to convert into a JSON array.

Instead, use JSON_ARRAYAGG() on a set of rows. This function transforms the set of rows into a JSON array.

select id, json_pretty(json_field) from first_table
where json_overlaps(json_field, 
  (select json_arrayagg(id) from second_table where name='Sam')
);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7a8d4f8a47042c1f9fa763c0689287ea

CodePudding user response：

SELECT t1.id, CAST(t1.json_field AS CHAR), t2.id, t2.name
FROM t1
JOIN t2 ON JSON_OVERLAPS(t1.json_field, CAST(t2.id AS JSON))
WHERE t2.name = 'sam'

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6d065e49643b04d4630f0031096aa4ee