MySQL JSON-使用IN语句| json_contains [英] MySQL JSON - using IN statement | json_contains

问题描述

我正在尝试选择json列中roles属性包含任何值的所有列.

I'm trying to select all columns where the roles property in the json column contains ANY of the values.

我尝试过的陈述:

SELECT * FROM components WHERE json->'$.roles' IN(1)

• 这甚至行不通，但我认为应该...

SELECT * FROM components WHERE JSON_CONTAINS(components, '1', '$.roles')

• 这确实有效，但是很严格，所以当我使用1时，它会像应有的那样拉两个，因为它们都包含1，但是如果我插入1,2或JSON_ARRAY(1,2)，它只会拉后一行，因为它不是不会检查每个数组元素...

• this does work however is strict, so when I use 1 it pulls both like it should because they both contain 1, however if I insert 1,2 or JSON_ARRAY(1,2) it will only pull the later row because it isn't checking per array element...

我有下表components 结构和数据:

+====+========================================================================================================================================================================================================+==+
| id |                                                                                                  json                                                                                                  |  |
+====+========================================================================================================================================================================================================+==+
|  1 | {"area": 1, "roles": [1], "elements": [{"home": {"content": "Home", "attributes": {"class": "my_class_1"}}}, {"dashboard": {"content": "Dashboard", "attributes": {"class": "my_class_1"}}}]}          |  |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|  2 | {"area": 1, "roles": [1, 2, 5], "elements": [{"home": {"content": "Testing", "attributes": {"class": "my_class_1"}}}, {"dashboard": {"content": "Dashboard", "attributes": {"class": "my_class_1"}}}]} |  |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

问题:如何修改这些语句中的任何一条，以允许它们基于roles属性中的值查询行?

Question: How can I modify either of these statements to allow them to query the rows based on the values in the roles property?

推荐答案

表达式

value in (x, y, z, ...)

等同于

value = x OR value = y OR value = z OR ...

这不适用于像json->'$.roles'这样的数组，因为数组不等于其元素，您需要调用JSON_CONTAINS()进行测试.

This doesn't work for an array like json->'$.roles' because an array is not equal to its elements, you need to call JSON_CONTAINS() to test that.

对于您想要的内容，您需要为要测试的每个值调用JSON_CONTAINS().

For what you want, you need to call JSON_CONTAINS() for each value you want to test.

WHERE JSON_CONTAINS(components, '1', '$.roles') OR JSON_CONTAINS(components, '2', '$.roles')

这篇关于MySQL JSON-使用IN语句| json_contains的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！