JSON_CONTAINS() with an array of JSON objects in MySQL

Question

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.

Answer 1

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 |
+-----+--------------------------------------------------------+---+

Answer 2

You must use JSON_SEARCH:

SELECT JSON_SEARCH(arr, 'one', 'jay', NULL, '$[*].name') IS NOT NULL
FROM test 
WHERE id=1;