snowflake-cloud-data-platform - 雪花从具有嵌套字典列表的列中获取每个键/值

Question

我有一个名为monthly_stats（VARCHAR 类型）的列，其中包含以下字典列表：

    [{"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"}]

我试过这个查询：

 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);

但这只会选择字典列表中的第一个 id。如何获取每个字典中的每个 id？

标签： snowflake-cloud-data-platform

Answer 1

你可以这样尝试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;