6

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?

  • `json->'$.roles'` is an array, `1` is a single number. An array is not equal to a number. – Barmar May 31 '17 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. –  May 31 '17 at 20:41

4 Answers4

11

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')
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • 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? –  May 31 '17 at 20:43
  • Came up with a better answer. – Barmar May 31 '17 at 20:49
  • 1
    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. –  May 31 '17 at 20:51
  • 1
    Oh right, it wants to match them all, not any. I think it will have to go back to the `OR` version. – Barmar May 31 '17 at 20:55
  • ```JSON_OVERLAPS(..)``` based solution should be the preferred answer. Ref: https://stackoverflow.com/a/62795451/2469308 – Madhur Bhaiya Feb 23 '23 at 08:39
  • @MadhurBhaiya My answer is 6 years old, when JSON_OVERLAPS didn't exist. – Barmar Feb 23 '23 at 16:13
  • my comment is for future readers, so that they can rather use the direct function available in MySQL now! – Madhur Bhaiya Feb 24 '23 at 04:35
11

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)
Alexey Kosov
  • 3,010
  • 2
  • 23
  • 32
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), '$');
james
  • 1
  • 1
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...

dkr8
  • 19
  • 3