如何在Snowflake中从数组返回指定对象与键值对

现有Snowflake表结构 #

示例数据 #

with sample_data as (
 select 1 as order_id, 'aaa' as cust_id, 
    '{
      "channel": "phone",
      "result": "approved", 
      "reason": null,
      "pairs": [
        {
          "key": "size",
          "value": "large"
        },
        {
          "key": "color",
          "value": "red"
        },
        {
          "key": "fit",
          "value": "slim"
        },
        {
          "key": "pattern",
          "value": "zebra"
        }
      ]
    }' as details union all
 select 2 as order_id, 'bbb' as cust_id, 
    '{
      "channel": "store",
      "result": "denied", 
      "reason": "stock",
      "pairs": [
        {
          "key": "size",
          "value": null
        },
        {
          "key": "pattern",
          "value": "tiger"
        }
      ]
    }' as details
)
select *
from sample_data

需求说明 #

从details字段中仅提取以下内容：

• channel、result字段
• pairs数组中key为size或color的键值对

单条记录期望输出 #

• ORDER_ID 1的details字段：

{
  "channel": "phone",
  "result": "approved", 
  "pairs": [
    {
      "key": "size",
      "value": "large"
    },
    {
      "key": "color",
      "value": "red"
    }
  ]
}

• ORDER_ID 2的details字段：

{
  "channel": "store",
  "result": "denied", 
  "pairs": [
    {
      "key": "size",
      "value": null
    }
  ]
}

全量表期望输出 #

当前遇到的问题 #

已通过lateral_flatten()在CTE中过滤出所需的pairs，但存在两个卡点：

1. 如何将筛选后的单个pairs元素重新组合为数组；
2. 如何将channel、result字段与修改后的pairs数组合并成目标JSON对象。
   使用array_construct()时会返回每行一个对象，无法按order_id聚合重组。

通过扁平化筛选→分组聚合重组数组→构建目标JSON的步骤实现，具体SQL如下：

with sample_data as (
 select 1 as order_id, 'aaa' as cust_id, 
    '{
      "channel": "phone",
      "result": "approved", 
      "reason": null,
      "pairs": [
        {
          "key": "size",
          "value": "large"
        },
        {
          "key": "color",
          "value": "red"
        },
        {
          "key": "fit",
          "value": "slim"
        },
        {
          "key": "pattern",
          "value": "zebra"
        }
      ]
    }'::variant as details union all
 select 2 as order_id, 'bbb' as cust_id, 
    '{
      "channel": "store",
      "result": "denied", 
      "reason": "stock",
      "pairs": [
        {
          "key": "size",
          "value": null
        },
        {
          "key": "pattern",
          "value": "tiger"
        }
      ]
    }'::variant as details
),
-- 1. 扁平化pairs并筛选目标key，同时提取channel和result
flattened_pairs as (
 select 
   order_id,
   cust_id,
   details:channel as channel,
   details:result as result,
   value as filtered_pair
 from sample_data,
 lateral flatten(input => details:pairs)
 where value:key in ('size', 'color')
),
-- 2. 按order_id分组，将筛选后的pair重新聚合为数组
aggregated_pairs as (
 select
   order_id,
   cust_id,
   channel,
   result,
   array_agg(filtered_pair) as filtered_pairs_array
 from flattened_pairs
 group by order_id, cust_id, channel, result
)
-- 3. 拼接channel、result和新pairs数组，构建目标JSON对象
select
 order_id,
 cust_id,
 object_construct(
   'channel', channel,
   'result', result,
   'pairs', filtered_pairs_array
 ) as details
from aggregated_pairs;

方案细节说明 #

1. 扁平化筛选：利用lateral flatten展开pairs数组，通过where子句过滤出key为size或color的元素，同时提取channel和result字段备用；
2. 聚合重组数组：用array_agg()函数按order_id分组，将分散的单个pair元素重新组合成数组；
3. 构建目标JSON：通过object_construct()函数将channel、result和重组后的pairs数组拼接成最终的details对象。

若原表存在pairs数组为空或无符合条件元素的情况，可将array_agg(filtered_pair)替换为coalesce(array_agg(filtered_pair), array_construct())，确保返回空数组而非null。

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