PostgreSQL 13如何基于JSONB字符串数组关联两张表？

你可以通过展开JSONB数组 + 表关联 + 字符串聚合的方式实现需求，具体SQL如下：

SELECT
    c.客户ID,
    c.姓名,
    STRING_AGG(a.活动名称, ', ' ORDER BY idx) AS 活动名称列表
FROM
    客户表 c
JOIN LATERAL (
    -- 展开JSONB数组，同时保留原数组中的顺序
    SELECT
        jsonb_array_elements_text(c.活动列表) AS 标识符,
        row_number() OVER () AS idx
) arr ON true
JOIN 活动表 a ON arr.标识符 = a.标识符
GROUP BY
    c.客户ID, c.姓名
ORDER BY
    c.客户ID;

关键逻辑说明： #

• jsonb_array_elements_text(c.活动列表)：将JSONB类型的字符串数组拆分为单行文本值，每个活动标识符对应一行数据。
• LATERAL：让子查询可以引用主查询的客户表数据，实现每个客户的数组行与客户数据的关联。
• STRING_AGG(a.活动名称, ', ' ORDER BY idx)：把关联到的活动名称按原数组的顺序拼接成逗号分隔的字符串，ORDER BY idx确保结果顺序和原数组一致。

执行后将得到期望结果：

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