パスで読み取れるのは1つの値です。配列の要素ごとに1行を得るには、テーブルをFLATTENと結合します。
SELECT src:order_id::STRING AS order_id, f.value:sku::STRING AS sku
FROM raw.orders_json,
LATERAL FLATTEN(input => src:lines) f;
FLATTENは要素ごとに1行を返し、VALUE(要素)、INDEX(その位置)、KEY(オブジェクトの場合)などの列を持ちます。LATERALを使うと、結合した行を読み取れます。
つまずきやすい点が2つあります。1つ目は、配列が空のドキュメントでは1行も返されないため、結果から消えてしまうことです。残すにはOUTER => TRUEを追加すると、展開した列がNULLになって残ります。2つ目は、入れ子の配列には、1つ目のvalueを読む2つ目のFLATTENが必要なことです。
FLATTENはオブジェクトにも使えます:各キーが1行になり、キーの名前はKEYに入ります。