Snowflake中提取数组内顺序可变对象字段值的实现方案咨询

嗨，我刚好碰到过几乎一模一样的场景，给你两个靠谱的解决方案，完全适配你说的数组对象顺序可变的情况，而且不会把单行拆成多行：

方案一：合并数组对象为单个大对象后提取（推荐） #

这个方法的核心是把数组里的所有零散对象合并成一个包含所有键值对的大对象，之后就可以像普通JSON对象一样用点符号直接取值，顺序完全不影响结果。

具体SQL代码如下：

WITH your_table AS (
    SELECT PARSE_JSON('[
        {"status": "pending"},
        {"date": "01-02-2000"},
        {"category": "Third Party Payment Providers"},
        {"creditDebit": "debit"}
    ]') AS tag
)
SELECT
    merged_tag:status::VARCHAR AS status,
    merged_tag:date::VARCHAR AS date,
    merged_tag:category::VARCHAR AS category,
    merged_tag:creditDebit::VARCHAR AS creditDebit
FROM your_table,
LATERAL (
    SELECT OBJECT_CONCAT_AGG(value) AS merged_tag
    FROM TABLE(FLATTEN(input=>tag))
)

原理说明 #

1. 先用LATERAL FLATTEN把数组拆成单个的小对象（这一步虽然会产生4行，但后续的聚合会把它们合并回去）
2. 用OBJECT_CONCAT_AGG聚合函数把所有小对象合并成一个大对象，比如合并后会得到{"status": "pending", "date": "01-02-2000", ...}这样的结构
3. 最后直接从合并后的大对象里提取每个字段，完全不用关心原数组里的对象顺序

方案二：子查询精准匹配字段提取 #

如果不想用聚合合并的方式，也可以针对每个字段单独写子查询，通过匹配对象的键名来定位取值，同样不会产生多行：

WITH your_table AS (
    SELECT PARSE_JSON('[
        {"status": "pending"},
        {"date": "01-02-2000"},
        {"category": "Third Party Payment Providers"},
        {"creditDebit": "debit"}
    ]') AS tag
)
SELECT
    (SELECT f.value:status::VARCHAR FROM TABLE(FLATTEN(input=>tag)) f WHERE OBJECT_KEYS(f.value)[0] = 'status') AS status,
    (SELECT f.value:date::VARCHAR FROM TABLE(FLATTEN(input=>tag)) f WHERE OBJECT_KEYS(f.value)[0] = 'date') AS date,
    (SELECT f.value:category::VARCHAR FROM TABLE(FLATTEN(input=>tag)) f WHERE OBJECT_KEYS(f.value)[0] = 'category') AS category,
    (SELECT f.value:creditDebit::VARCHAR FROM TABLE(FLATTEN(input=>tag)) f WHERE OBJECT_KEYS(f.value)[0] = 'creditDebit') AS creditDebit
FROM your_table

原理说明 #

每个字段对应的子查询都会：

1. 临时拆分数组为单个对象
2. 用OBJECT_KEYS(f.value)[0]取每个对象的唯一键（因为你的每个对象只有一个键值对）
3. 匹配到目标字段名后，提取对应的值

我个人更偏爱第一种方案，尤其是当字段数量较多时，代码会简洁很多，而且Snowflake对OBJECT_CONCAT_AGG的处理效率也很高，不会有性能瓶颈。

备注：内容来源于stack exchange，提问作者gjoe