sql - postgreSQL 如何将表中的多行转换为 JSON 数组

Question

postgres 中的

ROW_TO_JSON 函数将表格的一行转换为 JSON 对象。

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

给我三行:

{"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}

前两行来自 employee 表，最后一行来自 student 表。

如果我想将单个表中的整个结果集放入一个 JSON 对象数组中怎么办？例如。

[{"salary":null,"age":65,"address":null,"id":111,"name":"NAME"}, {"salary":null,"age ":21,"address":null,"id":222,"name":"SURNAME"}]作为单行而不是两行。

是否有等同于 TABLE_TO_JSON 的东西？

Answer 1

也许我遗漏了什么，但这看起来像 json_agg 应该做的。

您也不需要派生表:

select json_agg(row_to_json(employee)) 
from employee;