ネストされた配列をフラット化する

ネストされた配列を使用する場合、必要に応じて、ネストされた配列の要素を単一の配列に展開したり、配列を複数の行に展開したりすることがあります。

フラット化関数を使用する

ネストされた配列の要素を単一の値配列にフラット化するには、flatten 関数を使用します。次のクエリは、配列の要素ごとに行を返します。

SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items

このクエリは以下を返します。

+-----------+
| items     |
+-----------+
| [1,2,3,4] |
+-----------+

CROSS JOIN と UNNEST を使用する

配列を複数の行にフラット化するには、次の例に示すように、CROSS JOIN 演算子を UNNEST 演算子と組み合わせて使用します。

WITH dataset AS (
  SELECT
    'engineering' as department,
    ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)

このクエリは以下を返します。

+----------------------+
| department  | names  |
+----------------------+
| engineering | Sharon |
+----------------------|
| engineering | John   |
+----------------------|
| engineering | Bob    |
+----------------------|
| engineering | Sally  |
+----------------------+

キーと値のペアの配列をフラット化するには、次の例に示すように、選択したキーを列に入れ替えます。

WITH
dataset AS (
  SELECT
    'engineering' as department,
     ARRAY[
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
     ] AS people
  )
SELECT names['first'] AS
 first_name,
 names['last'] AS last_name,
 department FROM dataset
CROSS JOIN UNNEST(people) AS t(names)

このクエリは以下を返します。

+--------------------------------------+
| first_name | last_name | department  |
+--------------------------------------+
| Bob        | Smith     | engineering |
| Jane       | Doe       | engineering |
| Billy      | Smith     | engineering |
+--------------------------------------+

従業員の一覧から、総合スコアが最も高い従業員を選択します。UNNEST は、デフォルトの結合演算子で暗黙的に指定されるため、FROM 句において先行 CROSS JOIN なしで使用できます。

WITH
dataset AS (
  SELECT ARRAY[
    CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
  ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, person.department, SUM(score) AS total_score FROM users
GROUP BY (person.name, person.department)
ORDER BY (total_score) DESC
LIMIT 1

このクエリは以下を返します。

+---------------------------------+
| name | department | total_score |
+---------------------------------+
| Amy  | devops     | 54          |
+---------------------------------+

従業員の一覧から、個別のスコアが最も高い従業員を選択します。

WITH
dataset AS (
 SELECT ARRAY[
   CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
 ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, score FROM users
ORDER BY (score) DESC
LIMIT 1

このクエリは以下を返します。

+--------------+
| name | score |
+--------------+
| Amy  | 15    |
+--------------+

CROSS JOIN と UNNEST に関する考慮事項

UNNEST がクエリ内の 1 つ以上の配列で使用され、配列の 1 つが NULL である場合、クエリは行を返しません。空の文字列の配列に UNNEST を使用すると、空の文字列が返されます。

例えば、次のクエリでは、2 番目の配列が Null であるため、クエリは行を返しません。

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY []) AS t(col2)

次の例では、2 番目の配列が空の文字列を含むように変更されています。クエリは各行について、col1 の値を返し、col2 の値では空の文字列を返します。1 番目の配列の値を返すには、2 番目の配列の空の文字列が必要です。

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY ['']) AS t(col2)