PostgreSQL中LATERAL查询场景下json_agg行为疑问求解

聚合函数的作用范围由执行聚合时的分组粒度决定，你用到的两类查询的聚合逻辑完全不在同一个粒度上，所以返回结果差异很大。

CTE查询返回单个数组的原因 #

你的CTE逻辑是先把全表所有行的payloadJSON数组，用jsonb_array_elements（或同类展开函数）统一展开为全局的单个JSON元素结果集，筛选kind = person后直接执行json_agg。此时你没有指定GROUP BY条件，聚合的作用范围是整个筛选后的全局结果集，自然会把所有符合条件的元素合并为单个JSON数组返回，总输出只有1行。

LATERAL关联返回多行数组合的原因 #

LATERAL关联的核心逻辑是：子查询会针对外层segments表的每一行单独执行一次。
你把json_agg写在了LATERAL子查询内部，相当于每次子查询执行时，仅处理外层当前一行的payload数据，聚合后返回的是当前行符合条件的元素组成的JSON数组。外层查询没有做额外的全局聚合，segments表有3行数据，最终自然会返回3行独立的JSON数组。

CROSS JOIN LATERAL仅会过滤掉子查询返回空结果的外层行，不会改变「子查询逐行执行、聚合粒度为单行」的逻辑，所以结果和普通LATERAL一致。

如果要通过LATERAL写法得到全局合并的单个数组，可选两种方案：

方案1：把聚合逻辑移到外层（推荐） #

LATERAL子查询仅负责展开每行的payload、筛选符合条件的元素，外层统一做全局聚合：

SELECT json_agg(elem) AS merged_persons
FROM segments,
LATERAL jsonb_array_elements(payload) elem
WHERE elem->>'kind' = 'person';

方案2：子查询行级聚合后外层再合并 #

如果必须保留子查询内的行级聚合逻辑，可以在外层对所有行的数组做二次合并，PostgreSQL 14及以上版本可以直接用jsonb_concat_agg实现：

SELECT jsonb_concat_agg(row_level_agg) AS merged_persons
FROM segments,
LATERAL (
  SELECT jsonb_agg(elem) AS row_level_agg
  FROM jsonb_array_elements(payload) elem
  WHERE elem->>'kind' = 'person'
) sub
WHERE sub.row_level_agg IS NOT NULL;

低版本可以先把行级数组展开再重新聚合：

SELECT jsonb_agg(elem) AS merged_persons
FROM segments,
LATERAL (
  SELECT jsonb_agg(elem) AS row_level_agg
  FROM jsonb_array_elements(payload) elem
  WHERE elem->>'kind' = 'person'
) sub,
LATERAL jsonb_array_elements(sub.row_level_agg) elem
WHERE sub.row_level_agg IS NOT NULL;

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