PostgreSQL LEFT JOIN返回含null属性对象而非null的解决方法

左连接（LEFT JOIN）未匹配到layout表对应记录时，layout表的所有返回字段都会为NULL，但json_build_object函数只要接收到入参，哪怕所有入参都是NULL，也会返回一个包含对应键、值全为NULL的JSON对象，不会整体返回NULL，因此出现了你看到的不符合预期的结果。
你之前使用COALESCE无效的原因也在此：COALESCE只会判断入参本身是否为NULL，而此时json_build_object返回的是结构完整的JSON对象（只是内部值为NULL），不是NULL值，自然无法触发COALESCE的替换逻辑。

两种写法都可以得到你期望的结果：

方法1：通过CASE判断关联是否命中 #

直接判断关联到的layout主键是否为NULL，未命中时直接返回NULL，命中时再生成JSON对象：

SELECT
    p.id,
    CASE
        WHEN l.id IS NULL THEN NULL
        ELSE json_build_object(
            'id', l.id,
            'layout_name', l.layout_name
        )
    END AS layout
FROM my_schema.page p
LEFT JOIN my_schema.layout l
    ON l.id = p.layout_id;

方法2：使用标量子查询生成JSON #

把JSON生成逻辑放到关联标量子查询里，子查询查不到匹配记录时会自然返回NULL，不需要额外判断：

SELECT
    p.id,
    (
        SELECT json_build_object(
            'id', l.id,
            'layout_name', l.layout_name
        )
        FROM my_schema.layout l
        WHERE l.id = p.layout_id
    ) AS layout
FROM my_schema.page p;

执行以上任意一段SQL，id为2的page记录对应的layout字段都会返回NULL，和你预期的结果完全一致。

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