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.
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.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加