CODEWITHSUNDEEP
sqljsonsnowflake-cloud-data-platform
JustABeginner
JustABeginner

Reputation: 15

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

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?

Upvotes: 1

Views: 225

Answers (1)

Maja F.
Maja F.

Reputation: 333

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

Upvotes: 3

Related Questions