The fastest way to perform this operation is with the TRANSFORM
function in Snowflake. This approach is performed in-place, it doesn't require pivoting and grouping the resultset.
WITH orders AS (...)
SELECT
order_id,
TRANSFORM(
parse_json(orders.promo_json_array)::ARRAY,
promo OBJECT -> promo:"PromoCode"
) as promo_codes_applied
FROM orders
;