Um caminho lê um valor. Para obter uma linha por elemento de um array, faça o join da tabela com 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 retorna uma linha para cada elemento, com as colunas VALUE (o elemento), INDEX (a posição dele), KEY (para objetos) e mais algumas. LATERAL permite que ele leia a linha à qual está unido.
Duas coisas costumam surpreender. Primeiro, um documento cujo array está vazio não gera nenhuma linha, então some do resultado. Adicione OUTER => TRUE para mantê-lo, com NULL nas colunas achatadas. Segundo, arrays aninhados precisam de um segundo FLATTEN que leia o value do primeiro.
FLATTEN também funciona com objetos: cada chave vira uma linha, com o nome da chave em KEY.
Quantos pedidos têm cada tag: uma linha por tag e depois uma contagem.