CODEWITHSUNDEEP
sqlarraysjsonpostgresql
Andrei Terentiev
Andrei Terentiev

Reputation: 11

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

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?...

Upvotes: 1

Views: 1933

Answers (1)

GMB
GMB

Reputation: 222382

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

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

Demo on DB Fiddle:

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

Upvotes: 2

Related Questions