Snowflake - OBJECT data type

Question

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

Answer 1

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