How do I parse an unnamed nested json array in the Snowflake database?

Question

I've found the flatten function but the example is simpler than my real example. It can be created with the following code:

create or replace table test2 as
select '5dd17ef333de385a360e0ef1' as id,
parse_json('{
    "_id" : "5dd17ef333de385a360e0ef1",
    "uid" : "5dd175d3333b85961df27c51",
    "task_info" : {
        "reps=1" : [ 
            [ 
                {
                    "cached" : false,
                    "transform max RAM" : 51445000,
                }
            ], 
            [ 
                {
                    "cached" : false,
                    "transform max RAM" : 51445000,
                }
            ], 
            [ 
                {
                    "cached" : true,
                    "transform max RAM" : 51445000,
                }
            ]
        ]
    }
}')::variant as json_rec
;

Then my attempt to parse it:

select id
, json_rec:_id::string(100) as extracted_id
, value:cached::string as cached
, value
, json_rec
  from
    test2
  , lateral flatten( input => json_rec:task_info )
;

The cached is clearly not going deep enough, although I am unclear of the syntax that is required to fish something like these values out. Thoughts?

what DBMS are you using?

Z4-tier
– Z4-tier

2019-12-30 04:22:59 +00:00
Commented Dec 30, 2019 at 4:22 — Z4-tier
– Z4-tier, Commented Dec 30, 2019 at 4:22
Snowflake is the database

doyouevendata
– doyouevendata

2019-12-30 04:24:57 +00:00
Commented Dec 30, 2019 at 4:24 — doyouevendata
– doyouevendata, Commented Dec 30, 2019 at 4:24

d.hoeffer · Accepted Answer · 2019-12-30 07:18:10Z

3

If what you want is a separate row for each of the items in the innermost array (i.e. 3 rows for the above example), then you can use recursive=>true and filter on key='cached', like this:

select id
, json_rec:_id::string(100) as extracted_id
, value as cached
, json_rec
  from
    test2
  , lateral flatten( input => json_rec:task_info, recursive=>true)
where key='cached';

answered Dec 30, 2019 at 7:18

d.hoeffer

6325 silver badges14 bronze badges

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

4 Comments

doyouevendata Over a year ago

my original comment contained an error on my part, solution works, thanks!

doyouevendata Over a year ago

follow up -

select id , json_rec:_id::string(100) as extracted_id , case when key = 'cached' then value else null end as cached , case when key = 'transform max RAM' then value else null end as transform_max_ram , seq, key, path, index, value, this   from     test2   , lateral flatten( input => json_rec:task_info, recursive=>true)   where key in ('cached', 'transform max RAM')   ;

how do i indicate the groups of these related values? the only info i have appears to be the first part of the path - but that doesn't seem very elegant...

d.hoeffer Over a year ago

How do you want the output to look like? 3 rows with a column "cached" and one "transform_max_ram"?

doyouevendata Over a year ago

ultimately i turned this into 3 rows for each record and 2 for each field, so 6 total. i then pivot these to columns based on the reps=1... and did a distinct as my data doesn't appear to have a difference for these values.

Collectives™ on Stack Overflow

How do I parse an unnamed nested json array in the Snowflake database?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related