Consultar cadeias de caracteres JSON

Este artigo descreve os operadores de SQL do Databricks que você poderá usar para consultar e transformar os dados semiestruturados armazenados como cadeias de caracteres JSON.

Observação

Esse recurso permite ler dados semiestruturados sem achatar os arquivos. No entanto, para obter o desempenho ideal das consultas de leitura, a Databricks recomenda extrair colunas aninhadas usando os tipos de dados corretos.

Extraia uma coluna de campos que contenham cadeias de caracteres JSON usando a sintaxe <column-name>:<extraction-path>, em que <column-name> é o nome da coluna de cadeia de caracteres e <extraction-path> é o caminho para o campo a ser extraído. Os resultados retornados são cadeias de caracteres.

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 referenciam essa 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 seu caminho de extração.

Pode-se fornecer nomes de coluna entre colchetes. As colunas referenciadas entre colchetes são correspondidas com diferenciação entre maiúsculas e minúsculas. O nome da coluna também é referenciado sem diferenciar maiúsculas de 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 aspas invertidas para fazer escape de espaços e caracteres especiais. Os nomes dos campos são comparados sem diferenciar maiúsculas de 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      |
+----------+----------+-----------+

Observação

Se um registro JSON contiver várias colunas que possam corresponder ao seu caminho de extração devido à correspondência que não diferencia maiúsculas de minúsculas, você receberá uma mensagem de erro solicitando o uso de colchetes. Se houver correspondência entre colunas nas linhas, você não receberá nenhum erro. O seguinte gerará um erro: {"foo":"bar", "Foo":"bar"}e o seguinte não gerará um erro:

{"foo":"bar"}
{"Foo":"bar"}

Extrair campos aninhados

Especifique campos aninhados com notação de ponto ou usando colchetes. Quando você usa colchetes, as colunas são correspondidas com distinção entre 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

Indexe elementos em matrizes usando colchetes. Os índices são baseados em 0. Pode-se usar um asterisco (*) seguido pela notação de ponto ou colchete para extrair subcampos de todos os elementos em uma matriz.

Observação

A sintaxe [*] só é válida dentro de uma expressão de caminho JSON, após o operador : em uma coluna de string JSON. Não há suporte para:

  • Colunas nativas ARRAY . Aplicar [*] a uma coluna de matriz retorna o erro [INVALID_USAGE_OF_STAR_OR_REGEX]. Para extrair um campo de cada elemento de um array de structs, use array_column.field_name, transform ou explode em vez disso.
  • VARIANT colunas. Veja como consultar dados variantes?.
-- 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"} |                                 |          |
| ]                          | ]                | ]                   |                                 |          |
+----------------------------+------------------+---------------------+---------------------------------+----------+

Converter valores

Pode-se usar :: para converter valores em tipos de dados básicos. Use o método from_json para converter resultados aninhados em tipos de dados mais complexos, como matrizes ou estruturas.

-- 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 houver um campo JSON com valor null, você receberá um valor de SQL null para essa coluna, não um valor de texto null.

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 SQL do Spark

O Apache Spark tem várias funções internas para trabalhar com dados complexos e aninhados. O notebook a seguir contém exemplos.

Além disso, funções de ordem mais alta fornecem muitas opções adicionais quando operadores Spark internos não estão disponíveis para transformar dados da maneira desejada.

Notebook com dados aninhados complexos

Obter notebook

  • Last updated on