Postgres 13

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}


SELECT
  DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  data,
   jsonb_each(data) je
  FROM preprocess_things
;


grouped_date
location
data
je


2018-05-01 00:00:00
location_00
{"3": 2}
(3,2)

2018-05-01 00:00:00
location_00
{"3": 8, "4": 7}
(3,8)

2018-05-01 00:00:00
location_00
{"3": 8, "4": 7}
(4,7)

2018-05-01 00:00:00
location_00
{"2": 1, "5": 3}
(2,1)

2018-05-01 00:00:00
location_00
{"2": 1, "5": 3}
(5,3)

2018-05-01 00:00:00
location_00
{"2": 8, "4": 1}
(2,8)

2018-05-01 00:00:00
location_00
{"2": 8, "4": 1}
(4,1)

2018-06-01 00:00:00
location_00
{"4": 1, "5": 8}
(4,1)

2018-06-01 00:00:00
location_00
{"4": 1, "5": 8}
(5,8)

2018-06-01 00:00:00
location_00
{"1": 1}
(1,1)


SELECT
  DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  x.key::int thing_type, sum(x.value::int) total
    FROM preprocess_things,
         jsonb_each(data) x
  GROUP BY grouped_date,  location, x.key::int
  order by grouped_date,  location, x.key::int;


grouped_date
location
thing_type
total


2018-05-01 00:00:00
location_00
2
9

2018-05-01 00:00:00
location_00
3
10

2018-05-01 00:00:00
location_00
4
8

2018-05-01 00:00:00
location_00
5
3

2018-06-01 00:00:00
location_00
1
1

2018-06-01 00:00:00
location_00
4
1

2018-06-01 00:00:00
location_00
5
8

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}

grouped_date	location	data	je
2018-05-01 00:00:00	location_00	{"3": 2}	(3,2)
2018-05-01 00:00:00	location_00	{"3": 8, "4": 7}	(3,8)
2018-05-01 00:00:00	location_00	{"3": 8, "4": 7}	(4,7)
2018-05-01 00:00:00	location_00	{"2": 1, "5": 3}	(2,1)
2018-05-01 00:00:00	location_00	{"2": 1, "5": 3}	(5,3)
2018-05-01 00:00:00	location_00	{"2": 8, "4": 1}	(2,8)
2018-05-01 00:00:00	location_00	{"2": 8, "4": 1}	(4,1)
2018-06-01 00:00:00	location_00	{"4": 1, "5": 8}	(4,1)
2018-06-01 00:00:00	location_00	{"4": 1, "5": 8}	(5,8)
2018-06-01 00:00:00	location_00	{"1": 1}	(1,1)