Thread: Question regarding querying some JSON/JSONB
<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>
I'm riffing there, but maybe that idea is useful?
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!
I'm not sure I understand this part of your question: "what I'd like to do is pass in a number of dates and have those dates substituted in for the date keys"Are you trying to aggregate your query to add up all the "1" outputs in a sum from a large number of data structures featuring different dates? If so, I would think that you should use your current query as a dynamic table and then write a group by aggregation on it.If that assessment is right, then it seems like your real problem is that your date field is a key not a value? Can you restructure the json so you have the date as a value as well:["struct":{"date": “2014-10-10”, “overall_status”:10, “avg_response:20}, "struct":{"date": “2014-11-10”, “overall_status”:10, “avg_response:20}]Wouldn't that let you aggregate dates via a dynamic table? I'm writing free-hand (untested) but something like:select date, sum(overall_status) as StatusZeroCount, sum(avg_response) as Response10Countfromselectdata->'struct'->'date' as datecoalesce(data->'overall_status' as overall_status, 1)coalesce(data->'avg_response' as avg_response, 1)from some_tablegroup by date
I'm riffing there, but maybe that idea is useful?SteveOn Mon, Apr 27, 2015 at 7:19 AM, Weston Weems <wweems@gmail.com> wrote: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!