CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platform
anarche
anarche

Reputation: 536

Retrieving sub-fields from parsed JSON in snowflake

I'm having some difficulty getting the individual components of the address component

with data as (select PARSE_JSON('{ "data" : [ [ "row-ea6u~fkaa~32ry", "00000000-0000-0000-01B7-0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\"address\": \"4509 BELAIR ROAD\", \"city\": \"Baltimore\", \"state\": \"MD\", \"zip\": \"\"}", null, null, null, true ], null, null, null ] }') as j ) select f.value[1][0]::text from data d, lateral flatten(input=> d.j:data,recursive=>TRUE) f;

f.value[1][0] has a field address

{"address": "4509 BELAIR ROAD", "city": "Baltimore", "state": "MD", "zip": ""}

but f.value[1][0].address returns null

How do I get the individual attributes of f.value[1] like address, city, etc?

Upvotes: 0

Views: 156

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

The problem is given you have three levels of nested data, you should not be using recursive=>TRUE as the objects are not the same, so you cannot make anything of value out of the data. You need to break the different layers apart manually.

with data as (
  select 
  PARSE_JSON('{ data: [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\\"address\\": \\"4509 BELAIR ROAD\\", \\"city\\": \\"Baltimore\\", \\"state\\": \\"MD\\", \\"zip\\": \\"\\"}", null, null, null, true ], null, null, null ]]}') as j
), data_rows as (
    select f.value as r
    from data d,
    lateral flatten(input=> d.j:data) f
)
select dr.r[0] as v0
    ,dr.r[1] as v1
    ,dr.r[2] as v2
    ,dr.r[3] as v3
    ,f.value as addr_n
from data_rows dr,
    lateral flatten(input=> dr.r[13]) f;

so this get all the rows (of which your example has only one) the unpacks the values of interest (you will need to complete this part and give v0 - vN meaning) but there is an array or addresses

V0  V1  V2  V3  ADDR_N
"row-ea6u~fkaa~32ry"    "0B8F94EE5292"  0   1486063689  "{\"address\": \"4509 BELAIR ROAD\", \"city\": \"Baltimore\", \"state\": \"MD\", \"zip\": \"\"}"
"row-ea6u~fkaa~32ry"    "0B8F94EE5292"  0   1486063689  null
"row-ea6u~fkaa~32ry"    "0B8F94EE5292"  0   1486063689  null
"row-ea6u~fkaa~32ry"    "0B8F94EE5292"  0   1486063689  null
"row-ea6u~fkaa~32ry"    "0B8F94EE5292"  0   1486063689  true

now to decode the address as json ,parse_json(f.value) as addr_n does that, so you can break it apart like:

with data as (
  select 
  PARSE_JSON('{ data: [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\\"address\\": \\"4509 BELAIR ROAD\\", \\"city\\": \\"Baltimore\\", \\"state\\": \\"MD\\", \\"zip\\": \\"\\"}", null, null, null, true ], null, null, null ]]}') as j
), data_rows as (
    select f.value as r
    from data d,
    lateral flatten(input=> d.j:data) f
)
select dr.r[0] as v0
    ,dr.r[1] as v1
    ,dr.r[2] as v2
    ,dr.r[3] as v3
    ,parse_json(f.value) as addr_n
    ,addr_n:address::text as addr_address
    ,addr_n:city::text as addr_city
    ,addr_n:state::text as addr_state
    ,addr_n:zip::text as addr_zip  
from data_rows dr,
    lateral flatten(input=> dr.r[13]) f;

you can ether leave the addr_n dummy variable or swap it out by cut'n'pasting it like so:

    ,parse_json(f.value):address::text as addr_address
    ,parse_json(f.value):city::text as addr_city
    ,parse_json(f.value):state::text as addr_state
    ,parse_json(f.value):zip::text as addr_zip

Upvotes: 1

FKayani
FKayani

Reputation: 1021

You can follow the article for step-by-step for achieving it: https://community.snowflake.com/s/article/Using-lateral-flatten-to-extract-data-from-JSON-internal-field

Hope this helps!

Upvotes: 1

Related Questions