PostgreSQL 11中按ID数组顺序聚合文本并保留重复项的方法

原查询无法处理重复ID的核心原因：把unnest(report_template.template_blocks_id)放在WHERE条件中时，会先对数组元素去重再关联report_template_block表，导致重复ID对应的code只会被聚合一次。要保留数组原始顺序和重复ID的内容，需要先保留数组元素的位置信息，再关联表进行聚合。

正确SQL查询语句 #

SELECT 
    rt.id AS report_template_id,
    string_agg(rtb.code, '\n' ORDER BY u.ordinality) AS aggregated_code
FROM 
    report_template rt
LEFT JOIN LATERAL 
    unnest(rt.template_blocks_id) WITH ORDINALITY AS u(block_id, ordinality) ON true
LEFT JOIN 
    report_template_block rtb ON rtb.id = u.block_id
GROUP BY 
    rt.id;

逻辑说明 #

1. unnest(... WITH ORDINALITY)：将数组拆分为行的同时，保留每个元素在原数组中的位置序号（ordinality字段），这是保证聚合顺序、不丢失重复ID的关键。
2. LEFT JOIN LATERAL：对每条report_template记录单独处理其数组拆分结果，确保数组里的每个元素（包括重复ID）都能关联到对应的report_template_block记录。
3. string_agg(..., ORDER BY u.ordinality)：按照数组元素的原始顺序聚合code字段，既保证结果顺序和原数组一致，又能保留重复ID对应的内容。

额外说明 #

• 用LEFT JOIN而非INNER JOIN，可以确保即使数组中有未在report_template_block表匹配到的ID，对应的模板记录仍会被保留（此时对应位置的code为NULL，若要过滤无匹配的情况，可改为INNER JOIN）。
• 该语法适配PostgreSQL 11及以上版本，符合你的环境要求。

内容的提问来源于stack exchange，提问作者Максим Белов