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
– Blue, Commented Aug 5, 2016 at 20:55
Your JSON is invalid. You can't have a comma after the last property in an object. — Barmar
– Barmar, Commented Aug 20 at 17:37

Dina · Accepted Answer · 2017-10-06 05:45:22Z

44

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 6, 2017 at 5:45

Dina

1,01510 silver badges12 bronze badges

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

4 Comments

John Over a year ago

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.

karmendra Over a year ago

@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.

Lankesh Zade Over a year ago

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

John Over a year ago

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?

Hussein Akar · Accepted Answer · 2020-06-28 17:21:29Z

24

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, 2020 at 17:21

Hussein Akar

4453 silver badges13 bronze badges

answered May 18, 2017 at 7:44

user5134456

1 Comment

darshanparab Over a year ago

The second solution is the perfect one!

GeekLei · Accepted Answer · 2019-07-18 10:09:19Z

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, 2019 at 10:09

GeekLei

1,0018 silver badges8 bronze badges

Comments

Barmar · Accepted Answer · 2016-08-05 21:42:46Z

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 5, 2016 at 21:42

Barmar

789k57 gold badges554 silver badges669 bronze badges

2 Comments

Fabrício Over a year ago

Thank you for your explanation, but using json_search still returns 0 rows

Barmar Over a year ago

Unfortunately I don't have a MySQL 5.7 instance to test on.

Dominik Ehrenberg · Accepted Answer · 2025-08-20 11:42:48Z

-1

You can use JSON_VALUE for this

select * from jobs WHERE JSON_VALUE(document, "$[*].id") = "24276e4b-de81-4c2c-84e7-eed9c3582a31"

Assuming your column with the JSON is document

answered Aug 20 at 11:42

Dominik Ehrenberg

1,7681 gold badge17 silver badges15 bronze badges

3 Comments

Barmar Aug 20 at 17:38

This doesn't work. db-fiddle.com/f/dr8Rz5BtVk1YgUa5sVRQPT/0

Barmar Aug 20 at 17:41

select JSON_VALUE(document, "$[*].id") AS val from jobs returns NULL.

Dominik Ehrenberg Aug 25 at 12:51

My bad. I forgot that JSON_VALUE only works if you have the direct path (meaning the second param would have to be "$[0].id" or "$[1].id" to read the value of the specific element in the array), which is not what you need :D This here would work tho: SELECT document from jobs where JSON_SEARCH(document, "one", "24276e4b-de81-4c2c-84e7-eed9c3582a31", NULL, "$[*].id");

Collectives™ on Stack Overflow

Query a JSON column with an array of object in MySQL

5 Answers 5

4 Comments

1 Comment

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related