How to merge arrays of different table entries together?

Question

I have a table which keeps track of price changes at different dates:

| ID | price           | date       |
|----|-----------------|------------|
| 1  | {118, 123, 144} | 2020/12/05 |
| 2  | {222, 333, 231} | 2020/12/06 |
| 3  | {99, 55, 33}    | 2020/12/07 |

I would like to retrieve all prices in a single array ordered by their date so I would get the result like this:

| ID | price                              |
|----|------------------------------------|
| 1  | {118,123,144,222,333,231,99,55,33} |

How can I achieve this? It seems to me that the challenge is creating a new array or appending values to an existing one.

Answer 1

You can unnest and re-aggregate with proper ordering:

select array_agg(x.val order by t.date, x.rn) as all_prices
from mytable t
cross join lateral unnest(t.price) with ordinality as x(val, rn)

One (presumably) important thing is to keep track of the position of each element in the original array, that we can then use as second-level sorting criteria when aggregating.