MySQL select rows where the JSON array column is a subset of another array

Question

Hy, I have a table of machines with a JSON array column called cells. Some example data:

machine_id  | cells
-----------------------------------------
1           | ['TE001', 'GM0002']
2           | ['TE001', 'TK123', 'AW123']
3           | ['TK123', 'GS123']

I also have an array of cells that are compatible to a specific machine version, e.g.

('TE001', 'GM0002', 'TK123', 'GS123', 'LL321')

I need a query to extract all the machines whose cells are ALL contained into my machine version cells.

machine_id  | shouldSelect | explaination
------------------------------------
1           | true         | both TE001 and GM0002 are in the compatible list
2           | false        | AW123 is not in the compatible list
3           | true         | both TK123 and GS123 are in the compatible list

I can't find a viable way to achieve that, I tried some combination of JSON_OVERLAPS and MEMBER OF without any success

score 0 · Answer 1 · answered Sep 28 '20 at 14:08

This is can be solved by bit a complicate query:

SELECT 
    machines.*,
    JSON_LENGTH(machines.cells),
    sum(data.code MEMBER OF (machines.cells))
FROM
machines,
JSON_TABLE(
   '["TE001", "GM0002", "TK123", "GS123", "LL321"]',
   "$[*]"
   COLUMNS(code varchar(32) PATH "$")
) data
GROUP BY machines.machine_id, machines.cells
HAVING JSON_LENGTH(machines.cells) = sum(data.code MEMBER OF (machines.cells))
;

Here a fiddle: SQLize.online

The approach is convert JSON to table using JSON_TABLE and join it to source table. I'm not test performance of query but it do this job :)

MySQL select rows where the JSON array column is a subset of another array

1 Answers1