mysql5.7 json object - query elements of an array

Question

I'm using JSON object type on mysql5.7. I works well if I have a json object with one level, but if I have an object with an array of objects inside, I'm having problems to query for values in the array.

This is my Table:

id (int) | json_data (JSON)
-----------------------------------------------------------------
   1     | {'name':'joe', 'array':[{'prop':first','value':'1'},
         |    {'prop':second','value':'2'}]}
   2     | {'name':'bob', 'array':[{'prop':third','value':'3'}]}

I'm trying to write a query that will retrieve all the records that contains object with value=1 inside the array.

I tried this query:

SELECT * from myTable where json_data->'$.array[0].value' = '1'; 

It works but only because I'm checking specifically the first value in the array. How can I check for all the elements of the array?

I tried using json_data->'$.array[*].value' , json_data->'$.array[.].value' , json_data->'$.array[?].value', none of them worked.

What is the way to search all elements of an array?

Transpire

Answer 1

you can use JSON_SEARCH function. This function returns the path to the given string in the json object, but you can use it also to retrieve results as it returns null if the element doesn't exist

In your case, running:

select JSON_SEARCH(json_data, 'all', '1', null, '$.array[*].value') from myTable

Will return '$.array[0].value' for the first row, and null for the second.

So use that to do:

select * from myTable where JSON_SEARCH(json_data, 'all', '1', 
null, '$.array[*].value') is not null

To get the first row only.

Berdichev

@Domezchoc - indexing is not trivial on json column. You can't index a specific field inside the json. what you can do is create a generated column from a specific field of the json, and index this column. I'm not sure how it will work for search or arrays though. You can read more about it here: dev.mysql.com/doc/refman/5.7/en/… – Berdichev