Como posso expandir uma string JSON que está armazenada dentro de uma coluna em uma tabela Snowflake?

Uma correção para a resposta acima: Nulos precisam ser explicitamente tratados para categoria.

O achatamento lateral sozinho não consideraria NULLs, você pode usar OUTER => TRUEou usar UNIONcom condição WHERE Col4 IS NULL.

Documentação EXTERNA

Se FALSE, quaisquer linhas de entrada que não possam ser expandidas, seja porque não podem ser acessadas no caminho ou porque não têm campos ou entradas, serão completamente omitidas da saída.

Se TRUE, exatamente uma linha é gerada para expansões de linha zero (com NULL nas colunas KEY, INDEX e VALUE)

SELECT 
col1,col2,col3,flatten.value:id::STRING AS id,
flatten.value:category::STRING AS category
FROM 
test1 t,
LATERAL FLATTEN(input => t.col4,OUTER => TRUE) flatten 
;

Saída

No Snowflake, você pode conseguir isso usando a FLATTENfunção, que é projetada para manipular dados semiestruturados como arrays JSON. Esta função pode expandir o array armazenado dentro de uma coluna em várias linhas.

Você também usará OBJECT_GETpara extrair campos específicos do objeto JSON.

Veja como você pode obter a transformação desejada no Snowflake:

Consulta SQL:

SELECT 
    t.Col1,
    t.Col2,
    t.Col3,
    f.value:id::STRING AS id,
    f.value:category::STRING AS category
FROM 
    your_table_name t,
    LATERAL FLATTEN(input => t.Col4) f;

Explicação:

1. FLATTEN(input => t.Col4): Esta função nivela a matriz JSON em Col4linhas separadas, onde cada objeto JSON na matriz se torna uma linha separada.
2. f.value:id::STRING: Isso extrai o idcampo do objeto JSON, garantindo que ele seja convertido em uma string.
3. f.value:category::STRING: Isso extrai o categorycampo do objeto JSON, garantindo que ele seja convertido em uma string.
4. LATERAL: Isso é usado para permitir que a FLATTENfunção produza linhas para cada item na matriz JSON.

Resultado:

Isso lhe dará a saída no formato desejado:

Manipulando Nulos:

• Se houver nullem Col4, como visto na terceira linha, FLATTENretornará nullvalores para ide category, que é o que você deseja.

Este método lida eficientemente com a expansão da string JSON e a extração dos campos necessários no Snowflake. Me avise se precisar de mais esclarecimentos!