Meratakan array bersarang

Saat bekerja dengan larik nest, Anda sering perlu memperluas elemen larik nest ke dalam satu larik, atau memperluas larik menjadi beberapa baris.

Contoh

Untuk meratakan elemen larik nest ini ke dalam satu larik nilai, gunakanflattenfungsi. Kueri ini mengembalikan baris untuk setiap elemen dalam larik.

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

Kueri ini kembali:

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

Untuk meratakan larik menjadi beberapa baris, gunakanCROSS JOINdalam hubungannya denganUNNESToperator, seperti dalam contoh ini:

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)

Kueri ini kembali:

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

Untuk meratakan larik pasangan kunci-nilai, transpose kunci yang dipilih ke dalam kolom, seperti dalam contoh ini:

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)

Kueri ini kembali:

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

Dari daftar karyawan, pilih karyawan dengan skor gabungan tertinggi.UNNESTdapat digunakan dalamFROMklausul tanpa sebelumnyaCROSS JOINkarena merupakan default bergabung operator dan karena itu tersirat.

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

Kueri ini kembali:

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

Dari daftar karyawan, pilih karyawan dengan skor individu tertinggi.

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

Kueri ini menghasilkan:

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

Pertimbangan dan batasan

Jika UNNEST digunakan pada satu atau lebih array dalam query, dan salah satu array adalahNULL, query tidak mengembalikan baris. Jika UNNEST digunakan pada array yang merupakan string kosong, string kosong dikembalikan.

Misalnya, dalam query berikut, karena array kedua adalah null, query tidak mengembalikan baris.

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

Dalam contoh berikutnya, array kedua dimodifikasi untuk berisi string kosong. Untuk setiap baris, query mengembalikan nilai dalam col1 dan string kosong untuk nilai dalamcol2. String kosong dalam array kedua diperlukan agar nilai-nilai dalam array pertama dikembalikan.

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