snowflake-cloud-data-platform

Case insensitive Element Name in JSON Traversal in Snowflake


In traversing JSON, Element names are always case-sensitive in Snowflake. What is the best approach when we know that the case-sensitivity rules are not enforced in the JSON for e.g.

order_id promo_json_array
111 [{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:FreeShip}]
222 [{PromoCode:"ccc", Discount: -2}]
333 [{promoCode:"ccc", Discount: -2}, {PromoCode:"aaa"}, {PromoCode:"eee"} ]
444

If I run the following query, I miss the promo code ccc on order_id 333:

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;

Solution

  • It is simple to achieve it - GET_IGNORE_CASE(f.value, 'PromoCode') instead of f.value:PromoCode:

    with orders(order_id,promo_json_array) as (
     select 111, 
        '[{PromoCode:"bbb", Discount: -1 }, {PromoCode:"aaa", Type:"FreeShip"}]'
     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, GET_IGNORE_CASE(f.value, 'PromoCode')::TEXT AS PromoCode,
    from orders
    ,lateral flatten(input => parse_json(orders.promo_json_array), OUTER => TRUE) as f
    group by all;
    

    Output:

    enter image description here