Halaman ini diterjemahkan oleh Cloud Translation API.

Menggunakan array

Dalam GoogleSQL untuk BigQuery, array adalah daftar berurutan yang terdiri dari nol atau beberapa nilai dari jenis data yang sama. Anda dapat membuat array jenis data sederhana, seperti INT64, dan jenis data kompleks, seperti STRUCT. Pengecualian saat ini untuk jenis data ini adalah ARRAY karena array dari array tidak didukung. Untuk mempelajari jenis data ARRAY lebih lanjut, termasuk penanganan NULL, baca Jenis array.

Dengan GoogleSQL, Anda dapat membuat literal array, membangun array dari subkueri menggunakan fungsi ARRAY, dan menggabungkan nilai ke dalam array menggunakan fungsi ARRAY_AGG.

Anda dapat menggabungkan array menggunakan fungsi seperti ARRAY_CONCAT(), dan mengonversi array menjadi string menggunakan ARRAY_TO_STRING().

Mengakses elemen array

Pertimbangkan tabel yang diemulasikan berikut yang disebut Sequences. Tabel ini berisi kolom some_numbers dari jenis data ARRAY.

WITH
  Sequences AS (
    SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL
    SELECT [2, 4, 8, 16, 32] UNION ALL
    SELECT [5, 10]
  )
SELECT * FROM Sequences

/*---------------------*
 | some_numbers        |
 +---------------------+
 | [0, 1, 1, 2, 3, 5]  |
 | [2, 4, 8, 16, 32]   |
 | [5, 10]             |
 *---------------------*/

Untuk mengakses elemen array di kolom some_numbers, tentukan jenis pengindeksan yang ingin Anda gunakan: index atau OFFSET(index) untuk indeks berbasis nol, atau ORDINAL(index) untuk indeks berbasis satu.

Contoh:

SELECT
  some_numbers,
  some_numbers[0] AS index_0,
  some_numbers[OFFSET(1)] AS offset_1,
  some_numbers[ORDINAL(1)] AS ordinal_1
FROM Sequences

/*--------------------+---------+----------+-----------*
 | some_numbers       | index_0 | offset_1 | ordinal_1 |
 +--------------------+---------+----------+-----------+
 | [0, 1, 1, 2, 3, 5] | 0       | 1        | 0         |
 | [2, 4, 8, 16, 32]  | 2       | 4        | 2         |
 | [5, 10]            | 5       | 10       | 5         |
 *--------------------+---------+----------+-----------*/

Mencari panjang

Fungsi ARRAY_LENGTH menampilkan panjang array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
       ARRAY_LENGTH(some_numbers) AS len
FROM Sequences;

/*--------------------+--------*
 | some_numbers       | len    |
 +--------------------+--------+
 | [0, 1, 1, 2, 3, 5] | 6      |
 | [2, 4, 8, 16, 32]  | 5      |
 | [5, 10]            | 2      |
 *--------------------+--------*/

Mengonversi elemen dalam array menjadi baris dalam tabel

Untuk mengonversi ARRAY menjadi serangkaian baris, yang juga dikenal sebagai "perataan", gunakan operator UNNEST. UNNEST menggunakan ARRAY dan menampilkan tabel dengan satu baris untuk setiap elemen dalam ARRAY.

Karena UNNEST menghancurkan urutan elemen ARRAY, Anda mungkin ingin memulihkan urutan ke tabel. Untuk melakukannya, gunakan klausa WITH OFFSET opsional untuk menampilkan kolom tambahan dengan offset untuk setiap elemen array, lalu gunakan klausa ORDER BY untuk mengurutkan baris berdasarkan offsetnya.

Contoh

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

Untuk meratakan seluruh kolom ARRAY sekaligus mempertahankan nilai kolom lain di setiap baris, gunakan INNER JOIN yang dikorelasikan untuk menggabungkan tabel yang berisi kolom ARRAY ke output UNNEST dari kolom ARRAY tersebut.

Dengan penggabungan yang berkorelasi, operator UNNEST mereferensikan kolom berjenis ARRAY dari setiap baris dalam tabel sumber, yang muncul sebelumnya dalam klausa FROM. Untuk setiap baris N dalam tabel sumber, UNNEST meratakan ARRAY dari baris N menjadi kumpulan baris yang berisi elemen ARRAY, lalu INNER JOIN atau CROSS JOIN yang berkorelasi menggabungkan kumpulan baris baru ini dengan satu baris N dari tabel sumber.

Contoh

Contoh berikut menggunakan UNNEST untuk menampilkan baris bagi setiap elemen di kolom array. Karena INNER JOIN, kolom id berisi nilai id untuk baris di Sequences yang berisi setiap angka.

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id, flattened_numbers
FROM Sequences
INNER JOIN UNNEST(Sequences.some_numbers) AS flattened_numbers;

/*------+-------------------*
 | id   | flattened_numbers |
 +------+-------------------+
 |    1 |                 0 |
 |    1 |                 1 |
 |    1 |                 1 |
 |    1 |                 2 |
 |    1 |                 3 |
 |    1 |                 5 |
 |    2 |                 2 |
 |    2 |                 4 |
 |    2 |                 8 |
 |    2 |                16 |
 |    2 |                32 |
 |    3 |                 5 |
 |    3 |                10 |
 *------+-------------------*/

Perhatikan bahwa untuk join yang berkorelasi, operator UNNEST bersifat opsional dan INNER JOIN dapat dinyatakan sebagai CROSS JOIN atau cross join koma. Dengan menggunakan notasi singkat cross join koma, contoh sebelumnya digabungkan sebagai berikut:

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id, flattened_numbers
FROM Sequences, Sequences.some_numbers AS flattened_numbers;

/*------+-------------------*
 | id   | flattened_numbers |
 +------+-------------------+
 |    1 |                 0 |
 |    1 |                 1 |
 |    1 |                 1 |
 |    1 |                 2 |
 |    1 |                 3 |
 |    1 |                 5 |
 |    2 |                 2 |
 |    2 |                 4 |
 |    2 |                 8 |
 |    2 |                16 |
 |    2 |                32 |
 |    3 |                 5 |
 |    3 |                10 |
 *------+-------------------*/

Mengkueri array bertingkat

Jika tabel berisi ARRAY dari STRUCT, Anda dapat meratakan ARRAY untuk mengkueri kolom STRUCT. Anda juga dapat meratakan kolom jenis ARRAY dari nilai STRUCT.

Mengkueri elemen `STRUCT` dalam array

Contoh berikut menggunakan UNNEST dengan INNER JOIN untuk meratakan ARRAY dari STRUCT.

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
    )
SELECT
  race,
  participant
FROM Races AS r
INNER JOIN UNNEST(r.participants) AS participant;

/*------+---------------------------------------*
 | race | participant                           |
 +------+---------------------------------------+
 | 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]}   |
 | 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
 | 800M | {Murphy, [23.9, 26, 27, 26]}          |
 | 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]}     |
 | 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]}    |
 | 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
 | 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]}  |
 | 800M | {Berian, [23.7, 26.1, 27, 29.3]}      |
 *------+---------------------------------------*/

Anda dapat menemukan informasi spesifik dari kolom berulang. Misalnya, kueri berikut menampilkan pembalap tercepat dalam balapan 800M.

Contoh

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants)
    ORDER BY (SELECT SUM(duration) FROM UNNEST(laps) AS duration) ASC
    LIMIT 1
  ) AS fastest_racer
FROM Races;

/*------+---------------*
 | race | fastest_racer |
 +------+---------------+
 | 800M | Rudisha       |
 *------+---------------*/

Mengkueri kolom jenis `ARRAY` dalam sebuah struct

Anda juga bisa mendapatkan informasi dari kolom berulang bertingkat. Misalnya, pernyataan berikut menampilkan pelari yang memiliki putaran tercepat dalam balapan 800M.

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ]AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants), UNNEST(laps) AS duration
    ORDER BY duration ASC
    LIMIT 1
  ) AS runner_with_fastest_lap
FROM Races;

/*------+-------------------------*
 | race | runner_with_fastest_lap |
 +------+-------------------------+
 | 800M | Kipketer                |
 *------+-------------------------*/

Perhatikan bahwa kueri sebelumnya menggunakan operator koma (,) untuk melakukan join lintas dan meratakan array. Ini setara dengan menggunakan CROSS JOIN eksplisit, atau contoh berikut yang menggunakan INNER JOIN eksplisit:

WITH
  Races AS (
    SELECT "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps)
      ] AS participants
  )
SELECT
  race,
  (
    SELECT name
    FROM UNNEST(participants)
    INNER JOIN UNNEST(laps) AS duration
    ORDER BY duration ASC LIMIT 1
  ) AS runner_with_fastest_lap
FROM Races;

/*------+-------------------------*
 | race | runner_with_fastest_lap |
 +------+-------------------------+
 | 800M | Kipketer                |
 *------+-------------------------*/

Meratakan array dengan INNER JOIN mengecualikan baris yang memiliki array kosong atau NULL. Jika Anda ingin menyertakan baris ini, gunakan LEFT JOIN.

WITH
  Races AS (
    SELECT
      "800M" AS race,
      [
        STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps),
        STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps),
        STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps),
        STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps),
        STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps),
        STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps),
        STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps),
        STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps),
        STRUCT("Nathan" AS name, ARRAY<FLOAT64>[] AS laps),
        STRUCT("David" AS name, NULL AS laps)
      ] AS participants
  )
SELECT
  Participant.name,
  SUM(duration) AS finish_time
FROM Races
INNER JOIN Races.participants AS Participant
LEFT JOIN Participant.laps AS duration
GROUP BY name;

/*-------------+--------------------*
 | name        | finish_time        |
 +-------------+--------------------+
 | Murphy      | 102.9              |
 | Rudisha     | 102.19999999999999 |
 | David       | NULL               |
 | Rotich      | 103.6              |
 | Makhloufi   | 102.6              |
 | Berian      | 106.1              |
 | Bosse       | 103.4              |
 | Kipketer    | 106                |
 | Nathan      | NULL               |
 | Lewandowski | 104.2              |
 *-------------+--------------------*/

Membuat array

Anda dapat membuat array menggunakan literal array atau fungsi array. Untuk mempelajari lebih lanjut cara membuat array, lihat Jenis array.

Membuat array dari subkueri

Tugas umum saat menggunakan array adalah mengubah hasil subkueri menjadi array. Di GoogleSQL, Anda dapat melakukannya menggunakan fungsi ARRAY().

Misalnya, pertimbangkan operasi berikut pada tabel Sequences:

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x) AS doubled
FROM Sequences;

/*--------------------+---------------------*
 | some_numbers       | doubled             |
 +--------------------+---------------------+
 | [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
 | [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  |
 | [5, 10]            | [10, 20]            |
 *--------------------+---------------------*/

Contoh ini dimulai dengan tabel bernama Urutan. Tabel ini berisi kolom, some_numbers, dengan jenis ARRAY<INT64>.

Kueri itu sendiri berisi subkueri. Subkueri ini memilih setiap baris dalam kolom some_numbers dan menggunakan UNNEST untuk menampilkan array sebagai satu set baris. Selanjutnya, metode ini mengalikan setiap nilai dengan dua, lalu menggabungkan kembali baris tersebut ke dalam array menggunakan operator ARRAY().

Memfilter array

Contoh berikut menggunakan klausa WHERE di subkueri operator ARRAY() untuk memfilter baris yang ditampilkan.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM Sequences;

/*------------------------*
 | doubled_less_than_five |
 +------------------------+
 | [0, 2, 2, 4, 6]        |
 | [4, 8]                 |
 | []                     |
 *------------------------*/

Perhatikan bahwa baris ketiga berisi array kosong, karena elemen dalam baris asli yang sesuai ([5, 10]) tidak memenuhi persyaratan filter x < 5.

Anda juga dapat memfilter array menggunakan SELECT DISTINCT untuk hanya menampilkan elemen unik dalam array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM Sequences;

/*-----------------*
 | unique_numbers  |
 +-----------------+
 | [0, 1, 2, 3, 5] |
 *-----------------*/

Anda juga dapat memfilter baris array menggunakan kata kunci IN. Kata kunci ini memfilter baris yang berisi array dengan menentukan apakah nilai tertentu cocok dengan elemen dalam array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
   ARRAY(SELECT x
         FROM UNNEST(some_numbers) AS x
         WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM Sequences;

/*--------------------*
 | contains_two       |
 +--------------------+
 | [0, 1, 1, 2, 3, 5] |
 | [2, 4, 8, 16, 32]  |
 | []                 |
 *--------------------*/

Perhatikan kembali bahwa baris ketiga berisi array kosong, karena array dalam baris asli yang sesuai ([5, 10]) tidak berisi 2.

Memindai array

Untuk memeriksa apakah array berisi nilai tertentu, gunakan operator IN dengan UNNEST. Untuk memeriksa apakah array berisi nilai yang cocok dengan suatu kondisi, gunakan operator EXISTS dengan UNNEST.

Memindai nilai-nilai tertentu

Untuk memindai array yang berisi nilai tertentu, gunakan operator IN dengan UNNEST.

Contoh

Contoh berikut menampilkan true jika array berisi angka 2.

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

/*----------------*
 | contains_value |
 +----------------+
 | true           |
 *----------------*/

Untuk menampilkan baris tabel tempat kolom array berisi nilai tertentu, filter hasil IN UNNEST menggunakan klausa WHERE.

Contoh

Contoh berikut menampilkan nilai id untuk baris yang kolom array-nya berisi nilai 2.

WITH Sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM Sequences
WHERE 2 IN UNNEST(Sequences.some_numbers)
ORDER BY matching_rows;

/*---------------*
 | matching_rows |
 +---------------+
 | 1             |
 | 2             |
 *---------------*/

Memindai nilai yang memenuhi suatu kondisi

Untuk memindai nilai yang cocok dengan kondisi pada array, gunakan UNNEST untuk menampilkan tabel berisi elemen dalam array, gunakan WHERE untuk memfilter tabel yang dihasilkan di subkueri, dan gunakan EXISTS untuk memeriksa apakah tabel yang difilter berisi baris.

Contoh

Contoh berikut menampilkan nilai id untuk baris yang kolom array-nya berisi nilai yang lebih besar dari 5.

WITH
  Sequences AS (
    SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL
    SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL
    SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5);

/*---------------*
 | matching_rows |
 +---------------+
 | 2             |
 | 3             |
 *---------------*/

Memindai nilai kolom `STRUCT` yang memenuhi kondisi

Guna menelusuri array STRUCT untuk kolom yang nilainya cocok dengan sebuah kondisi, gunakan UNNEST untuk menampilkan tabel dengan kolom untuk setiap kolom STRUCT, lalu filter baris yang tidak cocok dari tabel menggunakan WHERE EXISTS.

Contoh

Contoh berikut menampilkan baris yang kolom array-nya berisi STRUCT yang kolom b-nya memiliki nilai lebih besar dari 3.

WITH
  Sequences AS (
    SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
    UNION ALL
    SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
    UNION ALL
    SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)] AS some_numbers
  )
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT 1 FROM UNNEST(some_numbers) WHERE b > 3);

/*---------------*
 | matching_rows |
 +---------------+
 | 2             |
 | 3             |
 *---------------*/

Array dan agregasi

Dengan GoogleSQL, Anda dapat menggabungkan nilai ke dalam array menggunakan ARRAY_AGG().

WITH Fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM Fruits;

/*-----------------------*
 | fruit_basket          |
 +-----------------------+
 | [apple, pear, banana] |
 *-----------------------*/

Array yang ditampilkan oleh ARRAY_AGG() berada dalam urutan arbitrer, karena urutan penyambungan nilai oleh fungsi tidak dijamin. Untuk mengurutkan elemen array, gunakan ORDER BY. Contoh:

WITH Fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM Fruits;

/*-----------------------*
 | fruit_basket          |
 +-----------------------+
 | [apple, banana, pear] |
 *-----------------------*/

Anda juga dapat menerapkan fungsi agregat seperti SUM() ke elemen dalam array. Misalnya, kueri berikut menampilkan jumlah elemen array untuk setiap baris dalam tabel Sequences.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;

/*--------------------+------*
 | some_numbers       | sums |
 +--------------------+------+
 | [0, 1, 1, 2, 3, 5] | 12   |
 | [2, 4, 8, 16, 32]  | 62   |
 | [5, 10]            | 15   |
 *--------------------+------*/

GoogleSQL juga mendukung fungsi agregat, ARRAY_CONCAT_AGG(), yang menyambungkan elemen kolom array di seluruh baris.

WITH Aggregates AS
  (SELECT [1,2] AS numbers
   UNION ALL SELECT [3,4] AS numbers
   UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM Aggregates;

/*--------------------------------------------------*
 | count_to_six_agg                                 |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 *--------------------------------------------------*/

Mengonversi array ke string

Fungsi ARRAY_TO_STRING() memungkinkan Anda mengonversi ARRAY<STRING> menjadi satu nilai STRING atau ARRAY<BYTES> menjadi nilai BYTES tunggal dengan nilai yang dihasilkan merupakan penyambungan elemen array secara berurutan.

Argumen kedua adalah pemisah yang akan disisipkan fungsi di antara input untuk menghasilkan output; argumen kedua ini harus dari jenis yang sama dengan elemen dari argumen pertama.

Contoh:

WITH Words AS
  (SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;

/*-------------*
 | greetings   |
 +-------------+
 | Hello World |
 *-------------*/

Argumen ketiga opsional akan menggantikan nilai NULL dalam array input.

Jika Anda menghilangkan argumen ini, fungsi tersebut akan mengabaikan elemen array NULL.
Jika Anda memberikan string kosong, fungsi tersebut akan menyisipkan pemisah untuk elemen array NULL.

Contoh:

SELECT
  ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
  ARRAY_TO_STRING(arr, ".", "") AS empty_string,
  ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);

/*------------------+--------------+---------*
 | non_empty_string | empty_string | omitted |
 +------------------+--------------+---------+
 | a.N.b.N.c.N      | a..b..c.     | a.b.c   |
 *------------------+--------------+---------*/

Menggabungkan array

Dalam beberapa kasus, Anda mungkin ingin menggabungkan beberapa array ke dalam satu array. Anda dapat melakukannya menggunakan fungsi ARRAY_CONCAT().

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six;

/*--------------------------------------------------*
 | count_to_six                                     |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 *--------------------------------------------------*/

Memperbarui array

Pertimbangkan tabel berikut yang bernama arrays_table. Kolom pertama dalam tabel adalah array bilangan bulat dan kolom kedua berisi dua array bilangan bulat bertingkat.

WITH arrays_table AS (
  SELECT
    [1, 2] AS regular_array,
    STRUCT([10, 20] AS first_array, [100, 200] AS second_array) AS nested_arrays
  UNION ALL SELECT
    [3, 4] AS regular_array,
    STRUCT([30, 40] AS first_array, [300, 400] AS second_array) AS nested_arrays
)
SELECT * FROM arrays_table;

/*---------------*---------------------------*----------------------------*
 | regular_array | nested_arrays.first_array | nested_arrays.second_array |
 +---------------+---------------------------+----------------------------+
 | [1, 2]        | [10, 20]                  | [100, 200]                 |
 | [3, 4]        | [30, 40]                  | [130, 400]                 |
 *---------------*---------------------------*----------------------------*/

Anda dapat memperbarui array dalam tabel menggunakan pernyataan UPDATE. Contoh berikut memasukkan angka 5 ke dalam kolom regular_array, dan menyisipkan elemen dari kolom first_array kolom nested_arrays ke dalam kolom second_array:

UPDATE
  arrays_table
SET
  regular_array = ARRAY_CONCAT(regular_array, [5]),
  nested_arrays.second_array = ARRAY_CONCAT(nested_arrays.second_array,
                                            nested_arrays.first_array)
WHERE TRUE;
SELECT * FROM arrays_table;

/*---------------*---------------------------*----------------------------*
 | regular_array | nested_arrays.first_array | nested_arrays.second_array |
 +---------------+---------------------------+----------------------------+
 | [1, 2, 5]     | [10, 20]                  | [100, 200, 10, 20]         |
 | [3, 4, 5]     | [30, 40]                  | [130, 400, 30, 40]         |
 *---------------*---------------------------*----------------------------*/

Membuat zip array

Dengan mempertimbangkan dua array yang berukuran sama, Anda dapat menggabungkannya ke dalam satu array yang terdiri dari pasangan elemen dari array input, yang diambil dari posisinya yang sesuai. Operasi ini terkadang disebut zipping.

Anda dapat membuat zip array dengan UNNEST dan WITH OFFSET. Dalam contoh ini, setiap pasangan nilai disimpan sebagai STRUCT dalam array.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    INNER JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  ) AS pairs;

/*------------------------------*
 | pairs                        |
 +------------------------------+
 | [{ letter: "a", number: 1 }, |
 |  { letter: "b", number: 2 }] |
 *------------------------------*/

Anda dapat menggunakan array input dengan panjang yang berbeda asalkan array pertama sama dengan atau kurang dari panjang array kedua. Array yang di-zip akan menjadi panjang dari array input terpendek.

Untuk mendapatkan array yang di-zip yang menyertakan semua elemen meskipun array input memiliki panjang yang berbeda, ubah LEAST menjadi GREATEST. Elemen dari kedua array yang tidak memiliki elemen terkait dalam array lain akan dipasangkan dengan NULL.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    INNER JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  ) AS pairs;

/*-------------------------------*
 | pairs                         |
 +-------------------------------+
 | [{ letter: "a", number: 1 },  |
 |  { letter: "b", number: 2 },  |
 |  { letter: null, number: 3 }] |
 *-------------------------------*/

Membuat array dari array

GoogleSQL tidak mendukung pembuatan array dari array secara langsung. Sebagai gantinya, Anda harus membuat array struct, dengan setiap struct berisi kolom jenis ARRAY. Untuk mengilustrasikan hal ini, pertimbangkan tabel Points berikut:

/*----------*
 | point    |
 +----------+
 | [1, 5]   |
 | [2, 8]   |
 | [3, 7]   |
 | [4, 1]   |
 | [5, 7]   |
 *----------*/

Sekarang, misalkan Anda ingin membuat array yang terdiri dari setiap point dalam tabel Points. Untuk melakukannya, gabungkan array yang ditampilkan dari setiap baris dalam STRUCT, seperti yang ditunjukkan di bawah ini.

WITH Points AS
  (SELECT [1, 5] AS point
   UNION ALL SELECT [2, 8] AS point
   UNION ALL SELECT [3, 7] AS point
   UNION ALL SELECT [4, 1] AS point
   UNION ALL SELECT [5, 7] AS point)
SELECT ARRAY(
  SELECT STRUCT(point)
  FROM Points)
  AS coordinates;

/*-------------------*
 | coordinates       |
 +-------------------+
 | [{point: [1,5]},  |
 |  {point: [2,8]},  |
 |  {point: [5,7]},  |
 |  {point: [3,7]},  |
 |  {point: [4,1]}]  |
 *-------------------*/

Menggunakan array

Mengakses elemen array

Mencari panjang

Mengonversi elemen dalam array menjadi baris dalam tabel

Mengkueri array bertingkat

Mengkueri elemen STRUCT dalam array

Mengkueri kolom jenis ARRAY dalam sebuah struct

Membuat array

Membuat array dari subkueri

Memfilter array

Memindai array

Memindai nilai-nilai tertentu

Memindai nilai yang memenuhi suatu kondisi

Memindai nilai kolom STRUCT yang memenuhi kondisi

Array dan agregasi

Mengonversi array ke string

Menggabungkan array

Memperbarui array

Membuat zip array

Membuat array dari array

Mengkueri elemen `STRUCT` dalam array

Mengkueri kolom jenis `ARRAY` dalam sebuah struct

Memindai nilai kolom `STRUCT` yang memenuhi kondisi