LEFT JOIN结合json_build_object返回含null的JSON对象，需改为返回null

数据库结构与测试数据 #

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.city (
    id serial PRIMARY KEY,
    city_name VARCHAR(15) NOT NULL
);
CREATE TABLE IF NOT EXISTS my_schema.user (
    id serial PRIMARY KEY,
    city_id BIGINT REFERENCES my_schema.city (id) DEFAULT NULL
);

INSERT INTO my_schema.city VALUES
    (1, 'Toronto'),
    (2, 'Washington');

INSERT INTO my_schema.user VALUES
    (1);

原始查询与当前结果 #

原始查询语句：

SELECT
  u.id,
  json_build_object(
    'id', c.id,
    'city_name', c.city_name
  ) as city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

当前返回结果：

[
    {
        "id": 1,
        "city": {
            "id": null,
            "city_name": null
        }
    }
]

预期结果 #

[
    {
        "id": 1,
        "city": null
    }
]

错误尝试说明 #

曾尝试以下语句，但执行报错：

SELECT
  u.id,
  COALESCE(json_build_object(
    'id', c.id,
    'city_name', c.city_name
  ) FILTER (WHERE u.city_id IS NOT NULL), 'none') as city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

报错原因：FILTER 子句仅适用于聚合函数（如SUM()、COUNT()），而json_build_object属于普通函数，不能搭配FILTER使用。

解决方案 #

方法一：使用CASE条件判断（推荐） #

通过判断city_id是否为null，决定返回JSON对象还是null，逻辑清晰且易于维护：

SELECT
  u.id,
  CASE
    WHEN u.city_id IS NOT NULL THEN json_build_object('id', c.id, 'city_name', c.city_name)
    ELSE NULL
  END AS city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

方法二：利用NULLIF和COALESCE组合 #

将全null的JSON对象与预定义的全null JSON字符串对比，匹配则返回null：

SELECT
  u.id,
  COALESCE(
    NULLIF(json_build_object('id', c.id, 'city_name', c.city_name), '{"id":null,"city_name":null}'::json),
    NULL
  ) AS city
FROM my_schema.user u
LEFT JOIN my_schema.city c
   ON c.id = u.city_id

注意：此方法依赖固定的JSON结构，若后续city表字段变更，需同步修改对比的JSON字符串，灵活性较差。

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