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, atau jenis data kompleks, seperti STRUCT. Namun, array array tidak didukung. Untuk mempelajari jenis data ARRAY lebih lanjut, termasuk penanganan NULL, lihat 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 berikut yang bernama 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: baik 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 berjenis 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 dalam 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 gabungan silang 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 gabungan silang 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 nilai 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]}] | *-------------------*/