Question regarding querying some JSON/JSONB

From: Weston Weems <wweems(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Question regarding querying some JSON/JSONB
Date: 2015-04-27 14:19:58
Message-ID: CAHcN2MxYsKiZbQATtRrnphEbN8-bJyqU7jZL2niAWT9bwHJQjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Midgley 2015-04-27 18:42:45 Re: Question regarding querying some JSON/JSONB
Previous Message Adrian Klaver 2015-04-21 18:05:49 Re: How to determine offending column for insert exceptions