PostgreSQL：如何在JOIN中使用返回集合的函数结果

你遇到的问题是因为jsonb_array_elements是返回集合的函数，不能直接放在IN的括号里——IN后面要么是明确的值列表，要么是返回单列结果的子查询，直接嵌套集合函数会触发语法错误。下面提供两种可行的写法：

方法1：使用JOIN LATERAL展开JSON数组（推荐） #

这种方式是PostgreSQL处理关联表中JSON数组的标准做法，性能更优，逻辑也更清晰：

SELECT *
FROM someTable st
-- 先展开JSON数组，把每个manuscript元素拆成单独的行
JOIN LATERAL jsonb_array_elements(st.manuscripts->'manuscripts') AS ms(elem) ON true
JOIN someOtherTable sot 
  ON someCondition -- 保留你原来的关联条件
  AND sot.document_id = (ms.elem->>'documentId')::int;

说明： #

• JOIN LATERAL允许我们引用前面表（someTable）的列，动态展开每行的JSON数组；
• 不需要额外的::json类型转换，因为jsonb_array_elements返回的elem本身就是jsonb类型，直接用->>提取字符串再转成int即可。

方法2：用子查询配合IN条件 #

如果一定要用IN语法，可以把集合函数放到子查询里，让它返回单列的documentId集合：

SELECT *
FROM someTable st
JOIN someOtherTable sot 
  ON someCondition -- 保留你原来的关联条件
  AND sot.document_id IN (
    -- 子查询提取当前行的所有documentId
    SELECT (elem->>'documentId')::int
    FROM jsonb_array_elements(st.manuscripts->'manuscripts') AS elem
  );

注意： #

• 如果某行的manuscripts->'manuscripts'是空数组，子查询会返回空结果，此时IN条件不成立，该行不会和someOtherTable产生关联。

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