CODEWITHSUNDEEP
sqljsonpostgresql
izengod
izengod

Reputation: 1156

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

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 ?

Upvotes: 7

Views: 7779

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 10

Related Questions