查詢變體資料

發行項
12/21/2024

重要

這項功能處於公開預覽狀態。

本文說明可用來查詢和轉換儲存為 VARIANT的半結構化數據的 Databricks SQL 運算符。 Databricks VARIANT Runtime 15.3 和更新版本提供數據類型。

Databricks 建議使用 VARIANT JSON 字串。對於目前使用想要移轉的 JSON 字串的使用者，請參閱變體與 JSON 字串有什麼不同？。

如果您想要查看查詢以 JSON 字串儲存的半結構化數據範例，請參閱查詢 JSON 字串。

注意

VARIANT 欄位無法用於叢集鍵、分割區或 Z 順序索引鍵。 VARIANT 資料類型無法用於比較、分組、排序和設定作業。如需完整的限制清單，請參閱限制。

使用 Variant 資料行建立數據表

執行下列查詢，以建立具有高度巢狀數據儲存為 VARIANT的數據表。本文章中的範例全部都參考此資料表。

CREATE TABLE store_data AS
SELECT parse_json(
  '{
    "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

查詢 Variant 資料行中的欄位

在 Azure Databricks 上查詢 JSON 字串和其他複雜數據類型的語法適用於 VARIANT 數據，包括：

使用 : 來選取最上層欄位。
使用 . 或 [<key>] 來選取具有具名索引鍵的巢狀欄位。
使用 [<index>] 從陣列中選取值。

注意

如果功能變數名稱包含句號（.），您必須使用方括弧（[ ]）逸出它。例如，下列查詢會選取名為 zip.code的欄位：

SELECT raw:['zip.code'] FROM store_data

擷取最上層變體欄位

若要擷取欄位，請在擷取路徑中指定 JSON 欄位的名稱。功能變數名稱一律區分大小寫。

SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

如果找不到路徑，則結果的類型NULL為 VARIANT 。

擷取 Variant 巢狀欄位

您可以透過點標記法或使用括號來指定巢狀欄位。功能變數名稱一律區分大小寫。

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

如果找不到路徑，則結果的類型NULL為 VARIANT 。

從 Variant 陣列擷取值

您可以使用括弧在陣列中編製元素的索引。索引是以 0 為基礎。

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

+-------------------+------------------+
| fruit             | fruit            |
+-------------------+------------------+
| {                 | {                |
|   "type":"apple", |   "type":"pear", |
|   "weight":8      |   "weight":9     |
| }                 | }                |
+-------------------+------------------+

如果找不到路徑，或陣列索引超出界限，則結果為 NULL。

扁平化變體物件和陣列

variant_explode數據表值產生器函式可用來扁平化VARIANT數位和物件。

因為 variant_explode 是產生器函式，所以您會使用它作為子句的一 FROM 部分，而不是在 SELECT 清單中，如下列範例所示：

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store);

+-------+--------------------+
|    key|               value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
|   book|[{"author":"Nigel...|
|  fruit|[{"type":"apple",...|
+-------+--------------------+

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

+---+-----------------+
|pos|            value|
+---+-----------------+
|  0|                1|
|  1|                2|
|  2|{"a":"x","b":"y"}|
+---+-----------------+

Variant 類型轉換規則

您可以使用類型來儲存陣列和純量 VARIANT 。嘗試將變體類型轉換成其他類型時，一般轉型規則會套用至個別值和欄位，並包含下列其他規則。

注意

variant_get 並 try_variant_get 接受類型自變數，並遵循這些轉換規則。

來源類型	行為
`VOID`	結果是 `NULL` 類型的 `VARIANT`。
`ARRAY<elementType>`	`elementType`必須是可以轉換成`VARIANT`的型別。

使用或推斷型schema_of_variant別時，函式會在無法解析的衝突型別存在時回復為schema_of_variant_agg類型，而不是VARIANTSTRING類型。

您可以使用 :: 或 cast 將值轉換成支援的數據類型。

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

Variant Null 規則

Variant 可以包含兩種 Null：

SQL NULL：SQL NULL表示值遺失。這些與處理結構化數據時相同 NULL。
Variant ：Variant NULLNULLs 表示 Variant 明確包含NULL值。這些與 SQL NULL不同，因為 NULL 值會儲存在數據中。

使用函 is_variant_null 式來判斷 variant 值是否為 Variant NULL。

SELECT
  is_variant_null(parse_json(NULL)) AS sql_null,
  is_variant_null(parse_json('null')) AS variant_null,
  is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
  is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null

+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
|   false|        true|              true|                 false|
+--------+------------+------------------+----------------------+

共用方式為