Snowflake json flatten from varchar data type

0
votes

Need to flatten json data, from varchar/string data type column in snowflake

create table testjson1 (name varchar(200)) 


select name:batchNumber from testjson1, lateral flatten( input => name ) 

insert into testjson1 select parse_json('{"batchNumber" : "B12345", "productCode" : "P1234"}')

insert into testjson1 select  ('2020-09-13T14:00:00.0000000')

select name:batchNumber from testjson1
1
snowflake-cloud-data-platform

1 Answers

0
votes

Key point here is to cast/parse varchar column, here by using TRY_PARSE_JSON:

Option 1:

select t.*, f.PATH, f.VALUE 
from testjson1 t, lateral flatten(input => TRY_PARSE_JSON(name), OUTER=>true) f;
/*
NAME                                            PATH        VALUE
{"batchNumber":"G35H9","productCode":"AHF7124"} batchNumber "G35H9"
{"batchNumber":"G35H9","productCode":"AHF7124"} productCode "AHF7124"
2020-09-13T14:00:00.0000000                     NULL        NULL    
*/

Option 2:

select *, TRY_PARSE_JSON(name):batchNumber::string AS result from testjson1
/*
NAME                                            RESULT
{"batchNumber":"G35H9","productCode":"AHF7124"} G35H9
2020-09-13T14:00:00.0000000                     NULL
*/