Espressione di percorso JSON

Si applica a: Databricks SQL Databricks Runtime

Un'espressione di percorso JSON viene usata per estrarre values 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.

Parameters

• identifier: identifier di un campo JSON. Per i tipi JSON di STRING, identifier è insensibile alle maiuscole. Per VARIANT i tipi, viene fatta distinzione tra maiuscole e minuscole.
• [ field ]: valore letterale 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

Oggetto STRING se l'espressione da esplorare è un oggetto STRING. Oggetto VARIANT se l'espressione da esplorare è un oggetto VARIANT.

Quando esiste un campo JSON con un valore null non delimitato, riceverai un valore SQL NULL per quel column, e non un valore di testo null.

È possibile usare :: operatore per eseguire il cast di values 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 identifier non delimitato per fare riferimento a un campo JSON se il nome non contiene spazi o caratteri speciali e non esiste alcun campo nel STRING JSON dello stesso nome in un caso diverso.

Utilizzare un identifier 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 VARIANToggetto e tale valore non è 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 eseguendo il cast in un determinato tipo. 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 usando identifier e i delimitatori
• Estrarre campi annidati
• Estrarre values da matrici
• Comportamento NULL
• cast values
• Espressioni VARIANT
• Dati di esempio

Estrarre utilizzando identifier 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

Campi annidati estratti

-- 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 values 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 NULL

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

Attori values

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

Funzioni correlate

• :operatore