Não entendi muito bem porque é necessario que o array seja construido sobre um select "as struct", se poderia ser apenas um array, embora no exemplo abaixo não seja possivel compilar sem essa opção:
SELECT
id_produto,
preco,
id_materia,
ARRAY(
SELECT -- se remover o AS STRUCT da erro
mp,
ROW_NUMBER() OVER() AS idx
FROM
UNNEST(materiasprimas) AS mp
) AS materiaprima_index
FROM
`curso-bigquery-436611.beleza_verde_vendas.produtos`;
Por que usar AS STRUCT?
Quando você quer que cada item do array contenha mais de uma informação (como mp e idx), você precisa usar AS STRUCT. Isso garante que essas informações fiquem juntas.
O que acontece se não usar? Se você remover o AS STRUCT, o SQL não consegue entender que você quer criar um array que contém dois valores juntos. Isso gera um erro.
Exemplo: No seu código, ARRAY(SELECT mp, ROW_NUMBER() OVER() AS idx FROM UNNEST(materiasprimas) AS mp) precisa de AS STRUCT para que cada item do array tenha tanto mp quanto idx. Sem ele, o SQL não saberia como empacotar essas informações.