Postgres查询构造嵌套结果对象时报子查询仅能返回单列错误如何解决

错误原因 #

• 作为字段返回的嵌套子查询同时返回了event、channel两个列，PostgreSQL要求该类标量子查询只能返回单个列，因此触发语法错误。
• 子查询内的INNER JOIN关联同表逻辑完全冗余，外层查询已经获取了当前行的所有字段，无需重复查询同一条记录。

实现方案 #

PostgreSQL 提供json_build_object函数可直接构造指定结构的JSON对象，无需嵌套子查询，参考语句如下：

SELECT json_build_object(
    'id', mt.id::text,
    'data', json_build_object(
        'event', mt.name,
        'channel', mt.channel_name
    )
) AS result
FROM my_table mt
WHERE mt.name ILIKE '%name 2%';

执行上述语句即可返回你期望的JSON结构。如果匹配条件可能命中多条记录，需要返回数组结构，可在外层包裹json_agg聚合函数。

内容的提问来源于stack exchange，提问作者Mike K