how to order output of arrays when using union-CodePudding

I have a query like this:

SELECT array_agg(candles) as candles FROM ( SELECT * FROM ... ) AS candles
UNION ALL
SELECT array_agg(trades) as trades FROM ( SELECT * FROM ... ) AS trades
UNION ALL
SELECT ...

But then I'll get rows that contain arrays, but the order of the rows doesn't necessarily match the query order. For example, it is possible that the output will have the trades row before the candles row.

How can I get the rows in a predictable order?

Edit:

updated the query based on the answer but getting an error:

SELECT a FROM
(
    SELECT 1 as o, array_agg(candles) as a
    FROM (
        SELECT ts, open, high, low, close, midpoint, volume
        FROM exchange.binance.candles
        WHERE instrument = 'BTCUSDT' AND ts >= '2022-04-01 00:00:00' AND ts < '2022-04-01 01:00:00'
        ORDER BY ts) AS candles
    UNION ALL
    SELECT 2 as o, array_agg(trades)
    FROM (
        SELECT ts, price, quantity, direction
        FROM exchange.binance.trades
        WHERE instrument = 'BTCUSDT' AND ts >= '2022-04-01 00:00:00' AND ts < '2022-04-01 01:00:00'
        ORDER BY ts) AS trades
    UNION ALL
    SELECT 3 as o, array_agg(kvwap)
    FROM (
        SELECT ts, price, "interval"
        FROM exchange.binance.kvwap
        WHERE instrument = 'BTCUSDT' AND "interval" IN ('M5', 'H1', 'H4') AND ts >= '2022-04-01 00:00:00' AND ts < '2022-04-01 01:00:00'
        ORDER BY ts) AS kvwap
)
ORDER BY o;

the error is:

[42601] ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. Position: 15

CodePudding user response：

Add a column for ordering to each subquery, but don't include it in the output:

SELECT a FROM (
  SELECT 1 as o, array_agg(candles) as a FROM ( SELECT * FROM ... ) c group by 1
  UNION ALL
  SELECT 2, array_agg(trades) FROM ( SELECT * FROM ... ) t group by 1
  UNION ALL
  SELECT ...
) x
ORDER BY o

Note that with UNION only the first subquery's column names are relevant - the entire union uses column names from the first subquery - so don't bother providing aliases for the others.