PostgreSQL如何将两列数组拆分为对应行？

原查询及结果 #

执行以下SQL查询：

SELECT id,col1,col2
  FROM  app_data.content_cards

得到的结果如下：

+----+-------------------------------+--------+
| id | col1                          | col2   |
+----+-------------------------------+--------+
| 90 | {'one', 'two', 'three'}       | {1,2,3}|
+----+-------------------------------+--------+
| 91 | {'abc', 'def'}                | {1,2}  |
+----+-------------------------------+--------+
| 92 | {'asdf}                       | {1}    |
+----+-------------------------------+--------+

需求说明 #

需要将上述结果中的col1和col2数组拆分为单独的行，同时过滤掉col1或col2为null的行，最终得到如下格式的结果：

+----+---------------------+-------+
| id | col1                | col2  |
+----+---------------------+-------+
| 90 | one                 | 1     |
+----+---------------------+-------+
| 90 | two                 | 2     |
+----+---------------------+-------+
| 90 | three               | 3     |
+----+---------------------+-------+
| 91 | abc                 | 1     |
+----+---------------------+-------+
| 91 | def                 | 2     |
+----+---------------------+-------+
| 92 | asdf                | 1     |
+----+---------------------+-------+

解决方案 #

以下针对两种常见数据格式提供处理方法：

场景1：数组为原生数据库数组类型 #

如果col1和col2是数据库原生数组类型（比如PostgreSQL的text[]、int[]），直接使用unnest函数结合元素位置关联，确保对应元素匹配：

SELECT 
    cc.id,
    u1.unnest_col1 AS col1,
    u2.unnest_col2 AS col2
FROM 
    app_data.content_cards cc,
    unnest(cc.col1) WITH ORDINALITY AS u1(unnest_col1, pos),
    unnest(cc.col2) WITH ORDINALITY AS u2(unnest_col2, pos)
WHERE 
    u1.pos = u2.pos
    AND unnest_col1 IS NOT NULL
    AND unnest_col2 IS NOT NULL;

场景2：数组为字符串格式 #

如果col1和col2是伪装成数组的字符串，需要先清理字符串格式，转换为原生数组后再拆分：

SELECT 
    cc.id,
    u1.unnest_col1 AS col1,
    u2.unnest_col2 AS col2
FROM 
    app_data.content_cards cc,
    unnest(
        string_to_array(
            trim(col1, '{}'), -- 去除字符串首尾的{}
            ', ' -- 按逗号加空格分割元素
        )
    ) WITH ORDINALITY AS u1(unnest_col1, pos),
    unnest(
        string_to_array(
            trim(col2, '{}'),
            ', '
        )
    ) WITH ORDINALITY AS u2(unnest_col2, pos)
WHERE 
    u1.pos = u2.pos
    AND unnest_col1 IS NOT NULL
    AND unnest_col2 IS NOT NULL
    -- 过滤格式错误的字符串（比如id=92的{'asdf}）
    AND col1 LIKE '{%}' 
    AND col2 LIKE '{%}';

关键说明 #

• WITH ORDINALITY用于获取数组元素的位置索引，保证col1和col2的元素按原数组顺序一一对应拆分。
• u1.pos = u2.pos确保每行的两个元素来自原数组的同一位置。
• 额外的格式过滤条件可避免拆分出无效值。

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