- カテゴリ:
テーブル関数、 半構造化データ関数と構造化データ関数 （抽出）
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 | 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 関数の使用 をご参照ください。
例¶
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 | | | | | | | ] | +-----+------+------+-------+-------+------+
PATH パラメーターの効果の例：
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), outer => true)) f; +-----+-----+------+-------+-------+-----------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+-----------| | 1 | a | a | NULL | 1 | { | | | | | | | "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@company.com" } ] } ] }'), (98127771, '{ name: { first: "Jane", last: "Doe"}, contact: [ { business:[ { type: "phone", content:"555-1236" }, { type: "email", content:"j.doe@company.com" } ] } ] }') v; -- Note the multiple instances of LATERAL FLATTEN in the FROM clause of the following query. -- Each LATERAL view is based on the previous one to refer to elements in -- multiple levels of arrays. 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@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 12712555 | { | "email" | "j.smith@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "phone" | "555-1236" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "email" | "j.doe@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | +----------+-----------------------------------------+---------+-----------------------+