Snowflake : Json data flattern

Question

I have below sql to fetch the data from JSON file, but my file contains array of data with multiple values.

SELECT
  select 
  DISTINCT
    
    ,REPLACE(DOCUMENT:"_id"::VARCHAR(50),'guests-','') GUEST_ID 
    ,PARSE_JSON(DOCUMENT):"_rev"::string as GUEST_REVISION_ID 
    ,PARSE_JSON(DOCUMENT):personal_info:addresses:address_id::varchar(255) as ADDRESS_ID
    ,PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"address_type"::varchar(255) as ADDRESS_CODE
    ,UPPER(regexp_replace(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"address_line1"::VARCHAR(255),'[\n\r]','')) as ADDRESS_LINE_1
    ,UPPER(regexp_replace(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"address_line2"::VARCHAR(255),'[\n\r]','')) as ADDRESS_LINE_2
    ,UPPER(regexp_replace(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"city"::VARCHAR(255),'[\n\r]','')) as CITY_NAME
    ,UPPER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"state"::varchar(255)) as STATE_CODE  
    ,UPPER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"country"::varchar(255)) as COUNTRY       
    ,PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"postal_code"::varchar(255) as POSTAL_CODE  
    ,UPPER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"country_code"::varchar(255)) as COUNTRY_CODE 
    ,UPPER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"first_name"::varchar(255)) as ADDRESS_FIRST_NAME 
    ,UPPER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"last_name"::varchar(255)) as ADDRESS_LAST_NAME 
    ,PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"phone_number"::varchar(255) as PHONE_NUMBER    
    ,CASE
        WHEN LOWER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"primary") = 'true' THEN 1
        WHEN LOWER(PARSE_JSON(DOCUMENT):"personal_info":"addresses[]":"primary") = 'false' THEN 0
     ELSE NULL END as FLAG
from test

Sample Data :

{
 "_id":"guests-240c8ef1-65f0-11e9-8e7e-8568b9f986fb",
 "personal_info": {
   "addresses": [
      {
       "address_id":"555148381793213101",
       "address_line1":"509 BROADLEAF LANE",
       "address_type":"generic",
       "city":"MCKINNEY",
       "country":"United States",
       "country_code":"US",
       "postal_code":"75070",
       "primary": true,
       "state":"TX"
      },
      {
       "address_id":"856855604204997103",
       "address_line1":"11 Blossom Dr",
       "address_line2":"Basking Ridge",
       "address_type":"billing",
       "city":"Basking Ridge",
       "country":"United States",
       "country_code":"US",
       "email_address":"[email protected]",
       "first_name":"Deborah",
       "last_name":"Fischang",
       "phone_number":"9086723249",
       "postal_code":"07920",
       "primary": false,
       "state":"NJ"
      },
      {
       "address_id":"856855604204997103",
       "address_line1":"11 Blossom Dr",
       "address_line2":"Basking Ridge",
       "address_type":"generic",
       "city":"Basking Ridge",
       "country":"United States",
       "country_code":"US",
       "email_address":"[email protected]",
       "first_name":"Deborah",
       "last_name":"Fischang",
       "phone_number":"9086723249",
       "postal_code":"07920",
       "primary": false,
       "state":"NJ"
      }
    ]

How to get this data in mutiple rows basis on number of address in array. I tried lateral flattern but its not working........ How to get this data in mutiple rows basis on number of address in array. I tried lateral flattern but its not working........

Answer 1

You may use something like this:

SELECT
  DISTINCT
    REPLACE(DOCUMENT:"_id"::VARCHAR(50),'guests-','') GUEST_ID 
    ,VALUE:address_id::varchar(255) as ADDRESS_ID
    ,VALUE:"address_type"::varchar(255) as ADDRESS_CODE
    ,regexp_replace(VALUE:"address_line1"::VARCHAR(255),'[\n\r]','') as ADDRESS_LINE_1
    ,regexp_replace(VALUE:"address_line2"::VARCHAR(255),'[\n\r]','') as ADDRESS_LINE_2
    ,regexp_replace(VALUE:"city"::VARCHAR(255),'[\n\r]','') as CITY_NAME
from test, lateral flatten( input =>  PARSE_JSON(DOCUMENT):"personal_info":"addresses"  ) f;