JSON 路徑表達式

適用於：Databricks SQL Databricks Runtime

JSON 路徑表示式是用來運用 ：運算子 從 JSON 字串或 VARIANT 擷取 values。

語法

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

和 周圍的field*index括弧是實際的方括弧，而不是表示選擇性語法。

Parameters

• identifier：JSON 欄位的 identifier。 針對 JSON STRING 類型，identifier 不區分大小寫。 針對 VARIANT 類型，它會區分大小寫。
• [ field ]：以括弧括住區分大小寫 STRING 的常值，可識別 JSON 字段。
• [ * ]：識別 JSON 陣列中的所有專案。 不支援 VARIANT此語法。
• [ index ]：整數常值，識別以 0 為基礎的 JSON 陣列中的特定專案。

傳回

STRING如果巡覽的表示式是 ，則為 STRING。 VARIANT如果巡覽的表示式是 ，則為 VARIANT。

當 JSON 欄位存在且具有未界定的 null 值時，您會收到該 column的 SQL NULL 值，而非 null 的文字值。

您可以使用 ：： 運算子，將 values 轉換成基本數據類型。

使用 from_json 函式，將巢狀結果轉換成更複雜的數據類型，例如陣列或結構。

備註

您可以使用未加界限的 identifier 來參照 JSON 欄位，前提是名稱不包含空格或特殊字元，並且 JSON STRING 中沒有名稱相同但大小寫不同的欄位。

如果不同大小寫中沒有相同名稱的欄位，請使用分隔 identifier。

表示 [ field ] 法一律可以使用，但需要您完全符合欄位的案例。

如果 Azure Databricks 無法唯一識別傳回錯誤欄位。 如果找不到任何欄位的相符專案，Azure Databricks 會傳 NULL回 。

NULL值可以在 內VARIANT編碼，而該值不是 SQL NULL。 因此， 是 parse_json('null') IS NULL，false但 is_variant_null(parse_json('null')) 為 true。 VARIANT編碼的 Null 可以轉換成 SQLNULL，方法是將它轉換成某種類型。 例如，parse_json('null')::int IS NULL 是 true。

範例

下列範例會使用在範例數據中 搭配 語句建立的數據。

本節內容：

• 使用 identifier 和分隔符 擷取
• 擷取巢狀字段
• 從陣列擷取 values
• NULL 行為
• 演員 values
• VARIANT 表達式
• 範例資料

使用 identifier 和分隔符進行擷取

> 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

擷取巢狀欄位

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

從陣列擷取 values

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

NULL 行為

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

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

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

範例資料

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

相關函數

• ：算子