I am trying to access the data stored in a snowflake table using python sql. Below is the columns given below i want to access

Below is the data-sample and i want to access columns value,start. This data i dumped in one column(DN) of a table (stg)

{
    "ok": true, 
    "metrics": [
        {
            "name": "t_in", 
            "data": [{"value": 0, "group": {"start": "00:00"}}]
        }, 
        {
            "name": "t_out", 
            "data": [{"value": 0,"group": {"start": "00:00"}}]
        }
    ]
}

##consider many lines stored in same column in different rows.

Below query only fetched data for name. I want to access other columns value also. This query is a part of python script.

         select
            replace(DN : metrics[0].name , '"' , '')as  metrics_name, #able to get
            replace(DN : metrics[2].data , '"' , '')as  metrics_data_value,##suggestion needed          
            replace(DN : metrics.data.start, '"','') as metrics_start, ##suggestion needed
            replace(DN : metrics.data.group.finish, '"','') as metrics_finish, ##suggestion needed

    from stg

Do i need to iterate over data and group? If yes, please suggest the code.

Answer

Here is an example of how to query that data.

Set up sample data:

create or replace transient table test_db.public.stg (DN variant);
insert overwrite into test_db.public.stg (DN) 
    select parse_json('{
    "ok": true, 
    "metrics": [
        {
            "name": "t_in", 
            "data": [
                {"value": 0, "group": {"start": "00:00"}}
            ]
        }, 
        {
            "name": "t_out", 
            "data": [
                {"value": 0,"group": {"start": "00:00"}}
            ]
        }
    ]
    }');

Select statement example:

select 
    DN:metrics[0].name::STRING,
    DN:metrics[1].data,
    DN:metrics[1].data[0].group.start::TIME,
    DN:metrics[1].data[0].group.finish::TIME
from test_db.public.stg;

Instead of querying individual indexes of the JSON arrays, I think you’ll want to use the flatten function which is documented here.

Here is how you do it with the flatten which is what I am guessing you want:

select 
    mtr.value:name::string,
    dta.value,
    dta.value:group.start::string,
    dta.value:group.finish::string
from test_db.public.stg stg,
   lateral flatten(input => stg.DN:metrics) mtr,
   lateral flatten(input => mtr.value:data) dta