Consulta de dados semiestruturados¶
Este tópico explica como usar operadores e funções especiais para consultar dados hierárquicos complexos armazenados em um VARIANT.
(Para exemplos simples de como extrair valores de ARRAYs e OBJECTs, consulte Acesso aos elementos de um ARRAY por índice ou por fatia e Elementos de acesso de um OBJECT por chave).
Normalmente, os dados hierárquicos foram importados para um VARIANT a partir de um dos seguintes formatos de dados com suporte:
JSON
Avro
ORC
Parquet
(Para obter mais informações sobre consulta de dados XML (por exemplo, dados originados no formato de dados XML e convertidos em um OBJECT chamando PARSE_XML), consulte XMLGET).
Dica
Você pode usar o serviço de otimização de pesquisa para melhorar o desempenho da consulta. Para obter mais detalhes, consulte Serviço de otimização de pesquisa.
Neste tópico:
Amostra de dados usados em exemplos¶
Exceto onde indicado, os exemplos neste tópico se referem a uma tabela chamada car_sales que contém uma única coluna VARIANT chamada src. Este VARIANT contém ARRAYs e OBJECTs aninhados.
Crie a tabela e carregue-a:
CREATE OR REPLACE TABLE car_sales
(
src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
Selecione os dados:
SELECT * FROM car_sales;
+-------------------------------------------+
| SRC |
|-------------------------------------------|
| { |
| "customer": [ |
| { |
| "address": "San Francisco, CA", |
| "name": "Joyce Ridgely", |
| "phone": "16504378889" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Valley View Auto Sales", |
| "salesperson": { |
| "id": "55", |
| "name": "Frank Beasley" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| ] |
| } |
| { |
| "customer": [ |
| { |
| "address": "New York, NY", |
| "name": "Bradley Greenbloom", |
| "phone": "12127593751" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Tindel Toyota", |
| "salesperson": { |
| "id": "274", |
| "name": "Greg Northrup" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
| ] |
| } |
+-------------------------------------------+
Como percorrer dados semiestruturados¶
Insira um símbolo de dois pontos : entre o nome da coluna VARIANT e qualquer elemento de primeiro nível: <column>:<level1_element>.
Nota
Nos exemplos a seguir, a saída da consulta é incluída entre aspas duplas porque é VARIANT, e não VARCHAR. (Os valores VARIANT não são cadeias de caracteres; os valores VARIANT contêm cadeias de caracteres). Os operadores : e subsequentes . e [] sempre retornam valores VARIANT contendo cadeias de caracteres.
Por exemplo, obtenha uma lista de todos os nomes de concessionárias:
SELECT src:dealership
FROM car_sales
ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Tindel Toyota" |
| "Valley View Auto Sales" |
+--------------------------+
Há duas maneiras de acessar elementos em um objeto JSON:
Notação de pontos (neste tópico).
Notação de parênteses (neste tópico).
Importante
Independentemente da notação que você utiliza, o nome da coluna não diferencia maiúsculas de minúsculas, mas os nomes dos elementos sim. Por exemplo, na lista a seguir, os dois primeiros caminhos são equivalentes, mas o terceiro não é:
src:salesperson.name
SRC:salesperson.name
SRC:Salesperson.Name
Notação de pontos¶
Use notação de pontos para percorrer um caminho em um objeto JSON: <column>:<level1_element>.<level2_element>.<level3_element>. Opcionalmente, inclua nomes de elementos entre aspas duplas: <column>:"<level1_element>"."<level2_element>"."<level3_element>".
Nota
As regras para as chaves JSON (nomes dos elementos) são diferentes das regras para os identificadores SQL do Snowflake.
Para obter mais informações sobre as regras para identificadores SQL do Snowflake, consulte: Requisitos para identificadores.
Para obter mais informações sobre as chaves JSON, consulte http://json.org, em particular a descrição de “string”.
Se um nome de elemento não estiver de acordo com as regras de identificador SQL do Snowflake (por exemplo, se contiver espaços), então você precisa incluir o nome entre aspas duplas. Abaixo estão alguns exemplos (nem todos são do exemplo car_sales acima) de nomes de elementos JSON válidos que não são nomes de identificadores Snowflake válidos, a menos que estejam entre aspas duplas:
-- This contains a blank.
SELECT src:"company name" FROM partners;
-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;
-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
Obtenha os nomes de todos os vendedores que venderam carros:
SELECT src:salesperson.name
FROM car_sales
ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
Notação de parênteses¶
Alternativamente, use notação de parênteses para percorrer o caminho em um objeto: <column>['<level1_element>']['<level2_element>']. Coloque os nomes dos elementos entre aspas simples. Os valores são recuperados como cadeias de caracteres.
Obtenha os nomes de todos os vendedores que venderam carros:
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
Recuperação de uma instância única de um elemento repetido¶
Recupere uma instância numerada específica de um elemento filho em uma matriz repetida, adicionando um predicado numerado (a partir de 0) à referência da matriz.
Observe que para recuperar todas as instâncias de um elemento filho em uma matriz repetida, é necessário nivelar a matriz. Consulte um exemplo em Uso da função FLATTEN para analisar matrizes neste tópico.
Obtenha os detalhes do veículo para cada venda:
SELECT src:customer[0].name, src:vehicle[0]
FROM car_sales
ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0] |
|----------------------+-------------------------|
| "Bradley Greenbloom" | { |
| | "extras": [ |
| | "ext warranty", |
| | "rust proofing", |
| | "fabric protection" |
| | ], |
| | "make": "Toyota", |
| | "model": "Camry", |
| | "price": "23500", |
| | "year": "2017" |
| | } |
| "Joyce Ridgely" | { |
| | "extras": [ |
| | "ext warranty", |
| | "paint protection" |
| | ], |
| | "make": "Honda", |
| | "model": "Civic", |
| | "price": "20275", |
| | "year": "2017" |
| | } |
+----------------------+-------------------------+
Obtenha o preço de cada carro vendido:
SELECT src:customer[0].name, src:vehicle[0].price
FROM car_sales
ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500" |
| "Joyce Ridgely" | "20275" |
+----------------------+----------------------+
Conversão explícita de valores¶
Quando você extrai valores de um VARIANT, pode converter explicitamente os valores para o tipo de dados desejado. Por exemplo, você pode extrair os preços como valores numéricos e fazer cálculos sobre eles:
SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
FROM car_sales
ORDER BY tax;
+--------+
| TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
Por padrão, quando VARCHARs, DATEs, TIMEs e TIMESTAMPs são recuperados de uma coluna VARIANT, os valores ficam entre aspas duplas. Você pode eliminar as aspas duplas, convertendo explicitamente os valores. Por exemplo:
SELECT src:dealership, src:dealership::VARCHAR
FROM car_sales
ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota" | Tindel Toyota |
| "Valley View Auto Sales" | Valley View Auto Sales |
+--------------------------+-------------------------+
Para obter mais informações sobre a conversão de valores VARIANT, consulte Uso de valores em um VARIANT.
Para obter mais informações sobre conversão em geral, consulte Conversão do tipo de dados.
Using FLATTEN to Filter the Results in a WHERE Clause¶
The FLATTEN function explodes nested values into separate columns. You can use the function to filter query results in a WHERE clause.
The following example returns key-value pairs that match a WHERE clause and displays them in separate columns:
CREATE TABLE pets (v variant);
INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');
SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';
+-------------------------+---------+--------------+
| V | KEY | VALUE |
|-------------------------+---------+--------------|
| { | species | "dog" |
| "is_dog": "true", | | |
| "name": "Fido", | | |
| "species": "dog" | | |
| } | | |
| { | name | "dog terror" |
| "is_dog": "false", | | |
| "name": "dog terror", | | |
| "species": "cat" | | |
| } | | |
+-------------------------+---------+--------------+
Using FLATTEN to List Distinct Key Names¶
When working with unfamiliar semi-structured data, you might not know the key names in an OBJECT. You can use the FLATTEN function with the RECURSIVE argument to return the list of distinct key names in all nested elements in an OBJECT:
SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
TYPEOF(f.value) AS "Type",
COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
The REGEXP_REPLACE function removes the array index values (e.g. [0]) and replaces them with brackets ([]) to group array elements.
For example:
{"a": 1, "b": 2, "special" : "data"} <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"} <----row 2 of VARIANT column
Output from query:
+---------+---------+-------+
| Path | Type | Count |
|---------+---------+-------|
| a | INTEGER | 1 |
| b | INTEGER | 1 |
| c | INTEGER | 1 |
| d | INTEGER | 1 |
| normal | VARCHAR | 1 |
| special | VARCHAR | 1 |
+---------+---------+-------+
Using FLATTEN to List Paths in an OBJECT¶
Related to Using FLATTEN to List Distinct Key Names, you can use the FLATTEN function with the RECURSIVE argument to retrieve all keys and paths in an OBJECT.
The following query returns keys, paths, and values (including VARIANT «null» values) for all data types stored in a VARIANT column. The code assumes that the VARIANT column contains an OBJECT in each row.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.index,
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
The following query is similar to the first query, but excludes nested OBJECTs and ARRAYs:
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
The queries return the following values:
- <variant_column>
OBJECT stored as a row in the VARIANT column.
- Seq
Unique sequence number associated with the data in the row.
- Key
String associated with a value in the data structure.
- Path
Path to the element within the data structure.
- Level
Level of the key-value pair within the data structure.
- Type
Data type for the value.
- Index
Index of the element in the data structure. Applies to ARRAY values only; otherwise NULL.
- Current Level Value
Value at the current level in the data structure.
- Above Level Value
Value one level higher in the data structure.
Uso da função FLATTEN para limpar matrizes¶
Analise uma matriz usando a função FLATTEN. FLATTEN é uma função de tabela que produz uma exibição lateral de uma coluna VARIANT, OBJECT ou ARRAY. A função retorna uma linha para cada objeto, e o modificador LATERAL junta os dados com qualquer informação fora do objeto.
Obtenha os nomes e endereços de todos os clientes. Converta a saída VARIANT para valores de cadeia de caracteres:
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);
+--------------------+-------------------+
| Customer Name | Address |
|--------------------+-------------------|
| Joyce Ridgely | San Francisco, CA |
| Bradley Greenbloom | New York, NY |
+--------------------+-------------------+
Uso da função FLATTEN para analisar matrizes aninhadas¶
A matriz extras está aninhada dentro da matriz vehicle nos dados de exemplo:
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
Adicione uma segunda cláusula FLATTEN para nivelar a matriz extras dentro da matriz vehicle nivelada e recupere os “extras” comprados para cada carro vendido:
SELECT
vm.value:make::string as make,
vm.value:model::string as model,
ve.value::string as "Extras Purchased"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:vehicle) vm
, LATERAL FLATTEN(INPUT => vm.value:extras) ve
ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing |
+--------+-------+-------------------+
Análise de texto como valores VARIANT usando a função PARSE_JSON¶
Analise o texto como um documento JSON usando a função PARSE_JSON.
Se a entrada for NULL, a saída também será NULL. Entretanto, se a cadeia de caracteres de entrada for null, ela é interpretada como um valor VARIANT null; ou seja, o resultado não é um SQL NULL mas um valor real usado para representar um valor nulo em formatos semiestruturados.
Para um exemplo, consulte Amostra de dados usados em exemplos neste tópico.
Extração de valores usando a função GET¶
GET aceita um valor VARIANT, OBJECT ou ARRAY como primeiro argumento e extrai o valor VARIANT do elemento no caminho fornecido como segundo argumento.
Calcule e extraia o último elemento de cada matriz em uma coluna VARIANT usando as funções GET e ARRAY_SIZE. ARRAY_SIZE retorna o tamanho da matriz de entrada:
Nota
Este exemplo se afasta da tabela car_sales usada em outros lugares neste tópico.
CREATE OR replace TABLE colors (v variant);
INSERT INTO
colors
SELECT
parse_json(column1) AS v
FROM
VALUES
('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
v;
SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;
+---------------+-------------------------+
| V | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [ | { |
| { | "b": 255, |
| "b": 0, | "g": 0, |
| "g": 12, | "r": 0 |
| "r": 255 | } |
| }, | |
| { | |
| "b": 0, | |
| "g": 255, | |
| "r": 0 | |
| }, | |
| { | |
| "b": 255, | |
| "g": 0, | |
| "r": 0 | |
| } | |
| ] | |
| [ | { |
| { | "c": 1, |
| "c": 0, | "k": 0, |
| "k": 0, | "m": 1, |
| "m": 1, | "y": 0 |
| "y": 1 | } |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 0, | |
| "y": 1 | |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 1, | |
| "y": 0 | |
| } | |
| ] | |
+---------------+-------------------------+
Extração de valores por caminho usando a função GET_PATH¶
Extraia um valor de uma coluna VARIANT usando a função GET_PATH , :. A função é uma variação de GET, usada para extrair um valor usando um nome de caminho. GET_PATH é equivalente a uma cadeia de funções GET.
Obtenha a marca do veículo comprado por cada cliente:
SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;
+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda" |
| "Toyota" |
+----------------------------------+
Como percorrer dados semiestruturados descreve a sintaxe do caminho usada para recuperar elementos em uma coluna VARIANT. A sintaxe é abreviatura para a função GET ou GET_PATH , :. Ao contrário da sintaxe do caminho, estas funções podem lidar com caminhos irregulares ou elementos de caminho.
As consultas a seguir produzem os mesmos resultados:
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;
Análise de matrizes diretamente de um arquivo de dados preparado¶
Suponha que um arquivo preparado chamado contacts.json.gz contenha os seguintes dados:
{
"root": [
{
"employees": [
{
"firstName": "Anna",
"lastName": "Smith"
},
{
"firstName": "Peter",
"lastName": "Jones"
}
]
}
]
}
Considere também que um formato de arquivo chamado my_json_format inclui TYPE=JSON em sua definição.
Consulte o nome do primeiro funcionário no arquivo preparado. Neste exemplo, o arquivo está localizado no estágio da tabela customers, mas pode estar localizado em qualquer estágio interno (ou seja, Snowflake) ou externo:
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;
+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '|| |
| S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME|| |
| ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith |
+----------------------------------------------+