CODEWITHSUNDEEP
snowflake-cloud-data-platform
doyouevendata
doyouevendata

Reputation: 179

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

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?

Upvotes: 1

Views: 946

Answers (1)

d.hoeffer
d.hoeffer

Reputation: 602

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';

Upvotes: 3

Related Questions