Selected columns into a sorted array

I have a problem. I don't know how it is resolved. I would do it in a database because I don't want to forget in other queries and to add in select. This is a query that is a view materialize. In one row, I get 5 different prices as number or null. In the future, prices can be more (6, 7 or more).

Select id price1, price2, price3 ,price, price5
From materialize_view

and result

// 1, 500, null, 20 ,null, 10

I want this row to sort from smaller to the largest in one column and nulls last. If I use an object, I wont't can to sort this value of column. So I invented something like this:

[
    ["e",10],
    ["c",20],
    ["a",500],
    ["b",null],
    ["d",null],
]

This is the result that I would get at the end of the row.

I tried to use jsonb_each, unnest but every time I got another problem

Solution

I create a fake table using VALUES and then I use ARRAY_AGG on it. This works because for aggregate functions, including ARRAY_AGG, you can use aggregate expressions which allow ORDER BY.

SELECT
    id,
    (
        SELECT ARRAY_AGG(val ORDER BY val)
        FROM (VALUES (price1),(price2),(price3),(price4),(price5)) AS vals(val)
    ) AS prices
FROM materialize_view;

 id |        prices
----+-----------------------
  1 | {10,20,500,NULL,NULL}

If I want to have each item in my array to be a tuple of label and value, then postgres doesn't let you have mixed type arrays:

ERROR: ARRAY types text and integer cannot be matched

So this doesn't work select ARRAY['a', 1];, but this does select ARRAY['a', 'b'];.
If I'm desperate then I can use the ROW syntax and get the following, but I'm unsure if it's easily consumable as it looks like it ends up being serialised as text, but maybe that's just something the psql client does:

SELECT id,
    (
        SELECT ARRAY_AGG(ROW(label,val) ORDER BY val)
        FROM (VALUES ('a', price1),('b',price2),('c',price3),('d',price4),('e',price5)) AS vals(label, val)
    ) AS prices
FROM materialize_view;

 id |                   prices
----+---------------------------------------------
  1 | {"(e,10)","(c,20)","(a,500)","(b,)","(d,)"}

If I match the type of price and use numbers in order to use arrays:

SELECT id,
    (
        SELECT ARRAY_AGG(ARRAY[idx,val] ORDER BY val)
        FROM (VALUES (1, price1),(2,price2),(3,price3),(4,price4),(5,price5)) AS vals(idx, val)
    ) AS prices
FROM materialize_view;

 id |                  prices
----+-------------------------------------------
  1 | {{5,10},{3,20},{1,500},{2,NULL},{4,NULL}}

If instead I want to go pure JSON:

SELECT id,
    (
        SELECT json_agg(json_build_array(label, val) ORDER BY val)
        FROM (VALUES ('a', price1),('b',price2),('c',price3),('d',price4),('e',price5)) AS vals(label, val)
    ) AS prices
FROM materialize_view;

 id |                            prices
----+--------------------------------------------------------------
  1 | [["e", 10], ["c", 20], ["a", 500], ["b", null], ["d", null]]

This could be switched to use jsonb_agg and jsonb_build_array if preferred.