jsonsnowflake-cloud-data-platformblob

Flatten nested JSON blob in snowflake


I'm trying to flatten the following JSON Blob in MyTable which is varchar:

MyTable

A B JSONBlob
x 1 {"a": "12345", "b": "city", "c": "state", "d": {"e":"567", "f":"llc"} }
y 2 {"a": "6789", "b": "country", "c": "county", "d": {"e":"999", "f":"col"} }

What I've tried so far that is returning no results:

select
f. value as a
, f1.value:f as f
from MyTable
, lateral flatten(input => PARSE_JSON(MyTable.JSONBlob)) f
, lateral flatten(input => f.value:d) f1

I'd like to query to get the results in the following format:

a f
12345 llc
6789 col

Solution

  • You don't need to flatten if all your data looks like example.

    The values in f is just nested in d in the example above.

    This SQL will give you the result you are looking for

    select jb:a::text a, jb:d:f::text f
      from (select PARSE_JSON(JSONBlob) jb
              from myTable)
    
    

    Tested with the following and the result is below

    with myTable(A,B,JSONBlob) as (
    select * from (values
    ('x',1,'{"a": "12345", "b": "city", "c": "state", "d": {"e":"567", "f":"llc"} }'),
    ('y',2,'{"a": "6789", "b": "country", "c": "county", "d": {"e":"999", "f":"col"} }'))
    )
    select jb:a::text a, jb:d:f::text f
      from (select PARSE_JSON(JSONBlob) jb
              from myTable)
    

    Result