PostgreSQL用JSON数组提取ID做表关联时set returning函数报错如何处理

错误原因 #

jsonb_array_elements是集合返回函数，PostgreSQL 不允许将这类返回多行结果的函数直接写在 JOIN 关联条件中，所以触发了你遇到的报错。

方案1：使用 LATERAL JOIN（推荐） #

你之前查阅的 LATERAL JOIN 完全适配当前场景，它支持引用前置表的字段完成 JSON 数组展开后再关联，实现代码如下：

SELECT m.*
FROM jsontable t
CROSS JOIN LATERAL jsonb_array_elements(t.response -> 'SCS') AS scs_arr(item)
JOIN message m 
  ON m.msg_id = (scs_arr.item ->> 'referenceId')::int
  AND m.status = 1;

执行后会返回两条匹配结果，对应 msg_id 为 215 和 544 的有效消息记录。

方案2：无需 LATERAL 的替代实现 #

如果不想使用 LATERAL 语法，也可以通过子查询先提取所有待匹配的 referenceId 再做关联：

SELECT m.*
FROM message m
WHERE m.status = 1
AND m.msg_id IN (
  SELECT (jsonb_array_elements(response -> 'SCS') ->> 'referenceId')::int
  FROM jsontable
);

该写法将集合返回函数放在子查询中先完成 ID 提取，再通过 IN 条件做匹配，最终得到的结果和方案1完全一致。

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