PostgreSQL基于jsonb数组内匹配键值对实现多表关联查询的问题

核心思路是借助PostgreSQL原生的jsonb_array_elements函数展开encounter表中document字段下的account数组，逐行匹配符合条件的account记录的标识符数组，无需硬编码数组索引。

最优查询写法（推荐，无重复返回问题） #

使用EXISTS子查询实现，性能最优，匹配到符合条件的元素就会终止判断，不会产生重复的encounter记录：

SELECT encounter.*
FROM encounter
WHERE EXISTS (
    SELECT 1
    -- 展开encounter的account数组为单个账户元素行
    FROM jsonb_array_elements(encounter.document -> 'account') AS enc_account(acc_item)
    JOIN account 
        ON account.foo = 'bar'
        -- 判断account的identifier数组是否包含当前账户元素
        AND account.document -> 'identifier' @> jsonb_build_array(acc_item)
);

JOIN写法（可选） #

如果更习惯JOIN语法可以使用该写法，注意加DISTINCT避免重复返回：

SELECT DISTINCT encounter.*
FROM encounter
CROSS JOIN LATERAL jsonb_array_elements(encounter.document -> 'account') AS enc_account(acc_item)
JOIN account 
    ON account.foo = 'bar'
    AND account.document -> 'identifier' @> jsonb_build_array(acc_item);

性能优化建议 #

可以通过创建GIN索引大幅提升JSONB包含操作的查询效率：

-- 给account表的identifier数组建GIN索引
CREATE INDEX idx_account_doc_identifier ON account USING GIN ((document -> 'identifier'));

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