How to convert a JSON field to Tabular format in SQL Query?

user_15307426 0
Viewed 59

I have a Table containing 3 columns (ID, Content, Date), where the Content column have values in json format as shown below:

{
    "Id": "9999",
    "Name": "PETERPAN",
    "SubContent": [
        {
            "subcontent1": "ABC",
            "subcontent2": "123"
        }
     [
}

How can I convert it into tabular format using SQL Query?

snowflake-cloud-data-platform sql json
1 Answers

Use LATERAL FLATTEN to get the key/value pairs as separate rows:

with t as (
select parse_json('{
                "Id": "9999",
                "Name": "PETERPAN",
                "SubContent": 
                    {
                        "subcontent1": "ABC",
                        "subcontent2": "123"
                    }
                 
            }') col
)
select col:Id as id, col:Name as name, sc.key, sc.value
from t, lateral flatten( input => col:SubContent ) sc;

The result is

ID      NAME        KEY         VALUE
9999    PETERPAN    subcontent1 ABC
9999    PETERPAN    subcontent2 123
user_165222240
Related