- 类别:
表函数、半结构化和结构化数据函数 (提取)
FLATTEN¶
将复合值展平(分解)成多行。
FLATTEN 是一个表函数,它采用 VARIANT、OBJECT 或 ARRAY 列并生成横向视图(即内联视图,其中包含与 FROM 子句中位于其之前的其他表的关联)。
FLATTEN 可用于将半结构化数据转换为关系表示形式。
语法¶
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
[ , OUTER => TRUE | FALSE ]
[ , RECURSIVE => TRUE | FALSE ]
[ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
实参¶
必填:
INPUT => expr
将要展平为行的表达式。表达式的数据类型必须为 VARIANT、OBJECT 或 ARRAY。
可选:
PATH => constant_expr
需要展平的 VARIANT 数据结构中的元素所在的路径。如果要展平最外层的元素,则可以是零长度字符串(即空路径)。
默认:零长度字符串(即空路径)
OUTER => TRUE | FALSE
如果是
FALSE
,则在输出中会完全忽略任何无法展开的输入行,因为这些行无法在路径中访问,或者具有零个字段或条目。如果是
TRUE
,则为零行展开只生成一行(KEY、INDEX 和 VALUE 列中为 NULL)。
默认:
FALSE
备注
空复合值的零行展开会在 THIS 输出列中显示 NULL,从而与尝试展开不存在或错误类型的复合值区分开来。
RECURSIVE => TRUE | FALSE
如果是
FALSE
,则仅展开PATH
引用的元素。如果是
TRUE
,则以递归方式对所有子元素执行展开。
默认:
FALSE
MODE => 'OBJECT' | 'ARRAY' | 'BOTH'
指定是否只应展平对象和/或数组。
默认:
BOTH
输出¶
返回的行由一组固定的列组成:
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
- SEQ:
与输入记录相关的唯一序列号;不能保证序列没有间隙或以任何特定方式排序。
- KEY:
对于映射或对象,此列包含分解值的键。
- PATH:
需要展平的数据结构中的元素所在的路径。
- INDEX:
元素的索引(如果它是数组),否则为 NULL。
- VALUE:
扁平化数组/对象的元素值。
- THIS:
要展平的元素(在递归展平中很有用)。
备注
用作 FLATTEN 的数据源的原始(相关)表的列也可供访问。如果原始表中的一行导致展平视图中有多行,则复制该输入行中的值以匹配 FLATTEN 生成的行数。
使用说明¶
有关将此函数与 结构化类型 一起使用的信息,请参阅 将 FLATTEN 函数用于结构化类型的值。
示例¶
另请参阅 示例:使用带 FLATTEN 表函数的横向联接 和 使用 FLATTEN 筛选 WHERE 子句中的结果。
展平一条记录的简单示例(请注意,缺少数组的中间元素)如下:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1, ,77]'))) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | [0] | 0 | 1 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
| 1 | NULL | [2] | 2 | 77 | [ |
| | | | | | 1, |
| | | | | | , |
| | | | | | 77 |
| | | | | | ] |
+-----+------+------+-------+-------+------+
下面两个查询显示了 PATH 参数的效果:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), OUTER => TRUE)) f;
+-----+-----+------+-------+-------+-----------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+-----------|
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ] |
| | | | | | } |
+-----+-----+------+-------+-------+-----------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), PATH => 'b')) f;
+-----+------+------+-------+-------+-------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+-------|
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
+-----+------+------+-------+-------+-------+
下面两个查询显示了 OUTER 参数的效果:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'))) f;
+-----+-----+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+------|
+-----+-----+------+-------+-------+------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), OUTER => TRUE)) f;
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
| 1 | NULL | | NULL | NULL | [] |
+-----+------+------+-------+-------+------+
下面两个查询显示了 RECURSIVE 参数的效果:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE )) f;
+-----+------+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | NULL | b[0] | 0 | 77 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | NULL | b[1] | 1 | 88 | [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ] |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+------+------+-------+------------+--------------+
以下示例显示了 MODE 参数的效果:
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
RECURSIVE => TRUE, MODE => 'OBJECT' )) f;
+-----+-----+------+-------+------------+--------------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+------------+--------------|
| 1 | a | a | NULL | 1 | { |
| | | | | | "a": 1, |
| | | | | | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | b | b | NULL | [ | { |
| | | | | 77, | "a": 1, |
| | | | | 88 | "b": [ |
| | | | | ] | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | c | c | NULL | { | { |
| | | | | "d": "X" | "a": 1, |
| | | | | } | "b": [ |
| | | | | | 77, |
| | | | | | 88 |
| | | | | | ], |
| | | | | | "c": { |
| | | | | | "d": "X" |
| | | | | | } |
| | | | | | } |
| 1 | d | c.d | NULL | "X" | { |
| | | | | | "d": "X" |
| | | | | | } |
+-----+-----+------+-------+------------+--------------+
以下示例将分解嵌套在另一数组中的数组。创建下表:
CREATE OR REPLACE TABLE persons AS
SELECT column1 AS id, PARSE_JSON(column2) as c
FROM values
(12712555,
'{ name: { first: "John", last: "Smith"},
contact: [
{ business:[
{ type: "phone", content:"555-1234" },
{ type: "email", content:"j.smith@example.com" } ] } ] }'),
(98127771,
'{ name: { first: "Jane", last: "Doe"},
contact: [
{ business:[
{ type: "phone", content:"555-1236" },
{ type: "email", content:"j.doe@example.com" } ] } ] }') v;
请注意,以下查询的 FROM 子句中的多个 LATERAL FLATTEN 实例。每个 LATERAL 视图都基于前一个视图来引用多级数组中的元素。
SELECT id as "ID",
f.value AS "Contact",
f1.value:type AS "Type",
f1.value:content AS "Details"
FROM persons p,
LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
LATERAL FLATTEN(INPUT => f.value:business) f1;
+----------+-----------------------------------------+---------+-----------------------+
| ID | Contact | Type | Details |
|----------+-----------------------------------------+---------+-----------------------|
| 12712555 | { | "phone" | "555-1234" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 12712555 | { | "email" | "j.smith@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1234", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.smith@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "phone" | "555-1236" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
| 98127771 | { | "email" | "j.doe@example.com" |
| | "business": [ | | |
| | { | | |
| | "content": "555-1236", | | |
| | "type": "phone" | | |
| | }, | | |
| | { | | |
| | "content": "j.doe@example.com", | | |
| | "type": "email" | | |
| | } | | |
| | ] | | |
| | } | | |
+----------+-----------------------------------------+---------+-----------------------+