Condividi tramite

Espressione di percorso JSON

Si applica a:segno di spunta sì Databricks SQL segno di spunta sì Databricks Runtime

Un'espressione di percorso JSON viene usata per estrarre valori da una stringa JSON o da un VARIANT usando l'operatore :

Sintassi

{ { identifier | [ field ] | [ * ] | [ index ] }
  [ . identifier | [ field ] | [ * ] | [ index ] ] [...] }

Le parentesi quadre che circondano fielde *index sono parentesi quadre effettive e non indicano una sintassi facoltativa.

Parametri

  • identificatore: identificatore di un campo JSON. Per i tipi JSON STRING, l'identificatore non tiene conto delle maiuscole e delle minuscole. Per i tipi VARIANT, è sensibile alle maiuscole e minuscole.
  • [ field ]: Un valore letterale tra parentesi con distinzione tra maiuscole e minuscole STRING che identifica un campo JSON.
  • [ * ]: identificazione di tutti gli elementi in una matrice JSON. Questa sintassi non è supportata per VARIANT.
  • [ index ]: valore letterale integer che identifica un elemento specifico in una matrice JSON basata su 0.

Valori restituiti

Un STRING se l'espressione da esplorare è un STRING. Un VARIANT se l'espressione da esplorare è un VARIANT.

Quando esiste un campo JSON con un valore di null non delimitato, si riceverà un valore sql NULL per tale colonna, non un valore di testo null.

È possibile usare :: operatore per effettuare il cast dei valori ai tipi di dati di base.

Utilizzare la funzione from_json per eseguire il cast dei risultati annidati in tipi di dati più complessi, ad esempio array o struct.

Note

È possibile usare un identificatore non delimitato per fare riferimento a un campo JSON se il nome non contiene spazi o caratteri speciali e non esiste alcun campo nella STRING JSON con lo stesso nome in un caso diverso.

Usare un identificatore delimitato se non è presente alcun campo con lo stesso nome in un caso diverso.

La [ field ] notazione può essere sempre usata, ma richiede che corrisponda esattamente al caso del campo.

Se Azure Databricks non è in grado di identificare in modo univoco un campo, viene restituito un errore. Se non viene trovata alcuna corrispondenza per qualsiasi campo, Azure Databricks restituisce NULL.

Un NULL valore può essere codificato all'interno di un VARIANT, e tale valore non è un SQL NULL. Pertanto, parse_json('null') IS NULL è false, ma is_variant_null(parse_json('null')) è true. Un VARIANT valore Null codificato può essere convertito in SQL NULL effettuando il cast a un tipo specifico. Ad esempio, parse_json('null')::int IS NULL è true.

Esempi

Negli esempi seguenti vengono usati i dati creati con l'istruzione in Dati di esempio.

Contenuto della sezione:

Estrarre utilizzando identificatori e delimitatori

> SELECT raw:owner, raw:OWNER, raw:['owner'], raw:['OWNER'] FROM store_data;
  amy  amy  amy  NULL

-- 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;
  94025      94025      1234

Estrai campi annidati

-- Use dot notation
> SELECT raw:store.bicycle FROM store_data;
  '{ "price":19.95, "color":"red" }'

-- Use brackets
> SELECT raw:['store']['bicycle'] FROM store_data;
  '{ "price":19.95, "color":"red" }'

Estrarre valori da matrici

-- Index elements
> SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- Extract subfields from arrays
> SELECT raw:store.book[*].isbn FROM store_data;
  '[ 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"}
  ]                            ]                  ]

Comportamento nullo

> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
  true          false

Cast valori

-- price is returned as a double, not a string
> SELECT raw:store.bicycle.price::double FROM store_data
  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
  '{ "price":19.95, "color":"red" }'

-- the column returned is an array of string arrays
> SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
  '[
    ["1","2","{\"b\":\"y\",\"a\":\"x\"}]",
    ["3","4"],
    ["5","6"]
  ]'

Espressioni VARIANT

-- Using JSON paths for VARIANT
> SELECT raw:store.bicycle FROM store_data_variant;
  '{ "price":19.95, "color":"red" }'

-- Extracting from VARIANT arrays
> SELECT raw:store.fruit[0], raw_variant:store.fruit[1] FROM store_data_variant;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- SQL NULL behavior of VARIANT NULL values
> SELECT
  parse_json(NULL) IS NULL AS sql_null,
  parse_json('null') IS NULL AS variant_null,
  parse_json('{ "field_a": null }'):field_a IS NULL AS variant_null_value,
  parse_json('{ "field_a": null }'):missing IS NULL AS missing_sql_value_null
  true  false  false  true

-- price is returned as a double, not a VARIANT
> SELECT raw:store.bicycle.price::double FROM store_data_variant
  19.95

Dati di esempio

> 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

 > CREATE TABLE store_data_variant AS SELECT parse_json(raw) FROM store_data;
  • Last updated on