Postgres 11

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 visits_agg (
  id          serial primary key,
  hour        timestamp without time zone default (now() at time zone 'utc'),
  hostname    text not null,
  visits      integer default 0,
  referrers   jsonb not null default '{}'::jsonb,
  paths       jsonb not null default '{}'::jsonb,
  widths      jsonb not null default '{}'::jsonb
);

CREATE TABLE


INSERT INTO "visits_agg" ("hour", "hostname", "visits", "referrers", "paths", "widths") VALUES
('2019-01-16 09:00:00', 'example.com', '6', '{"example2.com": 4}', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '{"google": 6}', '{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}', '{"360": 3, "1398": 1, "1680": 1, "2560": 1}'),
('2019-01-16 15:00:00', 'example.com', '6', '{"google": 6}', '{"/about": 1, "/article/title1": 1, "/article/title2": 3, "/": 1}', '{"360": 3, "1280": 2, "1920": 1}'),
('2019-01-16 16:00:00', 'example.com', '6', '{"facebook": 1, "example2.com": 1}', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');

INSERT 0 4


SELECT 
    hostname,
    r.key as ref_name,
    r.value::int as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_each(referrers) r;


hostname
ref_name
ref_value


example.com
example2.com
4

example.com
google
6

example.com
google
6

example.com
facebook
1

example.com
example2.com
1



SELECT 5


SELECT row_to_json(t0, true)
FROM
    (
    SELECT t1.hostname, t1.visits, t2.referrers, t3.paths, t4.widths
    FROM   (SELECT   hostname, SUM(visits) as visits
            FROM     visits_agg
            GROUP BY hostname) t1
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
            FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(referrers) r
                      GROUP BY hostname, r.key) t1
           GROUP BY hostname) t2
           ON t1.hostname = t2.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(path_name, path_value)) as paths
            FROM     (SELECT hostname, p.key as path_name, SUM(p.value::int) as path_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(paths) p
                      GROUP BY hostname, p.key) t1
            GROUP BY hostname) t3
           ON t1.hostname = t3.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(width_name, width_value)) as widths
            FROM     (SELECT hostname, w.key as width_name, SUM(w.value::int) as width_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(widths) w
                      GROUP BY hostname, w.key) t1
            GROUP BY hostname) t4
           ON t1.hostname = t4.hostname
    ) t0;


row_to_json


{"hostname":"example.com",
 "visits":25,
 "referrers":[{"google": 12}, {"facebook": 1}, {"example2.com": 5}],
 "paths":[{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}],
 "widths":[{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}]}



SELECT 1


SELECT jsonb_build_object(
                          'hostname',  t1.hostname,
                          'visits',    t1.visits,
                          'referrers', t2.r,
                          'paths',     t3.p,
                          'widths',    t4.w
                          )
FROM   (SELECT   hostname, SUM(visits) as visits
        FROM     visits_agg
        GROUP BY hostname
       ) t1
JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as r
        FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
                  FROM   visits_agg
                  CROSS JOIN LATERAL jsonb_each(referrers) r
                  GROUP BY hostname, r.key) t1
        GROUP BY hostname) t2
       ON t1.hostname = t2.hostname
JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(path_name, path_value)) as p
        FROM     (SELECT hostname, p.key as path_name, SUM(p.value::int) as path_value
                  FROM   visits_agg
                  CROSS JOIN LATERAL jsonb_each(paths) p
                  GROUP BY hostname, p.key) t1
        GROUP BY hostname) t3
       ON t1.hostname = t3.hostname
JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(width_name, width_value)) as w
        FROM     (SELECT hostname, w.key as width_name, SUM(w.value::int) as width_value
                  FROM   visits_agg
                  CROSS JOIN LATERAL jsonb_each(widths) w
                  GROUP BY hostname, w.key) t1
        GROUP BY hostname) t4
       ON t1.hostname = t4.hostname;


jsonb_build_object


{"paths": [{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}], "visits": 25, "widths": [{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}], "hostname": "example.com", "referrers": [{"google": 12}, {"facebook": 1}, {"example2.com": 5}]}



SELECT 1
hostname	ref_name	ref_value
example.com	example2.com	4
example.com	google	6
example.com	google	6
example.com	facebook	1
example.com	example2.com	1