如何从雪花中的记录数组中选择单个值

Question

我的雪花表中有一组记录，如：

select  * from dw.public.arr_table;

['val1't'val2't'val3't'val4't'val5't, 'val6't'val7't'val8't'val9't'val10't ]
['val1't'val2't'val3't'val4't'val5't, 'val6't'val7't'val8't'val9't'val10't ]
['val1't'val2't'val3't'val4't'val5't, 'val6't'val7't'val8't'val9't'val10't ]
['val1't'val2't'val3't'val4't'val5't, 'val6't'val7't'val8't'val9't'val10't ]

每个记录都是一个数组。如何迭代每个数组并选择val1&val6？我预计结果是：

col1 col2val1 val6

val1&val6分别位于柱col1和柱col2下方。

Answer 1

格式化示例数据的方式没有意义。我想你想要

select split(['val1tval2tval3tval4tval5t', 'val6tval7tval8tval9tval10t'][0],'t')[0]::varchar as col1,
split(['val1tval2tval3tval4tval5t', 'val6tval7tval8tval9tval10t'][1],'t')[0]::varchar as col2

Answer 2

因此，如果您的数据是"具有制表符分隔值的字符串编码数组"；，然后像这样的CTE重新创建你的数据：

with cte(col) as (
select parse_json(column1) from values    
('["val1tval2tval3tval4tval5t", "val6tval7tval8tval9tval10t"]')
)

这给了我们一个有两个条目的数组，如果我们总是有两个条目的话，我们可以访问这些可以硬编码的数组索引：

select 
col
,col[0] as a0
,col[1] as a1
from cte

给予：

COL	A0	A1
["val1\tval2\tval3\tval4\tval5\t"，"val6\tval7\tval8\tval9\tval10\t"]；val1\tval2\tval3\tval4\tval5\t">	"；val6\tval7\tval8\tval9\tval10\t">