Snowflake Get every key/value from a column that has a list of nested dictionaries

user_14444816 0
Viewed 1473

I have a column named monthly_stats (VARCHAR type) with the below list of dictionaries:

    [{"id": 123, "quantity_booked": "12", "budget_booked": "112", "budget_booked_loc": "36.428571"}, 
        {"id": 1288119, "quantity_booked": "1703.3", "budget_booked": "58.694684",
     "budget_booked_loc": "43.434066"}, {"id": 233, "quantity_booked": "1648.35",
 "budget_booked": "56.801307", "budget_booked_loc": "42.032967"}]

I've tried this query:

 with org_table as (             
    select
    parse_json(monthly_stats) as m
    from my_table
) 
    select
    m[0]:id::integer as monthly_budgets_id,
    from org_table);

but this only selects the first id in the list of dictionaries. how do I get every id in the each dictionary?

snowflake-cloud-data-platform
3 Answers

You could try it like this using lateral flatten:

set teststring = '[{"id": 123, "quantity_booked": "12", "budget_booked": "112", "budget_booked_loc": "36.428571"},
                    {"id": 1288119, "quantity_booked": "1703.3", "budget_booked": "58.694684", "budget_booked_loc": "43.434066"}]';


 with org_table as (
    select
    parse_json($teststring) as m
)
    select
    flattened.value:id as id
    from org_table,
    lateral flatten( input => m ) as flattened;
user_128068770
user_123967700

You can get iterate over arrays with flatten():

with my_table as (
    select
  '[{"id": 123, "quantity_booked": "12", "budget_booked": "112", "budget_booked_loc": "36.428571"}, 
        {"id": 1288119, "quantity_booked": "1703.3", "budget_booked": "58.694684",
     "budget_booked_loc": "43.434066"}, {"id": 233, "quantity_booked": "1648.35",
 "budget_booked": "56.801307", "budget_booked_loc": "42.032967"}]' monthly_stats
    )
 , org_table as (             
    select parse_json(monthly_stats) as m
    from my_table
) 

select x.value:id, x.value:budget_booked
from org_table, table(flatten(m)) x;
user_1324380
Related