PostgreSQL如何为JSON数组内各对象分别生成first与second数组交叉连接

解决方案 #

你不需要使用递归CTE，用PostgreSQL的LATERAL连接就能实现按单个JSON对象分组做交叉连接的需求，LATERAL允许关联前置FROM项中生成的列值做后续计算，刚好适配你需要对每个对象单独展开数组的场景。

正确查询语句 #

with t1(obj) as (
  -- 第一步把JSON数组拆分为单个对象行
  select jsonb_array_elements('[{"first":["1 - one", "2 - two", "3 - three"], "second": [1, 2]},{"first":["4 - four", "5 - five"], "second": [10]}]'::jsonb)
)
select 
  f.first,
  s.second::smallint
from t1
-- 对当前行的单个对象展开first数组
cross join lateral jsonb_array_elements_text(t1.obj->'first') as f(first)
-- 对同一行对象展开second数组，和当前对象的first元素交叉连接
cross join lateral jsonb_array_elements_text(t1.obj->'second') as s(second)
order by first, second;

原查询错误原因 #

你原来的写法是先全局展开所有对象的first数组得到全量的first值列表，再全局展开所有对象的second数组得到全量的second值列表，两个独立的列表做交叉连接自然就会出现跨对象匹配的问题，生成所有可能的全局组合。

而LATERAL连接的逻辑是，遍历t1中每一个单独的JSON对象，只对当前对象的first和second数组做展开交叉，得到的组合只会属于当前对象，多个对象的结果会自动合并返回，刚好符合你的需求。

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