Generate a JSON array of values for each row

Question

Assuming the following CTE:

with mytable as (
    select column1 as foo, column2 as bar, column3 as baz
    from (values
      ('a', 'b', 1),
      ('c', 'd', 2)
    ) v
)

Using array_agg() ouputs an array of values:

select
    array_agg(v)
from mytable v;

-- {"(a,b,1)","(c,d,2)"}

but surprisingly (to me at least), using to_json() on this array restores the field names into an object for each row

select
    to_json(array_agg(v))
from mytable v;

-- [{"foo":"a","bar":"b","baz":1},{"foo":"c","bar":"d","baz":2}]

How can we make PostgreSQL output an array of arrays instead, rendering each row as an array of values?

select
    something(v)
from mytable v;

-- [["a", "b", 1],["c", "d", 2]]

Answer 1

You can convert a row into a json, then unnest the key/value pairs and then aggregate the values back:

with mytable (foo, bar, baz) as (
  values
    ('a', 'b', 1),
    ('c', 'd', 2)
)
select jsonb_agg(x.vals)
from mytable m
  cross join lateral (
    select jsonb_agg(value order by idx) as vals
    from json_each(row_to_json(m)) with ordinality as t(key,value,idx)
  ) x

It's important to use json to convert the row, if the order of the column values in the array is important for you.

If you need this often, you can put this into a function.

---

If the order of the column values in the array isn't important, you can use a JSON path function:

select jsonb_path_query_array(to_jsonb(m), '$.keyvalue().value')
from mytable m;

Answer 2

Besides the answer from a_horse_with_no_name, I just found a way to achieve this, assuming column names are known:

with mytable as (
    select column1 as foo, column2 as bar, column3 as baz
    from (values
      ('a', 'b', 1),
      ('c', 'd', 2)
    ) v
)

select
    to_json(array_agg(x.vals))
from (
    select
        json_build_array(
            v.foo,
            v.bar,
            v.baz
        ) as vals
    from mytable v
) x
;

-- [["a", "b", 1],["c", "d", 2]]