Consultar cadeias de caracteres JSON
Este artigo descreve os operadores SQL Databricks que você pode usar para consultar e transformar dados semiestruturados armazenados como cadeias de caracteres JSON.
Nota
Esse recurso permite que você leia dados semiestruturados sem nivelar os arquivos. No entanto, para um desempenho de consulta de leitura ideal, o Databricks recomenda que você extraia colunas aninhadas com os tipos de dados corretos.
Você extrai uma coluna de campos que contêm cadeias de caracteres JSON usando a sintaxe <column-name>:<extraction-path>, onde <column-name> é o nome da coluna da cadeia de caracteres e <extraction-path> é o caminho para o campo a ser extraído. Os resultados retornados são strings.
Criar uma tabela com dados altamente aninhados
Execute a consulta a seguir para criar uma tabela com dados altamente aninhados. Todos os exemplos neste artigo fazem referência a esta tabela.
CREATE TABLE store_data AS SELECT
'{
"store":{
"fruit": [
{"weight":8,"type":"apple"},
{"weight":9,"type":"pear"}
],
"basket":[
[1,2,{"b":"y","a":"x"}],
[3,4],
[5,6]
],
"book":[
{
"author":"Nigel Rees",
"title":"Sayings of the Century",
"category":"reference",
"price":8.95
},
{
"author":"Herman Melville",
"title":"Moby Dick",
"category":"fiction",
"price":8.99,
"isbn":"0-553-21311-3"
},
{
"author":"J. R. R. Tolkien",
"title":"The Lord of the Rings",
"category":"fiction",
"reader":[
{"age":25,"name":"bob"},
{"age":26,"name":"jack"}
],
"price":22.99,
"isbn":"0-395-19395-8"
}
],
"bicycle":{
"price":19.95,
"color":"red"
}
},
"owner":"amy",
"zip code":"94025",
"fb:testid":"1234"
}' as raw
Extrair uma coluna de nível superior
Para extrair uma coluna, especifique o nome do campo JSON no caminho de extração.
Você pode fornecer nomes de colunas entre colchetes. As colunas referenciadas entre parênteses são combinadas com diferenciação de maiúsculas e minúsculas. O nome da coluna também é referenciado sem distinção entre maiúsculas e minúsculas.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Use backticks para escapar de espaços e caracteres especiais. Os nomes dos campos são correspondidos sem distinção entre maiúsculas e minúsculas.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Nota
Se um registro JSON contiver várias colunas que podem corresponder ao seu caminho de extração devido à correspondência que não diferencia maiúsculas de minúsculas, você receberá um erro solicitando o uso de colchetes. Se você tiver correspondências de colunas entre linhas, não receberá erros. O seguinte irá gerar um erro: {"foo":"bar", "Foo":"bar"}, e o seguinte não irá lançar um erro:
{"foo":"bar"}
{"Foo":"bar"}
Extrair campos aninhados
Você especifica campos aninhados por meio de notação de pontos ou usando colchetes. Quando você usa colchetes, as colunas são correspondidas com diferenciação de maiúsculas e minúsculas.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Extrair valores de matrizes
Você indexa elementos em matrizes com colchetes. Os índices são baseados em 0. Você pode usar um asterisco (*) seguido de notação de ponto ou colchete para extrair subcampos de todos os elementos em uma matriz.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Transmitir valores
Você pode usar :: para converter valores para tipos de dados básicos. Use o método from_json para converter resultados aninhados em tipos de dados mais complexos, como matrizes ou structs.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
Comportamento NULL
Quando existir um campo JSON com um null valor, você receberá um valor SQL null para essa coluna, não um valor de null texto.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Transformar dados aninhados usando operadores Spark SQL
O Apache Spark tem várias funções integradas para trabalhar com dados complexos e aninhados. O bloco de notas seguinte contém exemplos.
Além disso, as funções de ordem superior fornecem muitas opções adicionais quando os operadores integrados do Spark não estão disponíveis para transformar dados da maneira desejada.