I'm having troubles with this query (or rather getting it to work exactly as I expected to. I've also summarized here:
http://pastecode.org/index.php/view/47785912
data
[“2014-10-10”:{“overall_status”:10, “avg_response:20}, “2014-11-10”:{“overall_status”:10, “avg_response:20}]
[“2014-10-10”:{“overall_status”:10, “avg_response:20}, “2014-11-10”:{“overall_status”:10, “avg_response:20}]
select
count(case when data->’2014-10-10’->’overall_status’ = 0 then 1) as StatusZeroCount,
count(case when data->’2014-10-10’->’avg_response’ = 10 then 1) as Response10Count,
from some_table where ...
This works, even for cases where records dont actually even have a key for that date (which is what I want)
Ideally what I'd like to do is pass in a number of dates and have those dates substituted in for the date keys (and return counts even if the keys dont exist in the json) in the count queries and return data like:
date | StatusZeroCount | Response10Count
2014-10-10 | 10 | 2
The problems I see is first of all, how to say substittute in these dates... seems like a subselect, where the date keys are pulled from the parent query would work, but then I'd get one record with a ton of columns.
Seems like there would probably be a way to group by key and group by overall_status, avg_response and get counts of each or something too, but I dont know.
I guess worst case scenario, I could just query the data 12 times (since I'm basically saying get some counts for the last 12 mo) so conceivably 12 records, and the aggregates I can build against that data, but it seems like that would be hugely wasteful.
Thanks for any advice in advance!
