See if an JSON array in MySQL contains an object whose key holds a specific date

Question

i'm trying to find out if there is a row which contains a specific date inside a JSON array

Let's say my data looks like this:

Table applications:

id | application_id | data

# Rows
1 | 1 | [{"data" : ["some", "data#1"], "date": "2016年04月21日"}, {"data" : ["other", "data#1"], "date" : "2016年04月22日"}]
2 | 2 | [{"data" : ["some", "data#2"], "date": "2016年04月21日"}, {"data" : ["other", "data#2"], "date" : "2016年04月26日"}]
3 | 1 | [{"data" : ["some", "data#3"], "date": "2016年04月22日"}, {"data" : ["other", "data#3"], "date" : "2016年04月26日"}]
4 | 3 | [{"data" : ["some", "data#4"], "date": "2016年04月26日"}]

How can I find all the applications whose data contains the date '2016-04-26'?

So basically I can do this:

select id, json_extract(`data`, "$[*].date") from applications

Which returns:

1 | ["2016年04月21日", "2016年04月22日"]
2 | ["2016年04月21日", "2016年04月26日"]
3 | ["2016年04月22日", "2016年04月26日"]
4 | ["2016年04月26日"]

But if try to use json_extract in the WHERE clause I can only use it if I explicitly tell the array's key in the json_extract's path argument, like so:

select * from applications where json_extract(`data`, "$[0].date") = "2016年04月26日"

which correctly returns the row with id 4.

But if I try to use a wildcard in the path then it no longer works:

select * from applications where json_extract(`data`, "$[*].date") = "2016年04月26日"

this should return the rows 2, 3, 4.

I tried many other options/variations but I can't seem to find a way to structure the query correctly.

Is something like this even possible with the current implementation of MySQL JSON?

Answer 1

One solution as provided by Morgan Tucker - @morgo is to use json_contains like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}')

For now the answer is OK, but i believe it can have some performance issues and feels a bit hackish to me (see the next query) - but I will deal with those when i get there :)

If I would need to query on a date range (from 2016年04月24日 to 2016年04月26日) I would need to add an invididual json_contains for each day in the time span like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}') or json_contains(`data`, '{"date" : "2016-04-25"}') or json_contains(`data`, '{"date" : "2016-04-24"}')

And this would return invalid data if I would have an date key nested somewhere else

So if you have an different solution, I would like to know

• a_vlad: as variant - define max depth of date array - SELECT MAX(json_depth(data ->> '$[*].date')), than thru the loop in stored procedure extract to temp tables - id and selected date - select id, json_extract(data, "$[0].date") as 'date' from applications, than- select id, json_extract(data, "$[1].date") as 'date' from applications, and etc. than apply all filter and have list of id
• Jimmy Ilenloa: you gave me a clue on how to use the json_contains syntax