See if an JSON array in MySQL contains an object whose key holds a specific date 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 | [{&quot;data&quot; : [&quot;some&quot;, &quot;data#1&quot;], &quot;date&quot;: &quot;2016-04-21&quot;}, {&quot;data&quot; : [&quot;other&quot;, &quot;data#1&quot;], &quot;date&quot; : &quot;2016-04-22&quot;}]
2 | 2 | [{&quot;data&quot; : [&quot;some&quot;, &quot;data#2&quot;], &quot;date&quot;: &quot;2016-04-21&quot;}, {&quot;data&quot; : [&quot;other&quot;, &quot;data#2&quot;], &quot;date&quot; : &quot;2016-04-26&quot;}]
3 | 1 | [{&quot;data&quot; : [&quot;some&quot;, &quot;data#3&quot;], &quot;date&quot;: &quot;2016-04-22&quot;}, {&quot;data&quot; : [&quot;other&quot;, &quot;data#3&quot;], &quot;date&quot; : &quot;2016-04-26&quot;}]
4 | 3 | [{&quot;data&quot; : [&quot;some&quot;, &quot;data#4&quot;], &quot;date&quot;: &quot;2016-04-26&quot;}]

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`, &quot;$[*].date&quot;) from applications

Which returns:
1 | [&quot;2016-04-21&quot;, &quot;2016-04-22&quot;]
2 | [&quot;2016-04-21&quot;, &quot;2016-04-26&quot;]
3 | [&quot;2016-04-22&quot;, &quot;2016-04-26&quot;]
4 | [&quot;2016-04-26&quot;]

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`, &quot;$[0].date&quot;) = &quot;2016-04-26&quot;

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`, &quot;$[*].date&quot;) = &quot;2016-04-26&quot;

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?
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