Snowflake - OBJECT data type

user_18736647 0
Viewed 55

Snowflake OBJECT data type provides support for storing semi structure data (Primarily key value pairs), for example, if below is my dataset (with Parameter being of type OBJECT),

DeviceID, Parameters
D1, { "P1": "100", "P2": "150" }
D2, { "P2": "125", "P3": "200" }

it can flattened out by using, SELECT DeviceID, Parameters['P1'], Parameters['P2'] and the output would be,

DeviceID P1 P2
D1 100 150
D2 125 200

However if I want to have the individual elements as rows, what is the best method to do this, like if I need the output as below,

DeviceID ParmeterID ParameterName
D1 P1 100
D1 P2 150
D2 P1 125
D2 P2 200
snowflake-cloud-data-platform
1 Answers

Using a CTE for data:

WITH data(DeviceID, Parameters) as (
    SELECT column1, parse_json(column2) from values
        ('D1', '{ "P1": "100", "P2": "150" }'),
        ('D2', '{ "P2": "125", "P3": "200" }')
)

you want to use the FLATTEN function, wrapped in a TABLE or LATERAL:

SELECT 
    d.DeviceID,
    f.key::text as p1,
    f.value::number as P2
FROM data as d,
    table(flatten(input=>d.Parameters)) f

gives:

DEVICEID P1 P2
D1 P1 100
D1 P2 150
D2 P2 125
D2 P3 200
user_439920
Related