Snowflake中带条件的Lateral Flatten实现方法问询

场景与示例表 #

现有Snowflake表存储Variant类型的JSON数据，JSON结构中的键是否存在由API响应决定。以下是创建示例临时表的SQL：

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]}]}');

需求 #

需要实现条件Lateral Flatten：当JSON对象中存在key1时，展开key1对应的数组；若key1不存在，则展开始终存在的key2对应的数组。

当前尝试的SQL #

用户当前的实现尝试如下：

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
;

问题分析 #

上述SQL使用交叉连接（隐式内连接）的方式展开key1和key2，会导致以下问题：

• 当f1.value中不存在key1时，f2的展开结果为空，整行数据会被过滤掉（内连接特性），导致仅包含key2的记录无法被查询到。
• 当key1和key2同时存在时，会产生笛卡尔积，导致数据重复。

解决方案 #

我们可以通过动态指定Flatten的输入源来实现需求，以下是两种可行方案：

方案一：CASE分支动态选择输入 #

先判断当前JSON对象是否包含key1，再决定要展开的数组：

SELECT
    CASE WHEN f1.value:key1 IS NOT NULL THEN 'key1' ELSE 'key2' END AS value_type,
    f.value::int AS desired_value
FROM
    test,
    LATERAL FLATTEN(input => variant_col:response) AS f1,
    LATERAL FLATTEN(
        input => CASE 
                    WHEN f1.value:key1 IS NOT NULL THEN f1.value:key1 
                    ELSE f1.value:key2 
                END
    ) AS f;

方案二：COALESCE简化判断逻辑 #

利用COALESCE优先取key1，不存在则取key2，再进行展开：

SELECT
    iff(f1.value:key1 IS NOT NULL, 'key1', 'key2') AS value_type,
    f.value::int AS desired_value
FROM
    test,
    LATERAL FLATTEN(input => variant_col:response) AS f1,
    LATERAL FLATTEN(input => COALESCE(f1.value:key1, f1.value:key2)) AS f;

两种方案都能正确处理两种场景：

1. 当key1存在时，展开key1的数组，标记value_type为key1；
2. 当key1不存在时，展开key2的数组，标记value_type为key2；
   同时避免了数据丢失和重复的问题。

内容的提问来源于stack exchange，提问作者Coldchain9