SNOWFLAKE SQL, FLATTEN path parameter for Array Nested in first level of Variant

Question

There is a Variant field "events" that is made up of an Array storing Objects(aka dictionaries, key-value pairs), as per the below:

[
{ "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"}
]

This field belongs to an event log, and I am trying to use the content of "events" as a filter to get the related timestamps and ids.

Through the Snowflake documentation on Flatten I found out that the recursive => True parameter allows me to expand the Variant all the way down to its nested objects, but with hopes of optimising code, I wanted to use the path parameter, to selectively expand "events" only for the Objects I was interested in.

However, for some reason, Flatten does not allow me to pass a numeric path to identify the Array index of Object that I want to expand, as:

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

returns: invalid type [NUMBER(1,0)] for parameter 'path'

and

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

returns: Syntax error: unexpected '['. (line 162)

Ironically, when using recursive => True, the b.path field represent indexes like this [i].

The example in the SFlake docs makes an example of the use of the path parameter with an Object that stores Arrays, whereas here "events" is made up of an Array of Objects, so I actually do not have any working example for this type of Variants.

Lukasz Szozda · Accepted Answer · 2022-04-18 08:20:47Z

1

The array index should be provided as input:

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

Sample:

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"   } ]';

Output:

answered Apr 18, 2022 at 8:20

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simeon Pilgrim · Accepted Answer · 2022-04-18 09:31:19Z

So you have an array in each row. The array has many objects with the same "structure". Getting the ID makes sense as there is one in every object.

So you could just access it.

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

gives:

A."EVENTS"[0]:ID
987418431597

But given each object's ID looks to be different how do you know you are getting to correct ID's it would seem to make more sense to use flatten to unroll the array. and access the object elements.

so in this "2 rows" of 4 fields:

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
;

we get:

INPUT_ROW	ARRAY_INDEX	FIELD_NAME	ID	PREVIOUS_VALUE	TYPE	VALUE
1	0	status	987418431597	new	Change	pending
1	1	360020024138	987418431617		Change	#55927
1	2	360016698218	987418431637		Change	0681102386
1	3	360016774537	987418431657		Change	89367031562011632212
2	0	status	1987418431597	new	Change	pending
2	1	360020024138	1987418431617		Change	#55927
2	2	360016698218	1987418431637		Change	0681102386
2	3	360016774537	1987418431657		Change	89367031562011632212

Collectives™ on Stack Overflow

SNOWFLAKE SQL, FLATTEN path parameter for Array Nested in first level of Variant

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related