Snowflake中JSON遍历如何忽略元素名称大小写？

问题场景 #

Snowflake中遍历JSON时元素名称严格区分大小写，当JSON字段存在大小写变体（如示例中的PromoCode和promoCode），原查询仅提取PromoCode会遗漏promoCode对应的数据（如order_id=333的ccc值）。

订单表示例：

原查询（存在遗漏问题）：

with orders as (
  select 111 as order_id, '[{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:"FreeShip"}]' as promo_json_array
  union all
  select 222, '[{PromoCode:"ccc", Discount: -2}]'
  union all
  select 333, '[{promoCode:"ccc", Discount: -2}, {PromoCode:"aaa"}, {PromoCode:"eee"} ]'
  union all
  select 444, null
)
select order_id, f.value:PromoCode::string,
from orders
, lateral flatten(input => parse_json(orders.promo_json_array)::variant, OUTER => TRUE) as f
group by all;

最佳处理方案 #

方案1：COALESCE匹配所有已知大小写变体 #

如果字段大小写变体数量少且明确，直接用COALESCE依次尝试所有可能的键名，确保不遗漏数据。

修改后查询：

with orders as (
  select 111 as order_id, '[{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:"FreeShip"}]' as promo_json_array
  union all
  select 222, '[{PromoCode:"ccc", Discount: -2}]'
  union all
  select 333, '[{promoCode:"ccc", Discount: -2}, {PromoCode:"aaa"}, {PromoCode:"eee"} ]'
  union all
  select 444, null
)
select 
  order_id, 
  COALESCE(f.value:PromoCode::string, f.value:promoCode::string) as promo_code
from orders
, lateral flatten(input => parse_json(orders.promo_json_array)::variant, OUTER => TRUE) as f
group by all;

优点：实现简单，性能开销小；缺点：无法覆盖未知的大小写变体（如PROMOCODE）。

方案2：统一转换JSON键为小写（或大写）后提取 #

如果字段变体多或不确定，先将JSON对象的所有键转换为统一大小写（如全小写），再提取目标字段，彻底解决大小写问题。

使用OBJECT_CONSTRUCT_LOWER函数转换键为小写，再提取promocode：

with orders as (
  select 111 as order_id, '[{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:"FreeShip"}]' as promo_json_array
  union all
  select 222, '[{PromoCode:"ccc", Discount: -2}]'
  union all
  select 333, '[{promoCode:"ccc", Discount: -2}, {PromoCode:"aaa"}, {PromoCode:"eee"} ]'
  union all
  select 444, null
)
select 
  order_id, 
  -- 将每个JSON对象的键转成小写，再提取统一的promocode字段
  OBJECT_CONSTRUCT_LOWER(f.value):promocode::string as promo_code
from orders
, lateral flatten(input => parse_json(orders.promo_json_array)::variant, OUTER => TRUE) as f
group by all;

优点：一次性覆盖所有大小写变体，扩展性强；缺点：对复杂嵌套JSON的转换会有轻微性能开销，可忽略不计。

方案3：动态遍历键名匹配（适合极端变体场景） #

如果键名大小写完全无规律，可通过OBJECT_KEYS遍历所有键，匹配忽略大小写的目标字段名：

with orders as (
  select 111 as order_id, '[{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:"FreeShip"}]' as promo_json_array
  union all
  select 222, '[{PromoCode:"ccc", Discount: -2}]'
  union all
  select 333, '[{promoCode:"ccc", Discount: -2}, {PromoCode:"aaa"}, {PromoCode:"eee"} ]'
  union all
  select 444, null
)
select 
  order_id,
  -- 遍历所有键，找到忽略大小写匹配"promocode"的键对应值
  MAX(CASE WHEN LOWER(k.key) = 'promocode' THEN f.value[k.key]::string END) as promo_code
from orders
, lateral flatten(input => parse_json(orders.promo_json_array)::variant, OUTER => TRUE) as f
, lateral flatten(input => OBJECT_KEYS(f.value)) as k
group by order_id, f.value;

优点：完全兼容任意大小写变体；缺点：需要额外展开键名，性能开销略高，仅适合极端场景。

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