How to make a list of rows a json array, with continuous aggregates?

1

I have this query:

CREATE MATERIALIZED VIEW candles_agg
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('10 day', ts_bucket) AS day_bucket,
    json_agg(array[EXTRACT(EPOCH FROM ts_bucket), "open", high, low, "close", volume::BIGINT]) AS json_array_of_arrays
FROM
    exchange.candles_d1
WHERE
    ticker = 'BTCUSDT'
    AND ts_bucket >= time_bucket('10 day', exchange.candles_d1.ts_bucket)
    AND ts_bucket < time_bucket('10 day', exchange.candles_d1.ts_bucket) + INTERVAL '10 day'
GROUP BY
    day_bucket;

how can I make the ‘aggregate’ a json array where each entry is an array representing one of the rows in the time bucket?

2

Hi @thomasd3, have you tried array_agg function? Probably it would force you to cast the array to a text.

array_agg ( anynonarray ) → anyarray

Check the aggregation functions: PostgreSQL: Documentation: 15: 9.21. Aggregate Functions

Not sure what are your performance requirements but probably casting to text will make you aggregate the results. Example:

 select min(i), array_agg(i) from generate_series(1,10)  i group by i % 2;
┌─────┬──────────────┐
│ min │  array_agg   │
├─────┼──────────────┤
│   1 │ {1,3,5,7,9}  │
│   2 │ {2,4,6,8,10} │
└─────┴──────────────┘
(2 rows)

then with arrays:

select min(i), array_agg(array[i,i]) from generate_series(1,10)  i group by i % 2;
┌─────┬───────────────────────────────────┐
│ min │             array_agg             │
├─────┼───────────────────────────────────┤
│   1 │ {{1,1},{3,3},{5,5},{7,7},{9,9}}   │
│   2 │ {{2,2},{4,4},{6,6},{8,8},{10,10}} │
└─────┴───────────────────────────────────┘
(2 rows)