- カテゴリ:
テーブル関数、 半構造化データ関数と構造化データ関数 （抽出）
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_exprVARIANT データ構造内の、フラット化が必要な要素へのパス。一番外側の要素をフラット化する場合は、長さ0の文字列（すなわち空のパス） を指定することもできます。
デフォルト: ゼロ長文字列（空のパス）。
OUTER => TRUE | FALSEFALSEの場合、パスでアクセスできないか、フィールドまたはエントリがゼロであるために展開できない入力行は、出力から完全に省略されます。TRUEの場合、ゼロ行展開用に正確に1行が生成されます（ KEY、 INDEX、 VALUE 列に NULL が含まれる）。
デフォルト：
FALSE注釈
空の複合のゼロ行展開では、 THIS 出力列に NULL が表示され、存在しないまたは間違った種類の複合を展開しようとする試みと区別されます。
RECURSIVE => TRUE | FALSEFALSEの場合、PATHによって参照される要素のみが展開されます。TRUEの場合、展開はすべてのサブ要素に対して再帰的に実行されます。
デフォルト：
FALSEMODE => 'OBJECT' | 'ARRAY' | 'BOTH'オブジェクト、配列、またはその両方のみをフラット化するかどうかを指定します。
デフォルト：
BOTH
出力¶
返される行は、固定された列のセットで構成されます。
+-----+------+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
- SEQ:
入力レコードに関連付けられた一意のシーケンス番号です。シーケンスにギャップがないことや、特定の方法で順序付けられていることは保証されません。
- KEY:
マップまたはオブジェクトの場合、この列には展開された値のキーが含まれます。
- PATH:
フラット化が必要なデータ構造内の要素へのパス。
- INDEX:
配列の場合は要素のインデックスで、それ以外の場合は NULLです。
- VALUE:
フラット化された配列/オブジェクトの要素の値です。
- THIS:
平坦化される要素（再帰的フラット化に有用）です。
注釈
FLATTEN のデータソースとして使用されたオリジナルの（相関関係のある）テーブルの列にもアクセスできます。元のテーブルの単一の行がフラット化されたビューで複数の行になった場合、この入力行の値は FLATTEN によって生成された行の数と一致するように複製されます。
使用上の注意¶
この関数を 構造化タイプ で使用する情報については、 構造化型の値をともなう FLATTEN 関数の使用 をご参照ください。
例¶
（例: FLATTEN テーブル関数でのラテラル結合の使用 および WHERE 句での FLATTEN を使用した結果のフィルター処理 もご参照ください）
次の単純な例は、1つの記録をフラット化します（配列の真ん中の要素がないことに注意してください）。
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 |
| | | | | | ] |
+-----+------+------+-------+-------+------+
次の2つのクエリは、 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 |
| | | | | | ] |
+-----+------+------+-------+-------+-------+
次の2つのクエリは、 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 | [] |
+-----+------+------+-------+-------+------+
次の2つのクエリは、 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" | | |
| | } | | |
| | ] | | |
| | } | | |
+----------+-----------------------------------------+---------+-----------------------+