CODEWITHSUNDEEP
mysqlsqljson
Roboroads
Roboroads

Reputation: 1709

MySQL search in array of JSON objects

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.

Upvotes: 1

Views: 45

Answers (1)

Roboroads
Roboroads

Reputation: 1709

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));

Upvotes: 2

Related Questions