Flatten nested JSON blob in snowflake

Question

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

Answer 1

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)