Postgresql row to json as array of values (without keys)

Question

I'm trying to map the results of a query to JSON in PostgreSQL.

I can do the mapping with the following query:

select row_to_json(t) from (select * from mytable) t;

For example, let the table 'mytable' contains three columns and three records, then the query can return:

{"first":11,"second":12,"third":13}
{"first":21,"second":22,"third":23}
{"first":31,"second":32,"third":33}

The query above returns each row as a JSON object. But all the keys will be the same for each row.

I want to get only values as JSON array like that:

[11,12,13]
[21,22,23]
[31,32,33]

How can I do that efficiently in PostgreSQL?...

GMB · Accepted Answer · 2020-10-16 23:14:11Z

2

You can use json_build_array() - but it requires enumerating the column names:

select json_build_array(first, second, third) res
from mytable t

| res          |
| :----------- |
| [11, 12, 13] |
| [21, 22, 23] |
| [31, 32, 33] |

answered Oct 16, 2020 at 23:14

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1