查询半结构化数据¶

本主题介绍如何使用特殊运算符和函数来查询存储在 VARIANT 中的复杂分层数据。

（有关如何从 ARRAYs 和 OBJECTs 中提取值的简单示例，请参阅通过索引或切片访问 ARRAY 的元素和通过键访问 OBJECT 的元素。）

通常，分层数据已从以下支持的数据格式之一导入到 VARIANT：

JSON

Avro

ORC

Parquet

（有关查询 XML 数据 [例如，源于 XML 数据格式并通过调用 PARSE_XML 转换为 OBJECT 的数据] 的信息，请参阅 XMLGET。）

小技巧

您可以使用搜索优化服务来提高查询性能。有关详细信息，请参阅搜索优化服务。

本主题内容：

示例中使用的示例数据¶

除非另有说明，否则本主题中的示例引用一个名为 car_sales 的表，该表包含一个名为 src 的 VARIANT 列。该 VARIANT 包含嵌套的 ARRAYs 和 OBJECTs。

创建表并加载：

CREATE OR REPLACE TABLE car_sales
( 
  src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{ 
    "date" : "2017-04-28", 
    "dealership" : "Valley View Auto Sales",
    "salesperson" : {
      "id": "55",
      "name": "Frank Beasley"
    },
    "customer" : [
      {"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
    ],
    "vehicle" : [
      {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
    ]
}'),
('{ 
    "date" : "2017-04-28", 
    "dealership" : "Tindel Toyota",
    "salesperson" : {
      "id": "274",
      "name": "Greg Northrup"
    },
    "customer" : [
      {"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
    ],
    "vehicle" : [
      {"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}  
    ]
}') v;

Copy

选择数据：

SELECT * FROM car_sales;
+-------------------------------------------+
| SRC                                       |
|-------------------------------------------|
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "San Francisco, CA",     |
|       "name": "Joyce Ridgely",            |
|       "phone": "16504378889"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Valley View Auto Sales", |
|   "salesperson": {                        |
|     "id": "55",                           |
|     "name": "Frank Beasley"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "paint protection"                |
|       ],                                  |
|       "make": "Honda",                    |
|       "model": "Civic",                   |
|       "price": "20275",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "New York, NY",          |
|       "name": "Bradley Greenbloom",       |
|       "phone": "12127593751"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Tindel Toyota",          |
|   "salesperson": {                        |
|     "id": "274",                          |
|     "name": "Greg Northrup"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "rust proofing",                  |
|         "fabric protection"               |
|       ],                                  |
|       "make": "Toyota",                   |
|       "model": "Camry",                   |
|       "price": "23500",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
+-------------------------------------------+

Copy

遍历半结构化数据¶

在 VARIANT 列名和任何一级元素之间插入冒号 :：<column>:<level1_element>。

备注

在以下示例中，查询输出用双引号括起来，因为查询输出是 VARIANT，而不是 VARCHAR。（ VARIANT 值不是字符串； VARIANT 值包含字符串。运算符 : 及其后的 . 和 [] 始终返回包含字符串的 VARIANT 值。

例如，获取所有经销商名称的列表：

SELECT src:dealership
    FROM car_sales
    ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP           |
|--------------------------|
| "Tindel Toyota"          |
| "Valley View Auto Sales" |
+--------------------------+

Copy

有两种方法可以访问对象中的 JSON 元素：

点表示法（本主题内容）。
括号表示法（本主题内容）。

重要

无论使用哪种表示法，列名不区分大小写，但元素名称区分大小写。例如，在以下列表中，前两个路径是等效的，但第三个不是：

src:salesperson.name
SRC:salesperson.name
SRC:Salesperson.Name

点表示法¶

使用点表示法遍历对象中的 JSON 路径： <column>:<level1_element>.<level2_element>.<level3_element>。（可选）将元素名称括在双引号中： <column>:"<level1_element>"."<level2_element>"."<level3_element>"。

备注

JSON 键（元素名称）的规则与 Snowflake SQL 标识符的规则不同。

有关 Snowflake SQL 标识符规则的详细信息，请参阅：标识符要求。

有关 JSON 键的详细信息，请参阅 http://json.org (http://json.org)，特别是“字符串”的描述。

如果元素名称不符合 Snowflake SQL 标识符规则（例如，如果它包含空格），则必须将名称括在双引号中。下面是一些有效 JSON 元素名称的示例（并非所有示例都来自上面 car_sales 示例），除非它们被双引号括起来，否则这些元素名称不是有效的 Snowflake 标识符名称：

-- This contains a blank.
SELECT src:"company name" FROM partners;

-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;

-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;

Copy

获取所有销售汽车的销售人员的姓名：

SELECT src:salesperson.name
    FROM car_sales
    ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley"      |
| "Greg Northrup"      |
+----------------------+

Copy

括号表示法¶

或者，使用括号表示法遍历对象中的路径： <column>['<level1_element>']['<level2_element>']。将元素名称括在单引号中。值以字符串形式检索。

获取所有销售汽车的销售人员的姓名：

SELECT src['salesperson']['name']
    FROM car_sales
    ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley"            |
| "Greg Northrup"            |
+----------------------------+

Copy

检索重复元素的单个实例¶

通过在数组引用中添加编号谓词（从 0 开始）来检索重复数组中子元素的特定编号实例。

请注意，要检索重复数组中子元素的所有实例，必须对数组进行扁平化处理。请参阅本主题中使用 FLATTEN 函数解析数组的示例。

获取每笔销售的车辆详细信息：

SELECT src:customer[0].name, src:vehicle[0]
    FROM car_sales
    ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0]          |
|----------------------+-------------------------|
| "Bradley Greenbloom" | {                       |
|                      |   "extras": [           |
|                      |     "ext warranty",     |
|                      |     "rust proofing",    |
|                      |     "fabric protection" |
|                      |   ],                    |
|                      |   "make": "Toyota",     |
|                      |   "model": "Camry",     |
|                      |   "price": "23500",     |
|                      |   "year": "2017"        |
|                      | }                       |
| "Joyce Ridgely"      | {                       |
|                      |   "extras": [           |
|                      |     "ext warranty",     |
|                      |     "paint protection"  |
|                      |   ],                    |
|                      |   "make": "Honda",      |
|                      |   "model": "Civic",     |
|                      |   "price": "20275",     |
|                      |   "year": "2017"        |
|                      | }                       |
+----------------------+-------------------------+

Copy

获取每辆售出的汽车的价格：

SELECT src:customer[0].name, src:vehicle[0].price
    FROM car_sales
    ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500"              |
| "Joyce Ridgely"      | "20275"              |
+----------------------+----------------------+

Copy

显式类型转换值¶

从 VARIANT 中提取值时，可以将值显式转换为所需的数据类型。例如，您可以将价格提取为数值并对其执行计算：

SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
    FROM car_sales
    ORDER BY tax;
+--------+
|    TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+

Copy

默认情况下，从 VARIANT 列检索 VARCHARs、DATEs、TIMEs 和 TIMESTAMPs 时，这些值都会用双引号引起来。您可以通过显式强制转换值来消除双引号。例如：

SELECT src:dealership, src:dealership::VARCHAR
    FROM car_sales
    ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP           | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota"          | Tindel Toyota           |
| "Valley View Auto Sales" | Valley View Auto Sales  |
+--------------------------+-------------------------+

Copy

有关强制 VARIANT 类型转换值的详细信息，请参阅使用 VARIANT 中的值。

有关类型转换的常规详细信息，请参阅数据类型转换。

使用 FLATTEN 筛选 WHERE 子句中的结果¶

FLATTEN 函数将嵌套值分解为单独的列。您可以使用该函数筛选 WHERE 子句中的查询结果。

以下示例返回与 WHERE 子句匹配的键值对，并将它们显示在单独的列中：

CREATE TABLE pets (v variant);

INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');

SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';

+-------------------------+---------+--------------+
| V                       | KEY     | VALUE        |
|-------------------------+---------+--------------|
| {                       | species | "dog"        |
|   "is_dog": "true",     |         |              |
|   "name": "Fido",       |         |              |
|   "species": "dog"      |         |              |
| }                       |         |              |
| {                       | name    | "dog terror" |
|   "is_dog": "false",    |         |              |
|   "name": "dog terror", |         |              |
|   "species": "cat"      |         |              |
| }                       |         |              |
+-------------------------+---------+--------------+

Copy

使用 FLATTEN 列出不同的键名¶

当使用不熟悉的半结构化数据时，您可能不知道 OBJECT 中的键名。您可以将 FLATTEN 函数与 RECURSIVE 实参搭配使用，以返回 OBJECT 中所有嵌套元素中不同键名的列表：

SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
  TYPEOF(f.value) AS "Type",
  COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;

Copy

REGEXP_REPLACE 函数删除数组索引值（例如 [0]），并用括号 ([]) 替换它们，以对数组元素进行分组。

例如：

{"a": 1, "b": 2, "special" : "data"}   <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"}    <----row 2 of VARIANT column

Output from query:

+---------+---------+-------+
| Path    | Type    | Count |
|---------+---------+-------|
| a       | INTEGER |     1 |
| b       | INTEGER |     1 |
| c       | INTEGER |     1 |
| d       | INTEGER |     1 |
| normal  | VARCHAR |     1 |
| special | VARCHAR |     1 |
+---------+---------+-------+

Copy

使用 FLATTEN 列出 OBJECT 中的路径¶

有关使用 FLATTEN 列出不同的键名，您可以使用带有 RECURSIVE 实参的 FLATTEN 函数来检索 OBJECT 中的所有键和路径。

以下查询返回 VARIANT 列中存储的所有数据类型的键、路径和值（包括 VARIANT “null”值）。该代码假定 VARIANT 列在每一行中都包含 OBJECT。

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.index,
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;

Copy

以下查询与第一个查询类似，但不包括嵌套 OBJECTs 和 ARRAYs：

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');

Copy

查询返回以下值：

<variant_column>
OBJECT 存储为 VARIANT 列中的一行。

序列
与行中的数据关联的唯一序列号。

键
与数据结构中的值关联的字符串。

路径
数据结构中元素的路径。

级别
数据结构中键值对的级别。

类型
值的数据类型。

索引
数据结构中元素的索引。仅适用于 ARRAY 值；否则为 NULL。

当前级别的值
数据结构中当前级别的值。

高于级别的值
数据结构中高一级的值。

使用 FLATTEN 函数解析数组¶

使用 FLATTEN 函数解析数组。FLATTEN 是一个表函数，用于生成 VARIANT、 OBJECT 或者 ARRAY 列的横向视图。该函数为每个对象返回一行， LATERAL 修饰符将数据与对象外部的任何信息联接起来。

获取所有客户的姓名和地址。将 VARIANT 输出转换为字符串值：

SELECT
  value:name::string as "Customer Name",
  value:address::string as "Address"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:customer);

+--------------------+-------------------+
| Customer Name      | Address           |
|--------------------+-------------------|
| Joyce Ridgely      | San Francisco, CA |
| Bradley Greenbloom | New York, NY      |
+--------------------+-------------------+

Copy

使用 FLATTEN 函数解析嵌套数组¶

extras 数组嵌套在示例数据的 vehicle 数组中：

"vehicle" : [
     {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
   ]

Copy

添加第二个 FLATTEN 子句，以在扁平化的 vehicle 数组中扁平化 extras 数组，并检索为每辆售出的汽车所购买的“附加内容”：

SELECT
  vm.value:make::string as make,
  vm.value:model::string as model,
  ve.value::string as "Extras Purchased"
  FROM
    car_sales
    , LATERAL FLATTEN(INPUT => SRC:vehicle) vm
    , LATERAL FLATTEN(INPUT => vm.value:extras) ve
  ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE   | MODEL | Extras Purchased  |
|--------+-------+-------------------|
| Honda  | Civic | ext warranty      |
| Honda  | Civic | paint protection  |
| Toyota | Camry | ext warranty      |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing     |
+--------+-------+-------------------+

Copy

使用 PARSE_JSON 函数将文本解析为 VARIANT 值¶

使用 PARSE_JSON 函数将文本解析为 JSON 文档。

如果输入为 NULL，则输出也将是 NULL。但是，如果输入字符串是 null，则将其解释为 VARIANT null 值；也就是说，结果不是 SQL NULL，而是用于表示半结构化格式中的 null 值的实值。

有关示例，请参阅本主题中的示例中使用的示例数据。

使用 GET 函数提取值¶

GET 接受 VARIANT、 OBJECT 或 ARRAY 值作为第一个实参，并提取作为第二个实参提供的路径中元素的 VARIANT 值。

使用 GET 和 ARRAY_SIZE 函数计算并提取 VARIANT 列中每个数组的最后一个元素。ARRAY_SIZE 返回输入数组的大小：

备注

此示例与本主题中其他位置使用的 car_sales 表不同。

CREATE OR replace TABLE colors (v variant);

INSERT INTO
   colors
   SELECT
      parse_json(column1) AS v
   FROM
   VALUES
     ('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
     ('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
    v;

SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;

+---------------+-------------------------+
| V             | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [             | {                       |
|   {           |   "b": 255,             |
|     "b": 0,   |   "g": 0,               |
|     "g": 12,  |   "r": 0                |
|     "r": 255  | }                       |
|   },          |                         |
|   {           |                         |
|     "b": 0,   |                         |
|     "g": 255, |                         |
|     "r": 0    |                         |
|   },          |                         |
|   {           |                         |
|     "b": 255, |                         |
|     "g": 0,   |                         |
|     "r": 0    |                         |
|   }           |                         |
| ]             |                         |
| [             | {                       |
|   {           |   "c": 1,               |
|     "c": 0,   |   "k": 0,               |
|     "k": 0,   |   "m": 1,               |
|     "m": 1,   |   "y": 0                |
|     "y": 1    | }                       |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 0,   |                         |
|     "y": 1    |                         |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 1,   |                         |
|     "y": 0    |                         |
|   }           |                         |
| ]             |                         |
+---------------+-------------------------+

Copy

使用 GET_PATH 函数按路径提取值¶

使用 GET_PATH、: 函数从 VARIANT 列中提取值。该函数是 GET 的变体，用于使用路径名提取值。GET_PATH 等价于 GET 函数链。

获取每个客户购买的汽车的车辆品牌：

SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;

+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda"                          |
| "Toyota"                         |
+----------------------------------+

Copy

遍历半结构化数据描述用于检索 VARIANT 列中元素的路径语法。语法是 GET_PATH、: 或 GET 函数的简写。与路径语法不同，这些函数可以处理不规则路径或路径元素。

以下查询将产生相同的结果：

SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;

SELECT src:vehicle[0].make FROM car_sales;

Copy

直接从暂存数据文件解析数组¶

假设名为 contacts.json.gz 的暂存文件包含以下数据：

{
    "root": [
        {
            "employees": [
                {
                    "firstName": "Anna",
                    "lastName": "Smith"
                },
                {
                    "firstName": "Peter",
                    "lastName": "Jones"
                }
            ]
        }
    ]
}

Copy

此外，还假定名为 my_json_format 的文件格式在其定义中包含 TYPE=JSON。

查询暂存文件中第一个员工的姓名。在此示例中，文件位于 customers 表暂存区，但它可以位于任何内部（即 Snowflake）或外部暂存区：

SELECT 'The First Employee Record is '||
    S.$1:root[0].employees[0].firstName||
    ' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;

+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '||            |
|      S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME||   |
|      ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith      |
+----------------------------------------------+

Copy

使用 Snowflake 高阶函数过滤和转换数据¶

Snowflake 高阶函数使您能够使用匿名函数来过滤和转换半结构化和结构化数据。调用 Snowflake 高阶函数时，可以使用 Lambda 表达式来创建对数据进行操作的匿名函数，该函数通过数组指定。Snowflake 高阶函数提供了一种简洁、可读、高效的方法来执行数据操作和高级分析。

可以使用以下高阶函数：

高阶函数的好处¶

在数据分析中使用半结构化数据时，通常需要循环遍历数组并对数组中的每个值执行操作。您可以通过调用 Snowflake 高阶函数来执行这些操作。这些高阶函数具有以下优点：

简化高级分析 – 通过简化对数组元素的迭代，这些函数有助于实现数据过滤和转换的自定义逻辑，从而简化分析过程。如果没有高阶函数，这种类型的操作需要 LATERAL FLATTEN 操作或用户定义函数 (UDFs)。
增强开发者体验 - 高阶函数将操作逻辑封装在 Lambda 表达式中，从而使 SQL 语句更加可读和便于维护。通过使用高阶函数，可以避免编写冗长而复杂的 SQL 查詢。
避免不必要的 UDFs - 使用高阶函数，减少为临时数组操作逻辑创建、维护和管理对 UDFs 的访问的需要。这些功能可以减少开销并简化数据操作过程。

Lambda 表达式¶

Lambda 表达式是一小段代码块，它接受一个实参并返回一个值。在 Lambda 表达式中，您可以在 Lambda 运算符左侧 (->) 指定实参，在右侧指定表达式。您可以使用 Lambda 表达式来完成各种各样的操作。

例如，您可以使用 Lambda 表达式来生成数字输出。以下 Lambda 表达式将元素乘以 2：

a -> a * 2

Copy

您可以使用 Lambda 表达式来过滤元素，并返回那些过滤条件返回 TRUE 的元素。例如，以下 Lambda 表达式返回 value 大于 50 的元素：

a -> a:value > 50

Copy

您可以使用 Lambda 表达式向元素添加文本。例如，以下 Lambda 表达式可向元素添加文本 some string：

a -> a || ' some string'

Copy

您可以指定 Lambda 实参的数据类型。例如，以下 Lambda 表达式指定并添加两个 INTEGER 值：

(x INT, y INT) -> (x + y)

Copy

限制¶

Lambda 表达式不支持作为独立对象。它们必须作为实参指定给 FILTER 和 TRANSFORM 功能。
Lambda 表达式必须是匿名的。命名函数不能作为 Lambda 实参传递给 FILTER 和 TRANSFORM 函数。
Lambda 表达式仅接受内置函数、SQL 用户定义函数和标量子查询。它们不支持引用嵌套上下文（例如 Snowflake Scripting 变量），CTE 表达式、当前或嵌套查询块中的列引用、或者用户定义函数中的实参。