Question 294802 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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
Tags:

2

Answers


  1. 0

    Your JSON in jsonblob2 is not valid, assuming the invalid comes from you type "fake data" to protect your real data (thus I hand fixed it), we can then access it via a array index:

    with data(JSONBlob2) as (
    select * from values
    ( '{"e": [{"f":"321", "g":"432"}], "h": [{"i":"couch", "j":"sofa"}] }'),
    ( '{"e": [{"f":"765", "g":"444"}], "h": [{"i":"bed", "j":"blanket"}] }')
)
select
    try_parse_json(JSONBlob2):e[0]:f::text as f
    ,try_parse_json(JSONBlob2):h[0].i::text as i
from data

    I was lazy and didn’t use the nested try_parse_json like demircioglu did.

    enter image description here

    Where-as if your data really looks like "how you show" that is not JSON, and ether the source should output it correctly, or you have a horrible string processing task ahead of you.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search