I need to flatten JSON data in Snowflake Table1 table, in one JSON_DATA column that has an array

Question

Could somebody help me to create an SQL statement to flatten JSON data in Snowflake Table1 table, in one JSON_DATA column that has an array?

JSON_DATA:

{
    "scopes": [
        {
            "scope_name": "IN SCOPE",
            "company_code": "01",
            "lob_codes": ["01","07","09"]
        },
        {
            "scope_name": "IN SCOPE",
            "company_code": "02",
            "lob_codes": ["07","13","20"]
        },
        {
            "scope_name": "OUT OF SCOPE",
            "company_code": "01",
            "lob_codes": ["30","35","40"]
        },
        {
            "scope_name": "OUT OF SCOPE",
            "company_code": "02",
            "lob_codes": ["02","03","05"]
        }
        
    ]
}

I need to flatten it to:

|scope_name    |    company_code|   lob_codes|
|--------------|----------------|------------|
|IN SCOPE      |    1           |       01   |
|IN SCOPE      |    1           |       07   |
|IN SCOPE      |    1           |       09   |
|IN SCOPE      |    2           |       07   |
|IN SCOPE      |    2           |       13   |
|IN SCOPE      |    2           |       20   |
|OUT OF SCOPE  |    1           |       30   |
|OUT OF SCOPE  |    1           |       35   |
|OUT OF SCOPE  |    1           |       40   |
|OUT OF SCOPE  |    2           |       02   |
|OUT OF SCOPE  |    2           |       03   |
|OUT OF SCOPE  |    2           |       05   |

Mike Walton Mike Walton · Accepted Answer · 2021-02-18T00:59:51

I believe you are looking for something along these lines. Focus on the SELECT statement at the end. The top piece is just emulating the data you provided.

WITH x AS (
SELECT parse_json('{
    "scopes": [
        {
            "scope_name": "IN SCOPE",
            "company_code": "01",
            "lob_codes": ["01","07","09"]
        },
        {
            "scope_name": "IN SCOPE",
            "company_code": "02",
            "lob_codes": ["07","13","20"]
        },
        {
            "scope_name": "OUT OF SCOPE",
            "company_code": "01",
            "lob_codes": ["30","35","40"]
        },
        {
            "scope_name": "OUT OF SCOPE",
            "company_code": "02",
            "lob_codes": ["02","03","05"]
        }
        
    ]
}') as json_data
)
SELECT
    y.value:company_code::varchar as company_code,
    y.value:scope_name::varchar as scope_name,
    z.value::varchar as lob_codes
FROM x,
LATERAL FLATTEN (input=>x.json_data:scopes) y,
LATERAL FLATTEN (input=>y.value:lob_codes) z;

I need to flatten JSON data in Snowflake Table1 table, in one JSON_DATA column that has an array

1 Answers