docstore, PostgreSQL

Language: Layout:

DROP TABLE if exists DocumentStore;

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 }');

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 '0' end ) "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

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'
;

DROP TABLE DocumentStore;


absolute service time: 0,44 sec