时间戳

首页 > 解决方案 > postgres 数组到列的数组

sql - postgres 数组到列的数组

问题描述

我的桌子样本

Table_A
id integer
arr varchar(20) []

样本数据

id.         arr
1.    {{'a', 'b', 'c'}, {'d', 'test1', 'sample1'}}
2.    {{'sample2', sample3, sample4'}}
3.     null
4.    {{'sample6', 'sample7', 'test done'}}

我想选择将数据设为：

id.     col1.       col2          col3  
1       'a'         'b'.          'c'
1.      'd'.       'test1'.      'sample1'
2.      'sample2'  'sample3'.    'sample4'
3.      null        null          null
4.     'sample6'.  'sample7'.    'test done'

数据保证在数组中具有 5 个元素 if not null。为方便起见，上面的示例数据包含 3 个元素。这是一个相当大的表，会随着时间的推移而增长。现在我正在使用unnest然后row_number() over ()然后使用 id 加入它们。有没有更好的方法来做到这一点。

标签： sqlpostgresqlpostgresql-9.4

解决方案

常规多维数组的 unnest() 存在问题，它扩展了所有维度的所有元素，而不仅仅是第一个。因此，这些类型无法完成此操作。

一种可能的解决方法是将数组转换为 JSON 数组。对于 JSON 数组，这不是问题：

演示：db<>小提琴

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a,
    json_array_elements(array_to_json(arr)) elems

json_array_elements()扩展数组的第一个维度，因此子数组被移动到它们的一行中。然后通过索引简单地获取它们的元素。

上面的查询消除了 NULL 行，因为它的逗号表示法（实际上，INNER JOIN LATERAL在这种情况下，它是快捷方式）。这可以使用 anLEFT OUTER JOIN LATERAL来避免：

演示：db<>小提琴 

SELECT
    id,
    elems ->> 0 AS col1,
    elems ->> 1 AS col2,
    elems ->> 2 AS col3
FROM
    table_a
LEFT JOIN LATERAL
    json_array_elements(array_to_json(arr)) elems ON TRUE

推荐阅读