CODEWITHSUNDEEP
snowflake-cloud-data-platform
ays1
ays1

Reputation: 69

dynamically flatten json using snowflake function

Is it possible to dynamically flatten json using snowflake function ?

select key,value from table a ,lateral flatten(input => variant_column)

gives

key | value empname| e1 empid | 123 empname| e2 empid | 345

which need to be converted as

enter image description here

Upvotes: 0

Views: 258

Answers (1)

James Goodhand
James Goodhand

Reputation: 88

I've assumed your source JSON is something like this:

[
    {
        "empname": "e1",
        "empid": 123
    },
    {
        "empname": "e2",
        "empid": 456
    }
]

Based on this, you can achieve the output you want using:

select
    s.value:empname::varchar as empname,
    s.value:empid::number as empid
from
    json j,
    lateral flatten (input => j.src, path => '', mode => 'ARRAY') s
;

Full example replication code:

create or replace table json (src variant);

insert into json(src) select parse_json($$
[
    {
        "empname": "e1",
        "empid": 123
    },
    {
        "empname": "e2",
        "empid": 456
    }
]
$$
);

select * from json;

select
    s.value:empname::varchar as empname,
    s.value:empid::number as empid
from
    json j,
    lateral flatten (input => j.src, path => '', mode => 'ARRAY') s
;

Upvotes: 0

Related Questions