MySQL search in array of JSON objects

Question

Lets say I have a JSON column json with the following data:

+----+-----------------+
| id | json            |
+====+=================+
| 1  | [               |
|    |   {             |
|    |     "type":"a", |
|    |     "id":11111  |
|    |   },            |
|    |   {             |
|    |     "type":"a", |
|    |     "id":22222  |
|    |   },            |
|    |   {             |
|    |     "type":"b", |
|    |     "id":11111  |
|    |   }             |
|    | ]               |
+----+-----------------+
| 2  | [               |
|    |   {             |
|    |     "type":"b", |
|    |     "id":11111  |
|    |                 |
|    |   },            |
|    |   {             |
|    |     "type":"b", |
|    |     "id":22222  |
|    |                 |
|    |   },            |
|    |   {             |
|    |     "type":"c", |
|    |     "id":11111  |
|    |   }             |
|    | ]               |
+----+-----------------+

Is there a way to determine if there is an entry in this array which has type = a AND id = 11111 (in the same object), such that ROW 1 would be returned but not ROW 2?

What i've tried is something like

SELECT * 
  FROM table 
 WHERE json->>"$[*].type" = "a" 
   AND json->>"$[*].id" = 11111

but that's not for the same entry.

Roboroads · Accepted Answer · 2021-03-09 22:35:26Z

2

Ofcourse I didn't stop looking - and found the solution after 3 hours of trail and error. Feeling a little stupid now I know the solution.

SELECT *
  FROM table
 WHERE JSON_CONTAINS(json, JSON_OBJECT('type', 'a', 'id', 11111));

edited Mar 9, 2021 at 22:35

answered Mar 9, 2021 at 13:19

Roboroads

1,7571 gold badge15 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

MySQL search in array of JSON objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related