查询变体数据

项目
10/15/2024

重要

此功能目前以公共预览版提供。

本文介绍 Databricks SQL 运算符，这些运算符可用于查询和转换以 VARIANT 形式存储的半结构化数据。 Databricks Runtime 15.3 和更高版本中提供了 VARIANT 数据类型。

Databricks 建议对 JSON 字符串使用 VARIANT。对于当前使用 JSON 字符串且想要迁移的用户，请参阅变体与 JSON 字符串有何不同？。

如果你想查看查询使用 JSON 字符串存储的半结构化数据的示例，请参阅查询 JSON 字符串。

创建一个包含变体列的表

运行以下查询以创建一个表，其中包含以 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

查询变体列中的字段

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

提取变体嵌套字段

可以通过点表示法或使用方括号指定嵌套字段。字段名称始终区分大小写。

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

从变体数组中提取值

可以使用方括号对数组中的元素进行索引。索引从 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_get 和 try_variant_get 采用类型参数并遵循这些强制转换规则。

源类型	行为
`VOID`	结果是类型 `VARIANT` 为 `NULL`。
`ARRAY<elementType>`	`elementType` 必须是可强制转换为 `VARIANT` 的类型。

使用 schema_of_variant 或 schema_of_variant_agg 推断类型时，当存在无法解决的相互冲突类型时，函数会回退到 VARIANT 类型，而不是 STRING 类型。

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

变体 null 规则

变体可以包含两种类型的 null：

SQL NULL：SQL NULL 表示值缺失。这些是与处理结构化数据时相同的 NULL。
变体 NULL：变体 NULL 表示变体显式包含 NULL 值。这些与 SQL NULL 不同，因为 NULL 值存储在数据中。

使用 is_variant_null 函数确定变体值是否为变体 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|
+--------+------------+------------------+----------------------+

通过