如何在PostgreSQL查询中生成包含表名的JSON结果

用户希望从PostgreSQL数据库直接查询出包含表名的JSON格式结果，具体操作及当前情况如下：

1. 创建customer表：

create table if not exists customer (
    _id serial,
    first_name varchar(50),
    constraint pk_customer primary key (_id)
);

2. 插入数据：

insert into customer(first_name) values ('Customer1'), ('Customer2');

3. 执行查询：

SELECT json_agg(customer) as customer FROM customer;

当前得到的结果：

[{"_id":1,"first_name":"Customer1"}, 
 {"_id":2,"first_name":"Customer2"}]

预期结果：

{
   "Customer":[
      {
         "_id":1,
         "first_name":"Customer1"
      },
      {
         "_id":2,
         "first_name":"Customer2"
      }
   ]
}

请问是否可以直接通过数据库实现该需求？

解决方案 #

当然可以直接通过PostgreSQL实现，只需调整查询语句，用json_build_object将表名作为键，json_agg生成的数组作为值即可：

SELECT json_build_object('Customer', json_agg(customer)) AS result FROM customer;

如果不想硬编码表名，想要动态获取当前表名，可以借助PostgreSQL的系统表pg_class来实现：

SELECT json_build_object(
    (SELECT relname FROM pg_class WHERE oid = 'customer'::regclass),
    json_agg(customer)
) AS result FROM customer;

执行上述任意一条查询，就能得到你预期的JSON格式结果。

内容的提问来源于stack exchange，提问作者The Database Starter