Re: Question regarding querying some JSON/JSONB - Mailing list pgsql-sql

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 Response10Count

from

  select

    data->'struct'->'date' as date

    coalesce(data->'overall_status' as overall_status, 1)

    coalesce(data->'avg_response' as avg_response, 1)

  from some_table

group by date

I'm riffing there, but maybe that idea is useful?

Steve

On 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!