6

This is a sample database 'test' with a JSON column 'arr' containing an array of JSON objects

+----+----------------------------------------------------------+
| id | arr                                                      |
+----+----------------------------------------------------------+
|  1 | [{"name": "aman"}, {"name": "jay"}]                      |
|  2 | [{"name": "yash"}, {"name": "aman"}, {"name": "jay"}] |
+----+----------------------------------------------------------+

I want to use JSON_CONTAINS to know if a value exists in a specific key of an object in the array.

Here's my query :

SELECT JSON_CONTAINS(arr, '"jay"', '$[*].name') from test WHERE id=1;

I get the following error:

ERROR 3149 (42000): In this situation, path expressions may not contain the * and ** tokens or an array range.

I know that I can try using JSON_EXTRACT() for this, but what am I doing wrong here ?

Is there any way to use JSON_CONTAINS with an array of JSON objects in MySQL.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Jayesh Padhiar
  • 73
  • 1
  • 1
  • 6

2 Answers2

11

Yes, it is possible using the following syntax:

SELECT JSON_CONTAINS(arr, '{"name": "jay"}') from test WHERE id=1;

db<>fiddle demo

Example:

+-----+--------------------------------------------------------+---+
| id  |                          arr                           | r |
+-----+--------------------------------------------------------+---+
|  1  | [{"name": "aman"}, {"name": "jay"}]                    | 1 |
|  2  | [{"name": "yash"}, {"name": "aman"}, {"name": "jay"}]  | 1 |
|  3  | [{"name": "yash"}, {"name": "aman"}]                   | 0 |
+-----+--------------------------------------------------------+---+
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 2
    I know we're not supposed to comment "thanks", but man! I was about to lose my mind! Thank you so much for contributing this knowledge! – Charles Wood Sep 12 '22 at 21:09
2

You must use JSON_SEARCH:

SELECT JSON_SEARCH(arr, 'one', 'jay', NULL, '$[*].name') IS NOT NULL
FROM test 
WHERE id=1;
Akina
  • 39,301
  • 5
  • 14
  • 25