Wyrażenie ścieżki JSON

Dotyczy: Databricks SQL Databricks Runtime

Wyrażenie ścieżki JSON służy do wyodrębniania wartości z ciągu JSON lub VARIANT przy użyciu operatora :

Składnia

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

Nawiasy otaczające fieldnawiasy i index * są rzeczywistymi nawiasami kwadratowymi i nie wskazują opcjonalnej składni.

Parametry

• identifier: identyfikator pola JSON. W przypadku typów JSON STRING identyfikator jest bez uwzględniania wielkości liter. W przypadku VARIANT typów uwzględniana jest wielkość liter.
• [ field ]: literał uwzględniający STRING wielkość liter w nawiasie identyfikujący pole JSON.
• [ * ]: identyfikowanie wszystkich elementów w tablicy JSON. Ta składnia nie jest obsługiwana dla programu VARIANT.
• [ index ]: literał liczby całkowitej identyfikujący określony element w tablicy JSON opartej na 0.

Zwraca

Jeśli STRING nawigowane wyrażenie to STRING. Jeśli VARIANT nawigowane wyrażenie to VARIANT.

Gdy pole JSON istnieje z wartością bez ogranicznika null , otrzymasz wartość SQL NULL dla tej kolumny, a nie wartość tekstową null .

Możesz użyć operatora :: do rzutowania wartości do podstawowych typów danych.

Funkcja from_json umożliwia rzutowanie zagnieżdżonych wyników na bardziej złożone typy danych, takie jak tablice lub struktury.

Uwagi

Można użyć identyfikatora bez ograniczeń, aby odwoływać się do pola JSON, jeśli nazwa nie zawiera spacji ani znaków specjalnych, i nie ma pola w formacie JSON STRING o tej samej nazwie w innym przypadku.

Użyj identyfikatora rozdzielanego, jeśli w innym przypadku nie ma pola o tej samej nazwie.

Notacja [ field ] może być zawsze używana, ale wymaga dokładnego dopasowania wielkości liter pola.

Jeśli usługa Azure Databricks nie może jednoznacznie zidentyfikować pola, zwracany jest błąd. Jeśli nie zostanie znalezione dopasowanie dla dowolnego pola, usługa Azure Databricks zwróci wartość NULL.

NULL Wartość może być zakodowana w elemecie VARIANT, a ta wartość nie jest wartością SQL NULL. W związku z tym parametr ma falsewartość parse_json('null') IS NULL , ale is_variant_null(parse_json('null')) jest .true Zakodowany VARIANT null można przekonwertować na język SQL NULL , rzutując go na jakiś typ. Na przykład parse_json('null')::int IS NULL to true.

Przykłady

W poniższych przykładach użyto danych utworzonych za pomocą instrukcji w przykładowych danych.

W tej sekcji:

• Wyodrębnianie przy użyciu identyfikatora i ograniczników
• Wyodrębnianie zagnieżdżonych pól
• Wyodrębnianie wartości z tablic
• Zachowanie wartości NULL
• Rzutowanie wartości
• Wyrażenia VARIANT
• Przykładowe dane

Wyodrębnianie przy użyciu identyfikatora i ograniczników

> 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

Wyodrębnianie zagnieżdżonych pól

-- 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" }'

Wyodrębnianie wartości z tablic

-- 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"}
  ]                            ]                  ]

Zachowanie wartości NULL

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

Rzutowanie wartości

-- 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"]
  ]'

Wyrażenia 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

Przykładowe dane

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

Funkcje pokrewne

• :operator