如何在无匹配值时返回空数组？PostgreSQL JSON处理疑问

你可以通过过滤全null的行并处理聚合结果为null的情况来实现需求，修改后的查询语句如下：

SELECT 
  l.id,
  COALESCE(
    JSON_AGG(
      JSON_BUILD_OBJECT('id', la.id, 'name', la.name, 'lag_id', la.lag_id)
    ) FILTER (WHERE la.id IS NOT NULL OR la.name IS NOT NULL OR la.lag_id IS NOT NULL),
    '[]'::JSON
  ) as attr_groups
FROM location l
LEFT JOIN location_attr_groups lag ON lag.id = l.l_attr_group_id
LEFT JOIN location_attr la ON la.lag_id = lag.id
GROUP BY l.id;

关键步骤说明： #

1. FILTER 子句：过滤掉location_attr表中所有字段都为null的行（也就是无匹配数据的情况），避免这些全null的行被JSON_AGG聚合成空对象。
2. COALESCE 函数：当过滤后没有可聚合的数据时，JSON_AGG会返回null，用COALESCE把这个null替换成空数组'[]'::JSON。

如果需要同时移除结果中单个字段的null值（而非仅处理全null的情况），可以结合json_strip_nulls使用，调整后的语句：

SELECT 
  l.id,
  COALESCE(
    JSON_AGG(
      json_strip_nulls(JSON_BUILD_OBJECT('id', la.id, 'name', la.name, 'lag_id', la.lag_id))
    ) FILTER (WHERE la.id IS NOT NULL OR la.name IS NOT NULL OR la.lag_id IS NOT NULL),
    '[]'::JSON
  ) as attr_groups
FROM location l
LEFT JOIN location_attr_groups lag ON lag.id = l.l_attr_group_id
LEFT JOIN location_attr la ON la.lag_id = lag.id
GROUP BY l.id;

这样如果某条location_attr记录只有部分字段为null，会自动移除那些null字段；如果没有匹配的记录，则直接返回空数组。

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