Search code examples
jsonsnowflake-cloud-data-platformblob

Nested JSON blob in Snowflake

I'm trying to query on the following JSON Blobs in MyTable that is varchar, however, JSONBlob2 has square brackets over the curly brackets that seem to be interfering with my query:

MyTable

JSONBlob JSONBlob2
{"a": "12345", "b": {"c":"567", "d":"llc"} } {"e": [{"f":"321", "g":"432}] "h": [{"i":"couch", "j":"sofa"}] }
{"a": "6789", "b": {"c":"999", "d":"col"} } {"e": [{"f":"765", "g":"444}] "h": [{"i":"bed", "j":"blanket"}] }

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

select jb:e:f::text f, jb:h:i::text h
  from (select PARSE_JSON(JSONBlob2) jb
          from myTable)

hoping to get the results in this format:

f i
321 couch
765 bed
Solution

  • You can use FLATTEN to convert the JSON into rows :

    select 
       max(get( jb.value[0], 'f' ))::varchar f,
       max(get( jb.value[0], 'i' ))::varchar i
from myTable,
lateral flatten (  parse_json(JSONBlob2) )  jb
group by jb.seq

    Result :

    F I
    321 couch
    765 bed