json Snowflake中的条件横向平坦化

2ic8powd 于 2023-03-09 发布在其他

关注(0)|答案(1)|浏览(112)

我有一个包含json variant s的表，其中包含的键可能存在，也可能不存在，这取决于API响应。
示例表：

create or replace temporary table test (variant_col variant);

insert into test(variant_col)
select parse_json('{"response": [{"key1":[1,2,3]}, {"key2":[7,8,9]}]}');
insert into test(variant_col)
select parse_json('{"response": [{"key2":[7,8,9]}]}');

我想根据它们的关键点横向展平它们，如果存在，使用key1，否则使用key2（将始终存在）

SELECT
    iff(f2.value::int is null, 'key2', 'key1') as value_type,
    ifnull(f2.value::int, f3.value::int) as desired_value
FROM
    test,
    LATERAL FLATTEN(input => response) as f1,
    LATERAL FLATTEN(input => f1.value:key1) as f2,
    LATERAL FLATTEN(input => f1.value:key2) as f3
;

JSON

来源：https://stackoverflow.com/questions/75651672/conditional-lateral-flatten-in-snowflake

1条答案

按热度按时间

qcuzuvrc1#

我们可以通过给每一行一个id来解决这个问题，然后我们可以union all 2个查询的结果-取决于key1的存在：

with data as (
    select row_number() over(order by 0) id, * 
    from test
), rows_with_key1 as (
    select data.*, value v
    from data
        , lateral flatten(input => variant_col:response) as f1
    where f1.value:key1 is not null
), rows_without_key1 as (
    select data.*, value v
    from data
        , lateral flatten(input => variant_col:response) as f1
    where id not in (select id from rows_with_key1)
)

select id, value, variant_col
from rows_with_key1
    , lateral flatten(input => v:key1) ff
union all
select id, value, variant_col
from rows_without_key1
    , lateral flatten(input => v:key2)
;

赞(0）回复(0）举报 2023-03-09

我来回答

json Snowflake中的条件横向平坦化

1条答案

相关问题

热门标签

最新问答