The aggregate functions for temporal JSONB values are illustrated next.
Temporal count
tCount(tjsonb) → {tintSeq,tintSeqSet}
WITH Temp(temp) AS (
SELECT tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-03]' UNION
SELECT tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-02,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-04]' )
SELECT tCount(Temp)
FROM Temp;
-- {[1@2001-01-01, 2@2001-01-02, 2@2001-01-03], (1@2001-01-03, 1@2001-01-04]}
Window count
wCount(tjsonb) → {tintSeq,tintSeqSet}
WITH Temp(temp) AS (
SELECT tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-01,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-03]' UNION
SELECT tjsonb '[{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-02,
{"vehicleId": 1, "location": "Point(1 1)"}@2001-01-04]' )
SELECT wCount(Temp, interval '2 days')
FROM Temp;
-- {[1@2001-01-01, 2@2001-01-02, 2@2001-01-05], (1@2001-01-05, 1@2001-01-06]}