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 mytable (
serial int,
channel int,
mysum numeric,
mydate date
);
INSERT INTO mytable VALUES
(210062912, 1, 0.827, '2019-01-01'),
(210062912, 2, 20.864, '2019-01-01'),
(210062912, 3, 0.000, '2019-01-01'),
(210062912, 1, 0.650, '2019-01-02'),
(210062912, 2, 29.666, '2019-01-02');
5 rows affected
SELECT
serial || '-' || channel as serialchannel,
array_agg(row(mysum, mydate))
FROM
mytable
GROUP BY 1
| serialchannel | array_agg |
|---|---|
| 210062912-3 | {"(0.000,2019-01-01)"} |
| 210062912-2 | {"(20.864,2019-01-01)","(29.666,2019-01-02)"} |
| 210062912-1 | {"(0.827,2019-01-01)","(0.650,2019-01-02)"} |
SELECT
serial || '-' || channel as serialchannel,
array_agg(ARRAY[mysum::text, mydate::text])
FROM
mytable
GROUP BY 1
| serialchannel | array_agg |
|---|---|
| 210062912-3 | {{0.000,2019-01-01}} |
| 210062912-2 | {{20.864,2019-01-01},{29.666,2019-01-02}} |
| 210062912-1 | {{0.827,2019-01-01},{0.650,2019-01-02}} |
SELECT
serial || '-' || channel as serialchannel,
json_agg(json_build_object('sum', mysum, 'date', mydate))
FROM
mytable
GROUP BY 1
| serialchannel | json_agg |
|---|---|
| 210062912-3 | [{"sum" : 0.000, "date" : "2019-01-01"}] |
| 210062912-2 | [{"sum" : 20.864, "date" : "2019-01-01"}, {"sum" : 29.666, "date" : "2019-01-02"}] |
| 210062912-1 | [{"sum" : 0.827, "date" : "2019-01-01"}, {"sum" : 0.650, "date" : "2019-01-02"}] |