查詢變體數據

文章
10/16/2024

重要

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

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

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

使用 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"    |
+----------+-----------+

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

擷取 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  |
| }                |
+------------------+

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

從 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別時，函式會在無法解析的衝突型別存在時回復為VARIANT類型，而不是STRINGschema_of_variant_agg類型。

您可以使用 :: 或 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|
+--------+------------+------------------+----------------------+

分享方式：