13

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?

11

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

  • 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 – a_vlad Nov 18 '16 at 11:04
  • you gave me a clue on how to use the json_contains syntax – Jimmy Ilenloa Dec 24 '16 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.