Query a JSON column with an array of object in MySQL

Question

I have a json column with the follow array:

[
  {
    "id": "24276e4b-de81-4c2c-84e7-eed9c3582a31",
    "key": "id",
    "type": "input",
  },
  {
    "id": "e0ca5aa1-359f-4460-80ad-70445be49644",
    "key": "name",
    "type": "textarea",
    }
]

I tried the follow query to get the row that has the id 24276e4b-de81-4c2c-84e7-eed9c3582a31 in the document column, but it returns not results:

select * from jobs WHERE document->'$[*].id' = "24276e4b-de81-4c2c-84e7-eed9c3582a31"

Anyone know how to do the right query?

Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception/error, post the line it occurred on and the exception/error details. Please [edit] these details in or we may not be able to help. — Blue, Aug 05 '16 at 20:55

score 41 · Answer 1 · answered Oct 06 '17 at 05:45

41

I use mysql 5.7 and so JSON_CONTAINS can be easily used like this:

SELECT JSON_CONTAINS(
                '[{"id": "24av","name": "she"},{"id": "e0c2", "name": "another_she"}]', 
                JSON_OBJECT('id', "e0c2")
                );

answered Oct 06 '17 at 05:45

Dina

937
9
12

2

I don't understand what this is. Why is there an array of objects passed as the first parameter to JSON_CONTAINS when this array of objects is supposed to be a value in a column, and the query is supposed to find the object that has some matching parameter to the query. It doesn't make any sense to me at all. – John Jun 12 '20 at 19:31
2

@John That first parameter can be the the column from your table, here he is hardcoding the columns value so that you can run this query without any table. – karmendra Jun 16 '20 at 08:35
1

Just to add to the answer given by @Sep, you can query on multiple attributes in the json object as follows: `SELECT JSON_CONTAINS('[{"id": "24av","name": "she"},{"id": "e0c2", "name": "another_she"}]', JSON_OBJECT('id',"e0c2",'name',"another_she"));` – Lankesh Zade Sep 25 '20 at 10:22
Thanks. So I guess I am kind of wondering what this would look like if you wernt hardcoding values - practically speaking, if I'm querying JSON from a DB I don't have these values yet right? – John Jul 05 '21 at 21:50

score 22 · Answer 2 · edited Jun 28 '20 at 17:21

22

Try like this:

SELECT * FROM jobs WHERE document->'$[*].id' = json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31");

It works for me, but I think the below way is more better:

SELECT * FROM jobs WHERE json_contains(document->'$[*].id', json_array("24276e4b-de81-4c2c-84e7-eed9c3582a31"));

Actually It's easy just remember the return value is JSON_TYPE but not a String or something else;

edited Jun 28 '20 at 17:21

Hussein Akar

425
3
12

answered May 18 '17 at 07:44

The second solution is the perfect one! – darshanparab Jul 17 '21 at 14:31

score 5 · Answer 3 · answered Jul 18 '19 at 10:09

5

maybe this? @Barmar

SELECT * FROM jobs WHERE JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, '$[*].id') IS NOT NULL;

answered Jul 18 '19 at 10:09

GeekLei

991
7
8

score 4 · Answer 4 · answered Aug 05 '16 at 21:42

4

When you use document->'$[*].id' it returns a comma-delimited list of all the ID properties. This won't be equal to the value of just one ID string, unless there's only one object in the document column.

You need to use JSON_SEARCH() to search for a matching element within the JSON value.

SELECT * 
FROM jobs 
WHERE JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, '$[*].id');

answered Aug 05 '16 at 21:42

Barmar

741,623
53
500
612

1

Thank you for your explanation, but using json_search still returns 0 rows – Fabrício Aug 05 '16 at 22:52
Unfortunately I don't have a MySQL 5.7 instance to test on. – Barmar Aug 05 '16 at 22:58

Query a JSON column with an array of object in MySQL

4 Answers4

Linked