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;
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: