postgreSQL how to convert multiple rows from a table to an array of JSON

Question

ROW_TO_JSON function in postgres converts a row of a table to a JSON object.

select row_to_json(result) from (select * from employee) as result;
select row_to_json(result) from (select * from student) as result;

Gives me three rows:

{"salary":null,"age":65,"address":null,"id":111,"name":"NAME"}
{"salary":null,"age":21,"address":null,"id":222,"name":"SURNAME"}
{"dob":"1997-03-02","name":"Mediocore","passed":true,"id":555}

The first two rows are from employee table whereas the last row is from student table.

What If I want to put the entire resultset from a single table in an array of JSON objects ? eg.

[{"salary":null,"age":65,"address":null,"id":111,"name":"NAME"}, {"salary":null,"age":21,"address":null,"id":222,"name":"SURNAME"}]as a single row instead of two separate rows.

Is there something equivalent to TABLE_TO_JSON ?

what version you run?..

Vao Tsun
– Vao Tsun

2017-05-15 09:34:57 +00:00
Commented May 15, 2017 at 9:34 — Vao Tsun
– Vao Tsun, Commented May 15, 2017 at 9:34
PostgreSQL 9.6 with pgAdmin 4 v4

izengod
– izengod

2017-05-15 09:39:09 +00:00
Commented May 15, 2017 at 9:39 — izengod
– izengod, Commented May 15, 2017 at 9:39
ah, then @a_horse_with_no_name gave best answer :)

Vao Tsun
– Vao Tsun

2017-05-15 09:39:40 +00:00
Commented May 15, 2017 at 9:39 — Vao Tsun
– Vao Tsun, Commented May 15, 2017 at 9:39

user330315 · Accepted Answer · 2017-05-15 09:37:21Z

10

Maybe I'm missing something but this looks like json_agg should do it.

You also don't need the derived table:

select json_agg(row_to_json(employee)) 
from employee;

answered May 15, 2017 at 9:37

user330315

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

1 Comment

Vao Tsun Over a year ago

row_to_json is first json function, how did you know OPs not on 9.2?

Collectives™ on Stack Overflow

postgreSQL how to convert multiple rows from a table to an array of JSON

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related