如何在Postgresql中将两列数组拆分为行

fcy6dtqo 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(3)|浏览(620)

运行此查询时：

SELECT id,col1,col2
  FROM  app_data.content_cards

我得到了这样一张table：

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

我现在要做的是得到同样的信息，但是数组被拆分成行，所以我得到了这样的结果：

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

正如您所看到的，我不希望在"col1"和"col2"中获得具有空值的行。
非常感谢!

postgresql

来源：https://stackoverflow.com/questions/75155979/how-to-split-two-columns-array-into-rows-in-postgresql

3条答案

按热度按时间

vecaoik11#

假设col1和col2获得相同数量的元素，您可以使用UNNEST函数创建一个视图，将其拆分，然后查询它。

WITH unnested_col1 AS (
  SELECT id, unnest(col1) as col1, ROW_NUMBER() OVER (PARTITION BY id ORDER BY unnest(col1)) as row_num
  FROM app_data.content_cards
), unnested_col2 AS (
  SELECT id, unnest(col2) as col2, ROW_NUMBER() OVER (PARTITION BY id ORDER BY unnest(col2)) as row_num
  FROM app_data.content_cards
)
SELECT unnested_col1.id, unnested_col1.col1, unnested_col2.col2
FROM unnested_col1
JOIN unnested_col2 ON unnested_col1.id = unnested_col2.id AND unnested_col1.row_num = unnested_col2.row_num

赞(0）回复(0）举报 2023-01-25

nimxete22#

我们可以在两列上使用UNNEST：

SELECT id,
UNNEST(col1) AS col1,
UNNEST(col2) AS col2
FROM content_cards
ORDER BY id;

参见documentation
请在此试用：db<>fiddle

赞(0）回复(0）举报 2023-01-25

pbossiut3#

为了确保匹配的数组项在同一行结束，将两个数组传递给一个unnest（）调用：

SELECT c.id, u.*
FROM content_cards c
    cross join unnest(col1, col2) as u(col1, col2)
ORDER BY id;

这保证了即使一个数组的元素数不同，来自匹配数组位置的元素也会出现在同一行中（缺少的元素将用null值填充）。
如果某些数组可能根本没有元素，但您仍然希望看到基表的id，请使用外部连接：

FROM content_cards c
   left join unnest(col1, col2) as u(col1, col2) on true

Online example

赞(0）回复(0）举报 2023-01-25

我来回答

如何在Postgresql中将两列数组拆分为行

3条答案

相关问题

热门标签

最新问答