Snowflake中JSON键值对转分栏格式的实现方法求助

核心实现代码 #

根据你的JSON数据格式，使用Snowflake的FLATTEN函数可以快速将扁平JSON拆分成目标分栏结构，以下是具体实现：

处理JSON字面量

如果直接使用给定的JSON字符串，执行以下SQL：

SELECT
    f.key AS "user",
    f.value::INT AS "number"
FROM
    LATERAL FLATTEN(
        INPUT => PARSE_JSON('{"53CefwEQlSZyZmk4n9s24qGV3m":40,"rSwfCfEH01TrDCs9E0C3CL1fhi":107,"system":2}'),
        MODE => 'KEYS'
    ) f
ORDER BY
    CASE WHEN f.key = 'system' THEN 1 ELSE 0 END,
    f.value;

从表中读取JSON列

如果JSON数据存储在表的列中（假设表名为user_stats，JSON列名为user_count_json），执行以下SQL：

SELECT
    f.key AS "user",
    f.value::INT AS "number"
FROM
    user_stats,
    LATERAL FLATTEN(INPUT => user_count_json, MODE => 'KEYS') f
ORDER BY
    CASE WHEN f.key = 'system' THEN 1 ELSE 0 END,
    f.value;

代码说明 #

• PARSE_JSON()：将字符串格式的JSON转换为Snowflake可识别的JSON数据类型。
• FLATTEN(MODE => 'KEYS')：将JSON对象的键值对拆分为多行记录，返回每个键（key）和对应的值（value）。
• f.value::INT：将JSON类型的数值转换为整数，匹配目标格式的数字类型。
• ORDER BY子句：用于调整输出顺序，使其与你给出的示例一致（将system排在第二位，其余按数值升序排列），如果不需要特定顺序可直接移除。

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