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