PostgreSQL能否省略嵌套语法直接将SELECT语句转为JSON数组

我从相关技术资料了解到，如果要将一组关联行聚合为对象数组，需要使用如下语法：

(select to_json(C) from ( /* 子查询 */ ) C)

假设我有三张表：user（用户表）、creature（生物表），以及二者的关联表user_creature：

现在需要查询每个用户，以及对应用户拥有的所有生物，我编写了如下查询语句：

select to_json(T)
from (
         select "user".id        as user_id,
                (select to_json(C) -- 核心嵌套语法
                 from (
                          select name, height
                          from creature
                                   inner join "user_creature" uc on creature.id = "uc".creature_id
                                   inner join "user" u on "uc".user_id = u.id
                          where u.id = user_id
                      ) C)       as "creatures" -- 生成生物JSON数组
         from "user"
     ) T;

上述查询可以成功返回用户列表及其关联的生物数据：

我想知道是否可以省略查询中的select和from关键字，将写法简化为如下形式：

select to_json(T)
from (
         select "user".id        as user_id,
                to_json( -- 直接对SELECT语句调用to_json
                      select name, height
                      from creature
                               inner join "user_creature" uc on creature.id = "uc".creature_id
                               inner join "user" u on "uc".user_id = u.id
                      where u.id = user_id
                  )  as "creatures"
         from "user"
     ) T;

回答 #

你提出的这种简化写法不可行，运行时会直接触发语法错误。
原因是PostgreSQL的to_json函数仅支持接收单个值或者单行记录作为入参，不能直接接收返回多行结果的SELECT子查询。你最初写法里外层嵌套的SELECT to_json(C) FROM (子查询) C逻辑，是先把子查询返回的多行结果集逐行转换为JSON对象，再聚合为JSON数组，这一步逻辑是不能省略的。

如果想要简化代码，更推荐使用json_agg函数来实现相同需求，写法更简洁，执行性能也更优：

select to_json(T)
from (
         select "user".id as user_id,
                json_agg(
                    json_build_object('name', creature.name, 'height', creature.height)
                ) as creatures
         from "user"
         left join user_creature uc on "user".id = uc.user_id
         left join creature on uc.creature_id = creature.id
         group by "user".id
     ) T;

这种写法避免了多层嵌套子查询，后续调整查询字段或者关联逻辑时也更方便维护。

内容的提问来源于stack exchange，提问作者Ruslan Plastun