有没有办法检查一个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具有功能

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屋！