如何让PostgreSQL查询在关联对象不存在时返回null而非空JSON？

场景说明 #

我有一个message表，结构如下（修正原表字段笔误，将重复的created_by_agent_id改为created_by_client_id）：

我编写了一条SELECT查询，用于获取消息及其创建者（agent/client）：

SELECT 
id,
json_build_object(
'id', agent.id,
'firstName', agent.first_name,
) AS "createdByAgent",
json_build_object(
'id', client.id,
'firstName', client.first_name,
) AS "createdByClient"
FROM message 
LEFT JOIN agent
ON message.created_by_agent_id = agent.id
LEFT JOIN client
ON message.created_by_client_id = client.id
WHERE id = 10

问题在于，当不存在对应的client或agent时，返回的JSON是包含null字段的对象：

{"id" : null, "firstName" : null, "lastName" : null, "avatarLink" : null}

希望让createdByClient/createdByAgent字段在无对应关联时直接返回null值。

解决方案 #

方法1：使用CASE WHEN判断 #

通过判断关联表的主键是否存在，来决定返回JSON对象还是null：

SELECT 
  id,
  CASE 
    WHEN agent.id IS NOT NULL THEN json_build_object(
      'id', agent.id,
      'firstName', agent.first_name
    ) 
    ELSE NULL 
  END AS "createdByAgent",
  CASE 
    WHEN client.id IS NOT NULL THEN json_build_object(
      'id', client.id,
      'firstName', client.first_name
    ) 
    ELSE NULL 
  END AS "createdByClient"
FROM message 
LEFT JOIN agent ON message.created_by_agent_id = agent.id
LEFT JOIN client ON message.created_by_client_id = client.id
WHERE id = 10;

方法2：使用PostgreSQL的FILTER子句（更简洁） #

PostgreSQL支持在标量表达式上使用FILTER子句，仅当满足条件时才执行JSON构建逻辑，否则返回null：

SELECT 
  id,
  json_build_object(
    'id', agent.id,
    'firstName', agent.first_name
  ) FILTER (WHERE agent.id IS NOT NULL) AS "createdByAgent",
  json_build_object(
    'id', client.id,
    'firstName', client.first_name
  ) FILTER (WHERE client.id IS NOT NULL) AS "createdByClient"
FROM message 
LEFT JOIN agent ON message.created_by_agent_id = agent.id
LEFT JOIN client ON message.created_by_client_id = client.id
WHERE id = 10;

两种方法都能实现需求：当没有对应的agent或client时，createdByAgent或createdByClient字段直接返回null，而非包含null属性的JSON对象。

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