7

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

Statements I've tried:

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

  • this doesn't even work but it should in my opinion...

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

  • 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...

I have the following table components structure and data: 

+====+========================================================================================================================================================================================================+==+
| 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"}}}]} |  |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

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

Improve this question
2
  • json->'$.roles' is an array, 1 is a single number. An array is not equal to a number. Commented May 31, 2017 at 20:37
  • @Barmar agreed, even if I use [1,2] or JSON_ARRAY(1,2) in the JSON_CONTAINS() it will error because it doesn't like the value parameter an array. Commented May 31, 2017 at 20:41

4 Answers 4

Reset to default
14

See https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps

JSON_OVERLAPS(json_doc1, json_doc2)

Compares two JSON documents. Returns true (1) if the two document have any key-value pairs or array elements in common. If both arguments are scalars, the function performs a simple equality test.

This function serves as counterpart to JSON_CONTAINS(), which requires all elements of the array searched for to be present in the array searched in. Thus, JSON_CONTAINS() performs an AND operation on search keys, while JSON_OVERLAPS() performs an OR operation.

Queries on JSON columns of InnoDB tables using JSON_OVERLAPS() in the WHERE clause can be optimized using multi-valued indexes. Multi-Valued Indexes, provides detailed information and examples.

When comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not.

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

12

The expression 

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

is equivalent to

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

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.

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')
Improve this answer

7 Comments

Yeah I've seen that... it just looks "hackish" doesn't it? but yeah it may be only way possible to achieve it. Is this possible to make dynamic though?
Came up with a better answer.
Yeah I tried exactly that! only thing is it's strict so like JSON_ARRAY(1,2) won't return rows with roles that contain 1 only if it's in that exact order.
Oh right, it wants to match them all, not any. I think it will have to go back to the OR version.
JSON_OVERLAPS(..) based solution should be the preferred answer. Ref: stackoverflow.com/a/62795451/2469308
|
0

try one of these it should work

SELECT * FROM components WHERE JSON_CONTAINS([1],  roles, '$');

SELECT * FROM components WHERE JSON_CONTAINS([1],  CAST(roles as JSON), '$');
Improve this answer

Comments

0

Just tried because i got a similar issue, and the following works for me:

field->'$.sub' REGEXP '^(1|3|5)$'

Yes, it seems that it is possible to use regular expressions here...

Note that if you try to query a string field in the json you should considder that strings are surrounded by a ", so you have to query something like this:

customer->'$.mail' REGEXP '^\"(info|contact)@.*\.com\"$'

Hope it helps, but i dont know how that impacts in performance...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.