Wyrażenie ścieżki JSON SQL Databricks

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

Składnia

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

Nawiasy otaczające field i *index rzeczywistymi nawiasami kwadratowymi, które nie wskazują opcjonalnej składni.

Parametry

  • identifier:identyfikator pola JSON bez uwzględniania liter.
  • [ field ]: literał STRING z rozróżnianą wielkością literału CIĄGU w nawiasach, identyfikujący pole JSON.
  • [ * ]: identyfikowanie wszystkich elementów w tablicy JSON.
  • [ index ]: literał liczby całkowitej identyfikujący określony element w tablicy JSON opartej na wartości 0.

Zwraca

CIĄG.

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

Można użyć operatora :: do rzutowania wartości na podstawowe typy danych.

Użyj funkcji from_json, aby rzutować zagnieżdżone wyniki na bardziej złożone typy danych, takie jak tablice lub struktury.

Uwagi

Możesz użyć identyfikatora bez ogranicznika, aby odwołać się do pola JSON, jeśli nazwa nie zawiera spacji lub znaków specjalnych, a w różnych przypadkach nie ma pola o tej samej nazwie.

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

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

Jeśli użytkownik SQL databricks nie może jednoznacznie zidentyfikować pola, zwracany jest błąd. Jeśli nie znaleziono dopasowania dla dowolnego pola, funkcja Databricks SQL zwraca wartość NULL .

Przykłady

W poniższych przykładach są wykorzystywane dane utworzone za pomocą instrukcji w przykładowych danych.

W tej sekcji:

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 pól zagnieżdżonych

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

Wartości rzutowania

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

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