MySQL how to search a JSON field for all rows that intersect with JSON values

Assume that I have a MySQL table with a JSON field, with the structure & values shown below:

CREATE TABLE `projects` (
  `project_ids` json DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `projects` (`project_ids`)
VALUES
    ('[1, 2, 3]'),
    ('[1, 2]'),
    ('[2]'),
    ('[1]'),
    ('[2, 3]');

I would like to get all rows that have ANY of the JSON values that match in a SELECT query.

For example:

SELECT * FROM projects WHERE JSON_CONTAINS(project_ids, '[1,2]');

This yields 2 rows:

[1, 2, 3]
[1, 2]

However, I would like it to yield all of the rows, because every row has at least a “1” or a “2” in the JSON. In other words, I’m looking for a query that will return rows where the JSON intersection is non-empty.

Thus, I WANT it to return:

[1, 2, 3]
[1, 2]
[2]
[1]
[2, 3]

Please ignore the larger question of whether it’s even a good idea to use JSON fields over foreign keys, etc. Assume that I have to use a JSON field.

Answer

OR is the simplest method:

SELECT *
FROM projects
WHERE JSON_CONTAINS(project_ids, '[1]') OR
      JSON_CONTAINS(project_ids, '[2]') ;

But if you want to pass in the JSON array, use JSON_OVERLAPS():

SELECT *
FROM projects
WHERE JSON_OVERLAPS(project_ids, '[1,2]') ;

Here is a db<>fiddle.

Advertisement

Answer