雪花sql，嵌套在变体的第一级的阵列的平坦路径参数

Question

根据以下内容，有一个由存储对象（又称字典，键值对）组成的变体字段“事件”，如下所示：

[
{ "field_name": "status",  "id": 987418431597, "previous_value": "new", "type": "Change", "value": "pending"},
{ "field_name": "360020024138", "id": 987418431617, "previous_value": null, "type": "Change", "value": "#55927" },
{ "field_name": "360016698218", "id": 987418431637, "previous_value": null, "type": "Change", "value": "0681102386"},
{ "field_name": "360016774537", "id": 987418431657, "previous_value": null, "type": "Change", "value": "89367031562011632212"}
]

此字段属于事件日志，我试图使用“事件“作为获得相关时间戳和ID的过滤器。

通过 flatten上的雪花文档我发现<我发现<代码>递归=＆gt; true 参数使我能够将变体一直延伸到其嵌套对象，但是希望通过优化代码，我想使用path参数，以选择性地展开“事件” 但是，对于我感兴趣的对象。

但是，由于某种原因，Flatten不允许我通过数字路径来识别我要扩展的对象的数组索引，例如：

select b.* 
from "event_log" a
    ,lateral flatten (input => a."events", path => 0) b limit 100;

返回：无效类型，0）]对于参数'路径'

和

select b.* 
from "event_log" a
   , lateral flatten (input => a."events", path => [0]) b limit 100;

返回：语法错误：意外'['。 （第162行）

具有讽刺意味的是，使用recursive =＆gt; true，B.Path字段表示[i]之类的索引。

Sflake Docs中的示例是使用存储数组的对象使用路径参数的一个示例变体。

Answer 1

阵列索引应作为Input：

select b.* 
from "event_log" a,
lateral flatten (input => a."events"[1]) b
limit 100;

示例

CREATE OR REPLACE TABLE "event_log"("events" VARIANT)
AS
SELECT '[   {     "field_name": "status",     "id": 987418431597,     "previous_value": "new",     "type": "Change",     "value": "pending"   },   {     "field_name": "360020024138",     "id": 987418431617,     "previous_value": null,     "type": "Change",     "value": "#55927"   },   {     "field_name": "360016698218",     "id": 987418431637,     "previous_value": null,     "type": "Change",     "value": "0681102386"   },   {     "field_name": "360016774537",     "id": 987418431657,     "previous_value": null,     "type": "Change",     "value": "89367031562011632212"   } ]';

：

Answer 2

因此，每行都有一个数组。该数组具有许多具有相同“结构”的对象。获得ID是有道理的，因为每个对象都有一个。

因此，您可以访问它。

select a."events"[0]:id::number as id
from "event_log" as a
limit 100;

给出：

A。“事件” [0]：ID
987418431597

，但鉴于每个对象的ID看起来与众不同，您知道自己要纠正ID的情况似乎更有意义地使用Flatter来展开数组。并访问对象元素。

2行”中

with "event_log"("events") as (
    select parse_json(column1) from values
    ('[
        { "field_name": "status",  "id": 0987418431597, "previous_value": "new", "type": "Change", "value": "pending"},
        { "field_name": "360020024138", "id": 0987418431617, "previous_value": null, "type": "Change", "value": "#55927" },
        { "field_name": "360016698218", "id": 0987418431637, "previous_value": null, "type": "Change", "value": "0681102386"},
        { "field_name": "360016774537", "id":0987418431657, "previous_value": null, "type": "Change", "value": "89367031562011632212"}
        ]'),
    ('[
        { "field_name": "status",  "id": 1987418431597, "previous_value": "new", "type": "Change", "value": "pending"},
        { "field_name": "360020024138", "id":1987418431617, "previous_value": null, "type": "Change", "value": "#55927" },
        { "field_name": "360016698218", "id": 1987418431637, "previous_value": null, "type": "Change", "value": "0681102386"},
        { "field_name": "360016774537", "id":1987418431657, "previous_value": null, "type": "Change", "value": "89367031562011632212"}
        ]')
)
select b.seq as input_row
    ,b.index as array_index
    ,b.value:field_name::text as field_name
    ,b.value:id::number as id
    ,b.value:previous_value::text as previous_value
    ,b.value:type::text as type
    ,b.value:value::text as value
from "event_log" as a
    ,lateral flatten(input=>a."events") b
;

：

input_row	array_index	field_name	id	revory_value	类型	2
1	0	状态	987418431597	新	更改	未决
1	1	360020024138	987418418431617		0	状态
：	更改	得到	0016774537		此	我们
，	“	，	987418431657		89367031562011632212	因此
获取	在	我们	1987418431597	新	变化	悬而未决
2	1	360020024138	1987418431617		变更	55927
2	2	360016698218	​		的	＃
​	​	​	​		​	​