如何将PostgreSQL嵌套子查询返回的null值转换为空数组

你遇到的返回NULL问题由两个点共同导致：

1. 聚合函数的默认行为：PostgreSQL中除count()之外的所有聚合函数，在没有匹配到任何可聚合的输入行时，都会返回NULL，而非空集合。
2. 隐式类型不匹配：你定义的collaborators字段是BIGINT[]类型，但用户表的id是serial（本质为INTEGER类型），写c.id = ANY(s.collaborators)时存在类型匹配问题，即便是id=1的项目协作者数组存了[2,3]，数据库也无法正确匹配到对应的用户行，最终子查询无返回结果，触发聚合返回NULL。

你之前尝试的方案不生效的原因很明确：

• 类型强转只能转换已有值的类型，对NULL做任何类型转换结果还是NULL，而且你之前的强转没有加在关联条件上，解决不了类型匹配失败的问题
• NULLIF的逻辑是两个参数相等时返回NULL，和你需要的「NULL转空数组」需求完全相反

首先修正关联条件的类型匹配问题，再用coalesce()函数包裹子查询的聚合结果，当聚合返回NULL时，显式返回空JSON数组即可。

写法1：保留原有的array_agg + to_json逻辑 #

SELECT
    s.id,
    s.project_name,
    COALESCE(
        (
            SELECT to_json(array_agg(c.*))
            FROM my_schema.user as c
            WHERE c.id::BIGINT = ANY(s.collaborators)
        ),
        to_json(ARRAY[]::json[])
    ) as collaborators,
    json_build_object(
        'id', u.id,
        'user_name', u.user_name
    ) as user
FROM my_schema.project s
INNER JOIN my_schema.user u
    ON s.owner = u.id
WHERE s.id = 1;

写法2：用json_agg简化逻辑（更推荐） #

json_agg可以直接聚合返回JSON数组，不需要嵌套to_json + array_agg，写法更简洁：

SELECT
    s.id,
    s.project_name,
    COALESCE(
        (
            SELECT json_agg(c.*)
            FROM my_schema.user as c
            WHERE c.id::BIGINT = ANY(s.collaborators)
        ),
        '[]'::json
    ) as collaborators,
    json_build_object(
        'id', u.id,
        'user_name', u.user_name
    ) as user
FROM my_schema.project s
INNER JOIN my_schema.user u
    ON s.owner = u.id;

以上写法无论项目有没有协作者，collaborators字段都会返回数组类型：有匹配用户时返回用户对象数组，无匹配时返回空数组[]，不会再出现NULL。

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