TRANSFORM function in Snowflake can be used for advanced array transformations. You can apply a function or lambda expression to each element in an array and return a new array with the transformed values. Here’s the basic syntax:
TRANSFORM(input_array, expr)
SELECT TRANSFORM([1, 2, 3], x -> x * 2);
-- Result: [2, 4, 6]
Suppose we have a table that has Coupons as a JSON Array for each Sales Order as following
| order_id | coupon_json_array |
|---|---|
| 111 | [{Coupon:”bbb”, Discount: -1 }, {Coupon:”aaa”, Type:FreeShip}] |
| 222 | [{Coupon:”ccc”, Discount: -2}] |
| 333 | [{Coupon:”ccc”, Discount: -2}, {Coupon:”aaa”}, {Coupon:”eee”} ] |
| 444 |
For each Order, we need to get the comma-separate list of Coupons, if there are any as following
| order_id | coupons_applied |
|---|---|
| 222 | ccc |
| 333 | ccc, aaa, eee |
| 111 | bbb, aaa |
| 444 | |
| 444 |
We can utilize the TRANSFORM function to apply a Lambda Fuction promo OBJECT -> promo:"Coupon" to each element in the JSON array to extract the Coupon as following:
SELECT
order_id
, TRANSFORM(
parse_json(orders.coupon_json_array)::ARRAY,
promo OBJECT -> promo:"Coupon"
) as coupons_applied_array
, ARRAY_TO_STRING(coupons_applied_array, ', ')
FROM orders;
| order_id | coupons_applied_array | coupons_applied |
|---|---|---|
| 111 | [ “bbb”, “aaa” ] | bbb, aaa |
| 222 | [ “ccc” ] | ccc |
| 333 | [ “ccc”, “aaa”, “eee” ] | ccc, aaa, eee |
| 444 | ||
| 444 |
The same can be achieved using the Lateral Flatten in Snowflake:
select order_id, listagg (f.value:Coupon, ', ')
from orders
, lateral flatten(input => parse_json(orders.coupon_json_array)::variant, OUTER => TRUE) as f
group by all
However this will be a lot slower compare to using the TRANSFORM function.