GROUP BY with subqueries for a Group?

Question

Here's a simplified recreation of what I have:

CREATE TABLE "DocumentStore" (
  "ID" int NOT NULL,
  "Content" jsonb NOT NULL,
  CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);

INSERT INTO "DocumentStore" 
VALUES (1, '{ "ID": 1, "Group": 0, "RecordNumber": 0, "When": 1490280300 }');

INSERT INTO "DocumentStore" 
VALUES (2, '{ "ID": 2, "Group": 0, "RecordNumber": 1, "When": 1490280400 }');

INSERT INTO "DocumentStore" 
VALUES (3, '{ "ID": 3, "Group": 0, "RecordNumber": 2147483647, "When": 1490280500 }');

INSERT INTO "DocumentStore" 
VALUES (4, '{ "ID": 4, "Group": 1, "RecordNumber": 0, "When": 1490280600 }');

INSERT INTO "DocumentStore" 
VALUES (5, '{ "ID": 5, "Group": 1, "RecordNumber": 2147483647, "When": 1490280700 }');

INSERT INTO "DocumentStore" 
VALUES (6, '{ "ID": 6, "Group": 2, "RecordNumber": 0, "When": 1490280900 }');

INSERT INTO "DocumentStore" 
VALUES (7, '{ "ID": 7, "Group": 2, "RecordNumber": 1, "When": 1490281000 }');

I'm looking to break things down by Group and get a result set that looks something like this:

+-------+------------+------------+-------+
| Group |    Start   |    Stop    | Count |
+-------+------------+------------+-------+
|   0   | 1490280300 | 1490280500 |   3   |
+-------+------------+------------+-------+
|   1   | 1490280600 | 1490280800 |   2   |
+-------+------------+------------+-------+
|   2   | 1490280900 |    NULL    |   2   |
+-------+------------+------------+-------+

Start and Stop are Unix timestamps. I just grabbed ones from this morning and rounded out the numbers for simplicity.

A group will always have a "RecordNumber" = 0 entry so if there's a group, there's a start time. A group that doesn't have an entry with "RecordNumber" = 2147483647 should not have an end time.

I'm trying to summarize every Group by looking up it's Start time by finding the jsonb document for that Group that has a RecordNumber of 0 and using it's When value, and look up it's Stop time by finding the jsonb document for that Group that has a RecordNumber of 2147483647 and use it's When value, and finally a count of how many documents are in that Group.

I started with this:

SELECT
    "Content"->>'Group' AS "Group",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 0)::int AS "Start",
    (SELECT "Content"->>'When' FROM "DocumentStore" WHERE ("Content"->>'RecordNumber')::int = 2147483647)::int AS "End",
    COUNT("Content") AS "DetailCount"
        FROM "DocumentStore"
        GROUP BY "Group";

It works if all the documents in the table belong to the same Group. As soon as there are 2 groups my subqueries become a problem: more than one row returned by a subquery used as an expression. I tried adding WHERE "Content"->>'Group' = "Group" to the subqueries but it seems I can't do that (column "Group" does not exist).

I'm thinking this might be a chance to join a table with itself but I'm not sure where to begin with that.

Answer 1

Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group", 
         min(case when Content->>'RecordNumber' = '0' 
                  then Content->>'When' 
                  else null 
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647' 
                  then Content->>'When' 
                  else null 
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group", 
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620