- Categorias:
Funções de tabela , Funções de dados semiestruturados e estruturados (Extração)
FLATTEN¶
Nivela (explode) valores compostos em várias linhas.
FLATTEN é uma função de tabela que usa uma coluna VARIANT, OBJECT ou ARRAY e produz uma exibição lateral (ou seja, uma exibição inline que contém correlações com outras tabelas que a precedem na cláusula FROM).
FLATTEN pode ser usada para converter dados semiestruturados em uma representação relacional.
Sintaxe¶
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
[ , OUTER => TRUE | FALSE ]
[ , RECURSIVE => TRUE | FALSE ]
[ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
Argumentos¶
Obrigatório:
INPUT => exprA expressão que será nivelada em linhas. A expressão deve ser do tipo de dados VARIANT, OBJECT, ou ARRAY.
Opcional:
PATH => constant_exprO caminho para o elemento em uma estrutura de dados VARIANT que precisa ser nivelada. Pode ser uma cadeia de caracteres de comprimento zero (ou seja, um caminho vazio) se o elemento mais externo tiver que ser nivelado.
Padrão: cadeia de caracteres de comprimento zero (caminho vazio)
OUTER => TRUE | FALSESe
FALSE, quaisquer linhas de entrada que não possam ser expandidas, seja porque não podem ser acessadas no caminho ou porque têm zero campos ou entradas, serão completamente omitidas da saída.Se
TRUE, será gerada exatamente uma linha para expansões de zero linhas (com NULL nas colunas KEY, INDEX, e VALUE).
Padrão:
FALSENota
Uma expansão de zero linhas de um composto vazio exibe NULL na coluna de saída THIS, distinguindo-a de uma tentativa de expandir um tipo de composto inexistente ou errado.
RECURSIVE => TRUE | FALSESe
FALSE, somente o elemento referenciado porPATHserá expandido.Se
TRUE, a expansão será realizada recursivamente para todos os subelementos.
Padrão:
FALSEMODE => 'OBJECT' | 'ARRAY' | 'BOTH'Especifica se apenas objetos, matrizes ou ambos devem ser nivelados.
Padrão:
BOTH
Saída¶
As linhas devolvidas consistem em um conjunto fixo de colunas:
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
- SEQ:
Um número sequencial único associado ao registro de entrada; não é garantido que a sequência esteja livre de lacunas ou seja ordenada de qualquer forma específica.
- KEY:
Para mapas ou objetos, esta coluna contém a chave para o valor explodido.
- PATH:
O caminho para o elemento em uma estrutura de dados que precisa ser nivelada.
- INDEX:
O índice do elemento, se for uma matriz; caso contrário, NULL.
- VALUE:
O valor do elemento da matriz/do objeto nivelado.
- THIS:
O elemento a ser nivelado (útil no nivelamento recursivo).
Nota
As colunas da tabela original (correlacionada) que foi usada como fonte de dados para FLATTEN também estão acessíveis. Se uma única linha da tabela original resultar em várias linhas na exibição nivelada, os valores nesta linha de entrada serão replicados para corresponder ao número de linhas produzidas por FLATTEN.
Notas de uso¶
Para obter informações sobre como usar essa função com os tipos estruturados, consulte Uso da função FLATTEN com valores de tipos estruturados.
Exemplos¶
Consulte também Exemplo: uso de uma junção lateral com a função de tabela FLATTEN e Como usar FLATTEN para filtrar os resultados em uma cláusula WHERE.
O exemplo simples a seguir nivela um registro (observe que o elemento do meio da matriz está faltando):
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1, ,77]'))) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | [0] | 0 | 1 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
| 1 | NULL | [2] | 2 | 77 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
+-----+------+------+-------+-------+------+
As próximas duas consultas mostram o efeito do parâmetro PATH:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), OUTER => TRUE)) f;
+-----+-----+------+-------+-------+-----------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+-----------|
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
+-----+-----+------+-------+-------+-----------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), PATH => 'b')) f;
+-----+------+------+-------+-------+-------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+-------|
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
+-----+------+------+-------+-------+-------+
As próximas duas consultas mostram o efeito do parâmetro OUTER:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'))) f;
+-----+-----+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+------|
+-----+-----+------+-------+-------+------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), OUTER => TRUE)) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | | NULL | NULL | [] |
+-----+------+------+-------+-------+------+
As próximas duas consultas mostram o efeito do parâmetro RECURSIVE:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE )) f;
+-----+------+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+------+------+-------+------------+--------------+
O exemplo a seguir mostra o efeito do parâmetro MODE:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE, MODE => 'OBJECT' )) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
O exemplo a seguir explode uma matriz que está aninhada em outra matriz. Crie a tabela a seguir:
CREATE OR REPLACE TABLE persons AS
SELECT column1 AS id, PARSE_JSON(column2) as c
FROM values
(12712555,
'{ name: { first: "John", last: "Smith"},
contact: [
{ business:[
{ type: "phone", content:"555-1234" },
{ type: "email", content:"j.smith@example.com" } ] } ] }'),
(98127771,
'{ name: { first: "Jane", last: "Doe"},
contact: [
{ business:[
{ type: "phone", content:"555-1236" },
{ type: "email", content:"j.doe@example.com" } ] } ] }') v;
Observe as várias instâncias de LATERAL FLATTEN na cláusula FROM da consulta a seguir. Cada exibição LATERAL baseia-se na anterior para fazer referência a elementos em vários níveis de matrizes.
SELECT id as "ID",
f.value AS "Contact",
f1.value:type AS "Type",
f1.value:content AS "Details"
FROM persons p,
LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
LATERAL FLATTEN(INPUT => f.value:business) f1;
+----------+-----------------------------------------+---------+-----------------------+
| ID | Contact | Type | Details |
|----------+-----------------------------------------+---------+-----------------------|
| 12712555 | { | "phone" | "555-1234" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 12712555 | { | "email" | "j.smith@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "phone" | "555-1236" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "email" | "j.doe@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
+----------+-----------------------------------------+---------+-----------------------+