Querying variant data in Snowflake

Question

Here is the data variant source table I am using in my example. I want to make a query to parse this data into a table in snowflake from a variant src.

{
    "col1": bool,
    "col2": null,
    "col3": "datetime",
    "col4": int,
    "col5": "string",
    "col6": "string",
    "array": [
        {
            "x": bool,
            "y": null,
            "v": "datetime",
            "z": int,
           "w": "string",
            "q": "string",
            "obj": {
                    "a": "bool",
                     "b": "float"
                   },
    "col7": "datetime"
}
]
}

-- Here what I tried

SELECT 

     src:col1::string as col1,
     src:col2::string as col2,
     src:col3::string as col3,
     src:col4::string as col4,
     src:col5::string as col5,
     src:col6::string as col6,

     s.value:x::string as S_x,
     s.value:y::string as s_y,
     s.value:v::string as s_v,
     s.value:z::string as s_z,
     s.value:w::string as s_w,
     s.value:q::string as s_q,

     s.value:obj.value:a::string as s_obj_a,
     s.value:obj.value:b::string as s_obj_b,

     src:col7::string as col7 
FROM tblvariant
    , table(flatten(src:s)) s
    ;

Everything is working except that these two columns (a, b) are null while they should contain their data. Any suggestion? Many thanks!

Answer 1

s.value:obj .value:a ::string as s_obj_a,

s.value:obj .value:b ::string as s_obj_b,

Accessing an object's keys can be done with the use of the dot (.) notation. You do not need to use the GET_PATH (:) operator to gain access to those fields:

s.value:metadata.a::string as s_m_a,
s.value:metadata.b::string as s_m_b,

You also do not need to run a second FLATTEN over the metadata object within your stages array, unless you truly need one exclusive row per metadata key, assuming metadata is an object type and not a nested array. If you just want to extract the values out into the same level as each array row, just using the above should suffice.