flaten json dynamic in snowflake

user_17260082 0
Viewed 48

I am using this

select 'select ' 
      || (select listagg(distinct 'json_col:'||key::text||'::string'||' as '||key::text, ', ')  from GAURAV.PUBLIC.jsondata, lateral flatten(input=>json_col, mode=>'OBJECT')) 
      || ' from GAURAV.PUBLIC.jsondata'

but it can't pick keys dynamic

it shows me output like this

enter image description here

I have Multiple json data in a table how to flatten it

snowflake-cloud-data-platform
1 Answers

If I have a table of json like:

 ('{"key_a":"a", "key_b":"b"}'),
 ('{"key_c":"a", "key_d":"b"}')

this SQL

select 
  'select ' || listagg(distinct 'json_col:'|| f.key::text || '::string as ' || f.key::text, ', ') || ' from GAURAV.PUBLIC.jsondata' as output
from jsondata as jd, 
table(flatten(input=>jd.json_col, mode=>'OBJECT')) f
group by f.seq

gives:

OUTPUT
select json_col:key_d::string as key_d, json_col:key_c::string as key_c from GAURAV.PUBLIC.jsondata
select json_col:key_a::string as key_a, json_col:key_b::string as key_b from GAURAV.PUBLIC.jsondata

The major points are to loop across the table as a first order selection. And to group the LISTAGG via the SEQ of the flatten which gives a distinct value per row of input, thus if you have 100 input rows, you will get 100 different seq, thus able to keep them separated.

It can be written with that output line split apart also:


select 
  'select ' || 
      listagg(distinct 'json_col:'|| f.key::text || '::string as ' || f.key::text, ', ') || 
      ' from GAURAV.PUBLIC.jsondata' as output
from jsondata as jd, 
table(flatten(input=>jd.json_col, mode=>'OBJECT')) f
group by f.seq
user_439920
Related