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.

Lukasz Szozda · Accepted Answer · 2021-04-19 19:08:14Z

18

+50

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

edited Apr 19, 2021 at 19:08

answered Apr 19, 2021 at 19:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charles Wood Over a year ago

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!

Akina · Accepted Answer · 2021-04-21 09:10:00Z

2

You must use JSON_SEARCH:

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

answered Apr 21, 2021 at 9:10

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

JSON_CONTAINS() with an array of JSON objects in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related