Výraz cesty JSON

Platí pro: , kontrola Databricks SQL Databricks Runtime

Výraz cesty JSON se používá k extrakci hodnot z řetězce JSON pomocí operátoru :

Syntaxe

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

Hranaté závorky obklopující field, * a index jsou skutečné hranaté závorky, které neudávají volitelnou syntaxi.

Parametry

• identifier: Identifikátor pole JSON nerozlišující malá a velká písmena.
• [ field ]: Literál STRING s rozlišováním velkých a malých písmen v hranatých závorkách, který identifikuje pole JSON.
• [ * ]: Identifikace všech prvků v poli JSON.
• [ index ]: Celočíselný literál identifikující konkrétní prvek v poli JSON založeném na 0.

Vrátí

ŘETĚZEC.

Pokud existuje pole JSON s hodnotou bez oddělovačů null , obdržíte hodnotu SQL NULL pro tento sloupec, nikoli textovou null hodnotu.

Operátor :: můžete použít k přetypování hodnot na základní datové typy.

Pomocí funkce from_json přetypujte vnořené výsledky do složitějších datových typů, jako jsou pole nebo struktury.

Poznámky

Identifikátor bez oddělovače můžete použít k odkazování na pole JSON, pokud název neobsahuje mezery nebo speciální znaky a neexistuje žádné pole se stejným názvem v různých případech.

Pokud v různých případech neexistuje pole se stejným názvem, použijte identifikátor s oddělovači.

Zápis [ field ] lze vždy použít, ale vyžaduje, abyste přesně odpovídali případu pole.

Pokud Databricks SQL nemůže jednoznačně identifikovat pole, vrátí se chyba. Pokud se nenajde shoda pro žádné pole, Vrátí Databricks SQL NULL.

Příklady

V následujících příkladech se používají data vytvořená příkazem v části Ukázková data.

V této části:

• Extrakce pomocí identifikátoru a oddělovačů
• Extrahování vnořených polí
• Extrakce hodnot z polí
• Chování s hodnotou NULL
• Přetypování hodnot
• Ukázková data

Extrakce pomocí identifikátoru a oddělovačů

> 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

Extrahování vnořených polí

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

Extrakce hodnot z polí

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

Chování s hodnotou NULL

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

Přetypování hodnot

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

Ukázková data

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

Související funkce

• :Operátor