Fungsi Array

Operator array dari chapter sebelumnya menjawab pertanyaan ya/tidak: apakah array ini memuat nilai itu? Fungsi array melakukan hal yang jauh lebih transformasional — membongkar array menjadi baris, merakit baris kembali menjadi array, memotong, mengganti, dan menghitung isinya. Dua fungsi pertama (unnest dan array_agg) adalah pintu dua arah antara dunia array dan dunia tabel, dan menguasai keduanya membuka pola query yang mustahil dicapai SQL standar. Seperti biasa, seluruh contoh berjalan pada domain Toko Maju Jaya: tabel produk untuk memanipulasi tag dan tabel pengiriman untuk melihat lintas baris.

array_agg: Dari Baris Menjadi Array #

Aggregate array_agg() merakit nilai dari banyak baris menjadi satu array. Kuncinya ada pada klausa ORDER BY di dalam aggregate — tanpa klausa itu, urutan elemen hasil rakitan tidak dijamin mengikuti apa pun:

sql
buat-tabel.sql
CREATE TABLE produk (
    id     integer PRIMARY KEY,
    nama   text NOT NULL,
    tag    text[]
);

INSERT INTO produk (id, nama, tag) VALUES
    (1, 'Buku Tulis 384 Halaman', ARRAY['alat tulis', 'diskon', 'umum']),
    (2, 'Laptop Kerja 14 inci', ARRAY['elektronik', 'kantor']),
    (3, 'Mouse Wireless', ARRAY['elektronik', 'diskon']);

CREATE TABLE pengiriman (
    id      integer PRIMARY KEY,
    produk  text NOT NULL,
    kota    text NOT NULL
);

INSERT INTO pengiriman (id, produk, kota) VALUES
    (1, 'Buku Tulis 384 Halaman', 'Bandung'),
    (2, 'Buku Tulis 384 Halaman', 'Jakarta'),
    (3, 'Buku Tulis 384 Halaman', 'Surabaya'),
    (4, 'Laptop Kerja 14 inci', 'Jakarta'),
    (5, 'Mouse Wireless', 'Bandung'),
    (6, 'Mouse Wireless', 'Medan');

SELECT id, produk, kota
FROM pengiriman
ORDER BY id;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 6
 id |         produk         |   kota   
----+------------------------+----------
  1 | Buku Tulis 384 Halaman | Bandung
  2 | Buku Tulis 384 Halaman | Jakarta
  3 | Buku Tulis 384 Halaman | Surabaya
  4 | Laptop Kerja 14 inci   | Jakarta
  5 | Mouse Wireless         | Bandung
  6 | Mouse Wireless         | Medan
(6 rows)

sql
array-agg.sql
-- array_agg: baris menjadi array; ORDER BY DI DALAM menentukan urutan elemen
SELECT produk, array_agg(kota ORDER BY kota) AS kota_tujuan
FROM pengiriman
GROUP BY produk
ORDER BY produk;

-- DISTINCT membuang duplikat sebelum dirakit
SELECT array_agg(DISTINCT kota ORDER BY kota) AS kota_unik
FROM pengiriman
WHERE produk = 'Mouse Wireless';

-- FILTER: hanya baris yang lolos syarat yang dirakit
SELECT produk,
       array_agg(kota ORDER BY kota)
           FILTER (WHERE kota <> 'Jakarta') AS tanpa_jakarta
FROM pengiriman
GROUP BY produk
ORDER BY produk;

Output yang Dihasilkan:

text
         produk         |        kota_tujuan         
------------------------+----------------------------
 Buku Tulis 384 Halaman | {Bandung,Jakarta,Surabaya}
 Laptop Kerja 14 inci   | {Jakarta}
 Mouse Wireless         | {Bandung,Medan}
(3 rows)

    kota_unik    
-----------------
 {Bandung,Medan}
(1 row)

         produk         |   tanpa_jakarta    
------------------------+--------------------
 Buku Tulis 384 Halaman | {Bandung,Surabaya}
 Laptop Kerja 14 inci   | 
 Mouse Wireless         | {Bandung,Medan}
(3 rows)

Query pertama mengelompokkan pengiriman per produk lalu merakit daftar kotanya — ORDER BY kota di dalam array_agg menjamin elemen terurut alfabetis di setiap array hasil. Query kedua memakai DISTINCT untuk membuang kota ganda sebelum dirakit. Query ketiga memperkenalkan FILTER: hanya baris yang lolos syarat kota <> 'Jakarta' yang ikut dirakit, sehingga Buku Tulis kehilangan Jakarta dari daftarnya. Ketiga klausa (ORDER BY, DISTINCT, FILTER) bisa dikombinasikan pada aggregate lain seperti sum() atau string_agg() juga — pola yang sama berlaku.

unnest: Dari Array Menjadi Baris #

unnest() adalah kebalikan array_agg: array dibongkar menjadi baris, satu baris per elemen. Fungsi ini adalah “ledakan” yang mengubah kolom menjadi tabel:

sql
unnest-dan-ordinality.sql
-- unnest: membongkar array menjadi baris, satu baris per elemen
SELECT unnest(ARRAY['senin', 'rabu', 'jumat']) AS hari;

-- WITH ORDINALITY: posisi elemen ikut keluar sebagai kolom
SELECT *
FROM unnest(ARRAY['senin', 'rabu', 'jumat'])
    WITH ORDINALITY AS t(hari, posisi);

-- unnest dua array sekaligus: elemen ke-n dipasangkan dengan elemen ke-n
SELECT *
FROM unnest(ARRAY['senin', 'rabu'], ARRAY[150, 230]) AS t(hari, omzet);

-- pada tabel: kolom array "melebar" menjadi baris
SELECT id, nama, unnest(tag) AS tag
FROM produk
ORDER BY id, tag;

-- kombinasi: bongkar dengan ORDINALITY, rakit ulang sesuai posisi asli
SELECT p.id, p.nama,
       array_agg(t.tag ORDER BY t.posisi) AS tag_rakit_ulang
FROM produk p
CROSS JOIN LATERAL unnest(p.tag) WITH ORDINALITY AS t(tag, posisi)
GROUP BY p.id, p.nama
ORDER BY p.id;

Output yang Dihasilkan:

text
 hari  
-------
 senin
 rabu
 jumat
(3 rows)

 hari  | posisi 
-------+--------
 senin |      1
 rabu  |      2
 jumat |      3
(3 rows)

 hari  | omzet 
-------+-------
 senin |   150
 rabu  |   230
(2 rows)

 id |          nama          |    tag     
----+------------------------+------------
  1 | Buku Tulis 384 Halaman | alat tulis
  1 | Buku Tulis 384 Halaman | diskon
  1 | Buku Tulis 384 Halaman | umum
  2 | Laptop Kerja 14 inci   | elektronik
  2 | Laptop Kerja 14 inci   | kantor
  3 | Mouse Wireless         | diskon
  3 | Mouse Wireless         | elektronik
(7 rows)

 id |          nama          |      tag_rakit_ulang       
----+------------------------+----------------------------
  1 | Buku Tulis 384 Halaman | {"alat tulis",diskon,umum}
  2 | Laptop Kerja 14 inci   | {elektronik,kantor}
  3 | Mouse Wireless         | {elektronik,diskon}
(3 rows)

Lima contoh di atas naik tingkat demi tingkat. Bentuk paling sederhana membongkar array tiga elemen menjadi tiga baris. WITH ORDINALITY menambahkan nomor posisi elemen — ingat bahwa urutan ORDER BY di level luar bisa menyortir ulang baris, jadi posisi asli elemen harus dibawa sebagai kolom bila urutan itu penting. unnest dua array sekaligus memasangkan elemen ke-1 dengan elemen ke-1, elemen ke-2 dengan elemen ke-2 — pola yang enak dipakai untuk data pasangan. Query keempat memperlihatkan “melebar”-nya tabel produk: produk bertag tiga menghasilkan tiga baris output. Query kelima adalah kombinasi lengkapnya: unnest ... WITH ORDINALITY lewat CROSS JOIN LATERAL membongkar tag, lalu array_agg(tag ORDER BY posisi) merakitnya kembali sesuai urutan asli — siklus bongkar-pasang yang utuh, kebalikan penuh dari array_agg.

Fungsi Pembentuk Array Baru #

Kelompok fungsi berikutnya memodifikasi isi array. Satu hal penting sebelum melihat contoh: semuanya mengembalikan array baru — array asli tidak pernah diubah di tempat, sehingga efeknya baru tersimpan setelah ditulis lewat UPDATE:

sql
mengolah-array.sql
-- array_append: satu elemen baru di akhir
SELECT array_append(ARRAY['a', 'b'], 'c') AS di_akhir;

-- array_prepend: satu elemen baru di awal (perhatikan: elemen lebih dulu)
SELECT array_prepend('a', ARRAY['b', 'c']) AS di_awal;

-- array_cat: menggabungkan dua array
SELECT array_cat(ARRAY['a', 'b'], ARRAY['c', 'd']) AS digabung;

-- array_remove: membuang SEMUA elemen yang sama
SELECT array_remove(ARRAY['a', 'b', 'a', 'c'], 'a') AS tanpa_a;

-- trim_array: membuang N elemen terakhir sekaligus
SELECT trim_array(ARRAY['a', 'b', 'c', 'd'], 2) AS dipangkas;

-- semua fungsi di atas mengembalikan array BARU: kolom tabel tidak berubah
SELECT id, nama, tag AS tag_asli,
       array_append(tag, 'promo') AS hasil_append
FROM produk
WHERE id = 1;

-- untuk benar-benar menyimpan, hasilnya harus ditulis dengan UPDATE
UPDATE produk SET tag = array_append(tag, 'promo')
WHERE id = 1;

SELECT id, nama, tag
FROM produk
WHERE id = 1;

Output yang Dihasilkan:

text
 di_akhir 
----------
 {a,b,c}
(1 row)

 di_awal 
---------
 {a,b,c}
(1 row)

 digabung  
-----------
 {a,b,c,d}
(1 row)

 tanpa_a 
---------
 {b,c}
(1 row)

 dipangkas 
-----------
 {a,b}
(1 row)

 id |          nama          |          tag_asli          |           hasil_append           
----+------------------------+----------------------------+----------------------------------
  1 | Buku Tulis 384 Halaman | {"alat tulis",diskon,umum} | {"alat tulis",diskon,umum,promo}
(1 row)

UPDATE 1
 id |          nama          |               tag                
----+------------------------+----------------------------------
  1 | Buku Tulis 384 Halaman | {"alat tulis",diskon,umum,promo}
(1 row)

array_append menambah satu elemen di akhir dan array_prepend menambah di awal — perhatikan urutan parameternya berbeda: array_append(array, elemen) tetapi array_prepend(elemen, array). array_cat menggabungkan dua array (padanan operator ||), array_remove membuang semua kemunculan satu nilai, dan trim_array memangkas N elemen terakhir sekaligus — jauh lebih ringkas daripada array_remove berulang. SELECT pertama membuktikan sifat tak-mutabel: kolom tag_asli tetap tiga elemen meski hasil_append sudah bernilai empat. Hanya UPDATE di akhir file yang benar-benar menyimpan perubahan, dan SELECT penutup memastikan tag Buku Tulis kini memuat promo.

Mencari dan Mengganti Nilai #

Setelah chapter operator mengajarkan menanyakan keanggotaan, dua fungsi ini melapor posisi persisnya — dan array_replace menutup celah yang ditinggalkan operator dengan mengganti nilai:

sql
posisi-dan-nilai.sql
-- array_position: posisi kemunculan PERTAMA (NULL bila tidak ditemukan)
SELECT array_position(ARRAY['alat tulis', 'diskon', 'umum', 'diskon'], 'diskon') AS posisi_pertama,
       array_position(ARRAY['alat tulis', 'diskon'], 'elektronik')               AS tidak_ada;

-- array_positions: SEMUA posisi kemunculan, sebagai array
SELECT array_positions(ARRAY['alat tulis', 'diskon', 'umum', 'diskon'], 'diskon') AS semua_posisi;

-- array_replace: ganti setiap kemunculan nilai lama dengan nilai baru
SELECT array_replace(ARRAY['diskon', 'umum', 'diskon'], 'diskon', 'promo') AS diganti;

-- di tabel: di posisi berapa saja tag 'diskon' muncul pada tiap produk
SELECT id, nama, array_positions(tag, 'diskon') AS posisi_diskon
FROM produk
WHERE 'diskon' = ANY (tag)
ORDER BY id;

Output yang Dihasilkan:

text
 posisi_pertama | tidak_ada 
----------------+-----------
              2 |          
(1 row)

 semua_posisi 
--------------
 {2,4}
(1 row)

      diganti       
--------------------
 {promo,umum,promo}
(1 row)

 id |          nama          | posisi_diskon 
----+------------------------+---------------
  1 | Buku Tulis 384 Halaman | {2}
  3 | Mouse Wireless         | {2}
(2 rows)

array_position mengembalikan indeks kemunculan pertama (2 untuk 'diskon'), atau NULL bila nilai tidak ada — bukan 0, bukan error. array_positions mengembalikan seluruh posisi kemunculan sebagai array {2,4} — jawaban yang lebih jujur ketika nilai bisa muncul berkali-kali. array_replace mengganti setiap kemunculan 'diskon' menjadi 'promo' dalam sekali panggil. Query terakhir menerapkannya pada tabel: mencari semua produk bertag diskon (dengan = ANY, si operator andalan), lalu melaporkan posisi kemunculan tag itu di masing-masing array — pola yang berguna saat urutan tag punya makna, misalnya tag pertama sebagai kategori utama.

Konversi String dan Pengukuran Ukuran #

Dua fungsi terakhir adalah jembatan array↔string yang dipakai hampir setiap hari di lapisan aplikasi, ditutup pasangan pengukuran yang sering tertukar:

sql
string-dan-ukuran.sql
-- array_to_string: merakit array menjadi satu string dengan pemisah pilihan
SELECT array_to_string(ARRAY['Buku Tulis', 'Pensil', 'Kertas'], ', ') AS daftar;

-- string_to_array: memecah string menjadi array dengan pemisah pilihan
SELECT string_to_array('alat tulis, diskon, umum', ', ') AS pecah;

-- elemen NULL dilewati secara diam-diam oleh array_to_string
SELECT array_to_string(ARRAY['diskon', NULL, 'umum'], ', ') AS null_dilewati;

-- array_length mengukur satu dimensi; cardinality menghitung seluruh elemen
SELECT id, nama,
       array_length(tag, 1) AS panjang,
       cardinality(tag)     AS total
FROM produk
ORDER BY id;

-- pada array dua dimensi keduanya benar-benar berbeda makna
SELECT array_length(ARRAY[[10, 20], [30, 40]], 1) AS dimensi_1,
       array_length(ARRAY[[10, 20], [30, 40]], 2) AS dimensi_2,
       cardinality(ARRAY[[10, 20], [30, 40]])     AS total_elemen;

Output yang Dihasilkan:

text
           daftar           
----------------------------
 Buku Tulis, Pensil, Kertas
(1 row)

           pecah            
----------------------------
 {"alat tulis",diskon,umum}
(1 row)

 null_dilewati 
---------------
 diskon, umum
(1 row)

 id |          nama          | panjang | total 
----+------------------------+---------+-------
  1 | Buku Tulis 384 Halaman |       4 |     4
  2 | Laptop Kerja 14 inci   |       2 |     2
  3 | Mouse Wireless         |       2 |     2
(3 rows)

 dimensi_1 | dimensi_2 | total_elemen 
-----------+-----------+--------------
         2 |         2 |            4
(1 row)

array_to_string merakit array menjadi satu string dengan pemisah pilihan — padanan join() di bahasa pemrograman — dan string_to_array melakukan sebaliknya, padanan split(). Keduanya menjadikan format {a,b} tidak perlu ditulis tangan: rakit string dengan bahasa aplikasi Anda, lalu pecah lagi di sisi database. Perhatikan jebakan di contoh ketiga: elemen NULL dilewati diam-diam oleh array_to_string, sehingga array tiga elemen menghasilkan string dua nilai. Terakhir, bedakan dua pengukur: array_length(tag, 1) mengukur panjang satu dimensi dan mengembalikan NULL untuk array kosong, sedangkan cardinality() menghitung total elemen di seluruh dimensi — pada array dua dimensi, array_length per dimensi melaporkan 2 dan 2, sementara cardinality melaporkan total 4.

Array adalah tipe yang datang bersama PostgreSQL, lengkap dengan operator dan fungsinya. Pertanyaan berikutnya bergerak ke arah yang berbeda: bagaimana bila kita ingin tipe bentukan sendiri — nama, struktur, dan aturan validasi yang kita definisikan? Perhentian pertama adalah composite type: tipe baris yang field-nya kita rancang sendiri.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- array_agg: baris menjadi array; ORDER BY DI DALAM menentukan urutan elemen
SELECT produk, array_agg(kota ORDER BY kota) AS kota_tujuan
FROM pengiriman
GROUP BY produk
ORDER BY produk;

-- DISTINCT membuang duplikat sebelum dirakit
SELECT array_agg(DISTINCT kota ORDER BY kota) AS kota_unik
FROM pengiriman
WHERE produk = 'Mouse Wireless';

-- FILTER: hanya baris yang lolos syarat yang dirakit
SELECT produk,
       array_agg(kota ORDER BY kota)
           FILTER (WHERE kota <> 'Jakarta') AS tanpa_jakarta
FROM pengiriman
GROUP BY produk
ORDER BY produk;

sql
CREATE TABLE produk (
    id     integer PRIMARY KEY,
    nama   text NOT NULL,
    tag    text[]
);

INSERT INTO produk (id, nama, tag) VALUES
    (1, 'Buku Tulis 384 Halaman', ARRAY['alat tulis', 'diskon', 'umum']),
    (2, 'Laptop Kerja 14 inci', ARRAY['elektronik', 'kantor']),
    (3, 'Mouse Wireless', ARRAY['elektronik', 'diskon']);

CREATE TABLE pengiriman (
    id      integer PRIMARY KEY,
    produk  text NOT NULL,
    kota    text NOT NULL
);

INSERT INTO pengiriman (id, produk, kota) VALUES
    (1, 'Buku Tulis 384 Halaman', 'Bandung'),
    (2, 'Buku Tulis 384 Halaman', 'Jakarta'),
    (3, 'Buku Tulis 384 Halaman', 'Surabaya'),
    (4, 'Laptop Kerja 14 inci', 'Jakarta'),
    (5, 'Mouse Wireless', 'Bandung'),
    (6, 'Mouse Wireless', 'Medan');

SELECT id, produk, kota
FROM pengiriman
ORDER BY id;

sql
-- array_append: satu elemen baru di akhir
SELECT array_append(ARRAY['a', 'b'], 'c') AS di_akhir;

-- array_prepend: satu elemen baru di awal (perhatikan: elemen lebih dulu)
SELECT array_prepend('a', ARRAY['b', 'c']) AS di_awal;

-- array_cat: menggabungkan dua array
SELECT array_cat(ARRAY['a', 'b'], ARRAY['c', 'd']) AS digabung;

-- array_remove: membuang SEMUA elemen yang sama
SELECT array_remove(ARRAY['a', 'b', 'a', 'c'], 'a') AS tanpa_a;

-- trim_array: membuang N elemen terakhir sekaligus
SELECT trim_array(ARRAY['a', 'b', 'c', 'd'], 2) AS dipangkas;

-- semua fungsi di atas mengembalikan array BARU: kolom tabel tidak berubah
SELECT id, nama, tag AS tag_asli,
       array_append(tag, 'promo') AS hasil_append
FROM produk
WHERE id = 1;

-- untuk benar-benar menyimpan, hasilnya harus ditulis dengan UPDATE
UPDATE produk SET tag = array_append(tag, 'promo')
WHERE id = 1;

SELECT id, nama, tag
FROM produk
WHERE id = 1;

sql
-- array_position: posisi kemunculan PERTAMA (NULL bila tidak ditemukan)
SELECT array_position(ARRAY['alat tulis', 'diskon', 'umum', 'diskon'], 'diskon') AS posisi_pertama,
       array_position(ARRAY['alat tulis', 'diskon'], 'elektronik')               AS tidak_ada;

-- array_positions: SEMUA posisi kemunculan, sebagai array
SELECT array_positions(ARRAY['alat tulis', 'diskon', 'umum', 'diskon'], 'diskon') AS semua_posisi;

-- array_replace: ganti setiap kemunculan nilai lama dengan nilai baru
SELECT array_replace(ARRAY['diskon', 'umum', 'diskon'], 'diskon', 'promo') AS diganti;

-- di tabel: di posisi berapa saja tag 'diskon' muncul pada tiap produk
SELECT id, nama, array_positions(tag, 'diskon') AS posisi_diskon
FROM produk
WHERE 'diskon' = ANY (tag)
ORDER BY id;

sql
-- array_to_string: merakit array menjadi satu string dengan pemisah pilihan
SELECT array_to_string(ARRAY['Buku Tulis', 'Pensil', 'Kertas'], ', ') AS daftar;

-- string_to_array: memecah string menjadi array dengan pemisah pilihan
SELECT string_to_array('alat tulis, diskon, umum', ', ') AS pecah;

-- elemen NULL dilewati secara diam-diam oleh array_to_string
SELECT array_to_string(ARRAY['diskon', NULL, 'umum'], ', ') AS null_dilewati;

-- array_length mengukur satu dimensi; cardinality menghitung seluruh elemen
SELECT id, nama,
       array_length(tag, 1) AS panjang,
       cardinality(tag)     AS total
FROM produk
ORDER BY id;

-- pada array dua dimensi keduanya benar-benar berbeda makna
SELECT array_length(ARRAY[[10, 20], [30, 40]], 1) AS dimensi_1,
       array_length(ARRAY[[10, 20], [30, 40]], 2) AS dimensi_2,
       cardinality(ARRAY[[10, 20], [30, 40]])     AS total_elemen;

sql
-- unnest: membongkar array menjadi baris, satu baris per elemen
SELECT unnest(ARRAY['senin', 'rabu', 'jumat']) AS hari;

-- WITH ORDINALITY: posisi elemen ikut keluar sebagai kolom
SELECT *
FROM unnest(ARRAY['senin', 'rabu', 'jumat'])
    WITH ORDINALITY AS t(hari, posisi);

-- unnest dua array sekaligus: elemen ke-n dipasangkan dengan elemen ke-n
SELECT *
FROM unnest(ARRAY['senin', 'rabu'], ARRAY[150, 230]) AS t(hari, omzet);

-- pada tabel: kolom array "melebar" menjadi baris
SELECT id, nama, unnest(tag) AS tag
FROM produk
ORDER BY id, tag;

-- kombinasi: bongkar dengan ORDINALITY, rakit ulang sesuai posisi asli
SELECT p.id, p.nama,
       array_agg(t.tag ORDER BY t.posisi) AS tag_rakit_ulang
FROM produk p
CROSS JOIN LATERAL unnest(p.tag) WITH ORDINALITY AS t(tag, posisi)
GROUP BY p.id, p.nama
ORDER BY p.id;