sql - postgres 数组到列的数组

Question

我的表格示例

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 个元素。为了方便起见，上面的示例数据包含 3 个元素。这是一个相当大的表，并且会随着时间的推移而增长。现在我正在使用 unnest 执行此操作，然后使用 row_number() over () 然后使用 id 将它们连接起来。有没有更好的办法。

Answer 1

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

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

demo:db<>fiddle

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 的快捷方式)。可以使用 LEFT OUTER JOIN LATERAL 来避免这种情况:

demo:db<>fiddle

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