Espressione del percorso JSON

Articolo
03/01/2024

Si applica a: check contrassegnato con sì controllo SQL di Databricks Databricks Runtime

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

Sintassi

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

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

Parametri

identificatore: identificatore senza distinzione tra maiuscole e minuscole di un campo JSON.
[ field ]: valore letterale STRING con distinzione tra maiuscole e minuscole che identificano un campo JSON.
[ * ]: identificazione di tutti gli elementi in una matrice JSON.
[ index ]: valore letterale integer che identifica un elemento specifico in una matrice JSON basata su 0.

Restituisce

STRINGA.

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

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

Usare la funzione from_json per eseguire il cast dei risultati annidati in tipi di dati più complessi, ad esempio matrici 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 con lo stesso nome in caso diverso.

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

La [ field ] notazione può essere sempre utilizzata, ma richiede di corrispondere esattamente al caso del campo.

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

Esempi

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

In questa sezione:

Estrarre usando identificatore e delimitatori
Estrarre campi annidati
Estrarre valori dalle matrici
Comportamento NULL
Eseguire il cast dei valori
Dati di esempio

Estrarre usando identificatore 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

Estrarre 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 dalle 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 NULL

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

Eseguire il cast dei 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"]
  ]'

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

:Operatore