我有一个 snowflake 表如下
表名:raw_data
| id|卷积型|抄本|
| - -----|- -----|- -----|
| 1|闲聊|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11:11,2019 - 01:11:11:11,2019 - 01:11:11,2019 - 01:11:11,2019 - 01:19,2019 - 01:19,2019 - 01:19,2019|
| 2|电话|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 - 01 11:11,2019 - 01 - 01 - 01 - 01 11:11:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01:1|
需要像下面这样扁平化数据
| id|卷积型|抄本|由|正文|时间|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 1|闲聊|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11:11,2019 - 01:11:11:11,2019 - 01:11:11,2019 - 01:11:11,2019 - 01:19,2019 - 01:19,2019 - 01:19,2019|系统|我能帮你吗?|2023 - 01 - 01 11:11:11|
| 1|闲聊|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01 11:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11,2019 - 01 - 01:11:11:11,2019 - 01:11:11:11,2019 - 01:11:11,2019 - 01:11:11,2019 - 01:19,2019 - 01:19,2019 - 01:19,2019|个人1|是的告诉我|2023 - 01 - 01 11:12:11|
| 2|电话|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 - 01 11:11,2019 - 01 - 01 - 01 - 01 11:11:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01:1|系统|我能帮你吗?|2023 - 01 - 01 11:13:11|
| 2|电话|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 - 01 11:11,2019 - 01 - 01 - 01 - 01 11:11:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01:1|个人2|我想知道现在的时间|2023 - 01 - 01 12:11:18|
| 2|电话|[{“by”:“sys”,“text”:“我能帮你吗?2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:13:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 11:11,2019 - 01 - 01 - 01 11:11,2019 - 01 - 01 - 01 - 01 11:11:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01 - 01:11,2019 - 01:1|系统|时间是|2023 - 01 - 01 13:11:11|
我尝试使用下面的代码。
原始数据
select
*,
a.value:by::varchar as by,
a.value:text::varchar as text,
a.value:time::varchar as time
from raw_data
, lateral flatten(input => raw_data:transcript) a;
by,text,time值显示为null,有帮助吗?
2条答案
按热度按时间qqrboqgw1#
您可以使用
parse_json()
来执行此操作,如下所示:xzv2uavs2#
问题是JSON数组
[]
中有值。