メインコンテンツまでスキップ

unnest

unnest

説明​

UNNESTは、配列を取り、その配列内の要素を複数の行に変換するテーブル関数です。この変換は「フラット化」とも呼ばれます。

STRING、ARRAY、またはBITMAPから複数の行に変換するために、UNNESTとLateral Joinを組み合わせて共通の変換を実装することができます。詳細については、Lateral joinを参照してください。

v2.5から、UNNESTは可変長の配列パラメータを受け取ることができます。配列はタイプと長さ（要素数）を変えることができます。もし配列の長さが異なる場合、最も長い長さが優先され、この長さ未満の配列にはnullが追加されます。詳細については、Example 2を参照してください。

構文​

unnest(array0[, array1 ...])

パラメータ​

array: 変換したい配列です。これは配列またはARRAYデータ型に評価可能な式である必要があります。1つまたは複数の配列または配列式を指定することができます。

戻り値​

配列から変換された複数の行が返されます。戻り値の型は、配列内の要素の型に依存します。

配列でサポートされている要素の型については、ARRAYを参照してください。

使用上の注意​

  • UNNESTはテーブル関数です。Lateral Joinと一緒に使用する必要がありますが、キーワードのLateral Joinは明示的に指定する必要はありません。
  • 配列式がNULLまたは空の場合、行は返されません。
  • 配列内の要素がNULLの場合、その要素はNULLが返されます。

例​

例1: UNNESTが1つのパラメータを受け取る場合​

-- scoresがARRAY列であるstudent_scoreテーブルを作成します。
CREATE TABLE student_score
(
    `id` bigint(20) NULL COMMENT "",
    `scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`);

-- テーブルにデータを挿入します。
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- テーブルからデータをクエリします。
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- UNNESTを使用してscores列を複数の行にフラット化します。
SELECT id, scores, unnest FROM student_score, unnest(scores);
+------+--------------+--------+
| id   | scores       | unnest |
+------+--------------+--------+
|    1 | [80,85,87]   |     80 |
|    1 | [80,85,87]   |     85 |
|    1 | [80,85,87]   |     87 |
|    2 | [77,null,89] |     77 |
|    2 | [77,null,89] |   NULL |
|    2 | [77,null,89] |     89 |
|    5 | [90,92]      |     90 |
|    5 | [90,92]      |     92 |
+------+--------------+--------+

id = 1に対応する[80,85,87]が3つの行に変換されます。

id = 2に対応する[77,null,89]はnullの値を保持します。

id = 3およびid = 4に対応するscoresはNULLおよび空であり、スキップされます。

例2: UNNESTが複数のパラメータを受け取る場合​

-- typeとscores列のタイプが異なるexample_tableテーブルを作成します。
CREATE TABLE example_table (
id varchar(65533) NULL COMMENT "",
type varchar(65533) NULL COMMENT "",
scores ARRAY<int> NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(id)
COMMENT "OLAP"
DISTRIBUTED BY HASH(id)
PROPERTIES (
"replication_num" = "3");

-- テーブルにデータを挿入します。
INSERT INTO example_table VALUES
("1", "typeA;typeB", [80,85,88]),
("2", "typeA;typeB;typeC", [87,90,95]);

-- テーブルからデータをクエリします。
SELECT * FROM example_table;
+------+-------------------+------------+
| id   | type              | scores     |
+------+-------------------+------------+
| 1    | typeA;typeB       | [80,85,88] |
| 2    | typeA;typeB;typeC | [87,90,95] |
+------+-------------------+------------+

-- UNNESTを使用してtypeとscoresを複数の行に変換します。
SELECT id, unnest.type, unnest.scores
FROM example_table, unnest(split(type, ";"), scores) as unnest(type,scores);
+------+-------+--------+
| id   | type  | scores |
+------+-------+--------+
| 1    | typeA |     80 |
| 1    | typeB |     85 |
| 1    | NULL  |     88 |
| 2    | typeA |     87 |
| 2    | typeB |     90 |
| 2    | typeC |     95 |
+------+-------+--------+

UNNESTのtypeとscoresはタイブと長さが異なります。

typeはVARCHAR列であり、scoresはARRAY列です。split()関数を使用してtypeをARRAYに変換します。

id = 1の場合、typeは["typeA", "typeB"]に変換され、要素は2つです。

id = 2の場合、typeは["typeA", "typeB", "typeC"]に変換され、要素は3つです。

各idごとに行数を一貫させるために、["typeA", "typeB"]にnull要素が追加されます。