ernestoongaro/json.md

## json.md

      
    Raw
  

              json.md
            
          
    First run this in snowflake:
(
 src variant
)
AS SELECT parse_json(column1) as src
FROM values
('{ 
 "topleveldate" : "2017-04-28", 
 "toplevelname" : "somename", 
 "extraFields": [ 
 { 
 "value": "somevalue1", 
 "key": "somekey1", 
 "type": "sometype1", 
 "booleanflag": false 
 }, 
 { 
 "value": "", 
 "key": "somekey2"
 }]}');

then create a model in dbt called flatten.sql
WITH a as
(
 select 
   src:topleveldate::string as topleveldate
 , src:toplevelname::string as toplevelname
 , value as val
 from vnt, lateral flatten( input => src:extraFields )
)
select topleveldate, toplevelname, key, value from a, lateral flatten( input => val )

then run the model dbt run --models flatten
finally, run select * from {{ ref('flatten') }}