有没有办法检查一个json数组是否在MySQL中至少包含另一个json数组的一项? [英] Is there a way to check if an json array contains at least one item of another json array in MySQL?
问题描述
我在我的MySQL db json字段中存储了一些json数组.
例如: ["Python","C","C ++","C#","R"]
I'm storing some json arrays in my MySQL db json field.
e.g: ["Python", "C", "C++", "C#", "R"]
我的数据库示例:
| name | techs |
|--------|------------------|
| victor | ["Python", "R"] |
| anne | ["C#", "Python"] |
我需要搜索json数组包含另一个json数组的至少一项的行.问题出在我正在执行的查询中:
I need to search the lines that the json array contains at least one of the items of another json array. The problem is in the query that I'm executing:
select name from devs
where json_contains('techs', '["Python"]')
这实际上可以正常工作,并返回数组包含"Python"的所有行(在此示例中为[Victor,Anne]),但是当我尝试传递任何数组中不存在的项目时,
This actually works fine and returns all the lines that the array contains "Python" (in this example, [Victor, Anne]), but when I try to pass items that don't exists in any of the arrays:
select * from devs
where json_contains('techs', '["Python", "Java"]')
这没有返回任何内容,因为上面没有带有"Python"和"Java"的数组.也许,我想在他们的json数组中收到所有带有"Python"或"Java"的行.
This didn't return nothing, because there isn't an array with "Python" AND "Java" on it. Perhaps, I would like to receive all the lines with "Python" OR "Java" on their json array.
这样,是否有一种语法可以按我想要的方式返回数据?
预先感谢.
In this way, Is there a syntax to return the data in the way that I want?
Thanks in advance.
有用的信息:MySQL:v8.0,可在Windows 10上运行.
Useful information: MySQL: v8.0, Working on Windows 10.
推荐答案
MySQL 8.0 has function JSON_OVERLAPS(), which does exactly what you ask for:
比较两个JSON文档.如果两个文档具有相同的键值对或数组元素,则返回true(1).
Compares two JSON documents. Returns true (1) if the two document have any key-value pairs or array elements in common.
当两个比较两个数组时,如果JSON_OVERLAPS()共享一个或多个数组元素,则返回true,否则返回false.
When two comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not.
您可以在自联接查询中使用它,例如:
You can use that in a self-join query, like:
select t.*
from mytable t
inner join mytable t1 on json_overlaps(t1.techs, t2.techs)
这篇关于有没有办法检查一个json数组是否在MySQL中至少包含另一个json数组的一项?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!