By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE IF NOT EXISTS preprocess_things
(
preprocess_id SERIAL NOT NULL,
arrive_date timestamp without time zone NOT NULL,
arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
data jsonb NOT NULL,
CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);
INSERT INTO preprocess_things (arrive_date, arrive_location, data) VALUES
('2018-05-30 05:00:00', 'location_00', '{"3": 2}'),
('2018-05-30 10:00:00', 'location_00', '{"3": 8, "4": 7}'),
('2018-05-28 10:00:00', 'location_00', '{"5": 3, "2": 1}'),
('2018-05-28 11:00:00', 'location_00', '{"4": 1, "2": 8}'),
('2018-06-02 09:00:00', 'location_00', '{"4": 1, "5": 8}'),
('2018-06-02 08:00:00', 'location_00', '{"1": 1}');
6 rows affected
SELECT
DATE_TRUNC('month', arrive_date) AS grouped_date,
LOWER(arrive_location) AS location,
json_build_object(
'1', SUM((data->'1')::int),
'2', SUM((data->'2')::int),
'3', SUM((data->'3')::int),
'4', SUM((data->'4')::int),
'5', SUM((data->'5')::int)
) AS data
FROM preprocess_things
GROUP BY grouped_date,
location
| grouped_date | location | data |
|---|---|---|
| 2018-06-01 00:00:00 | location_00 | {"1" : 1, "2" : null, "3" : null, "4" : 1, "5" : 8} |
| 2018-05-01 00:00:00 | location_00 | {"1" : null, "2" : 9, "3" : 10, "4" : 8, "5" : 3} |