Endpoint API jarang mengembalikan tabel datar. Klien biasanya ingin satu dokumen bersarang: produk berisi array ulasannya, kategori berisi daftar produknya, lengkap dengan ringkasan seperti rating rata-rata. Kalau perakitan itu dilakukan di kode aplikasi, pola yang muncul adalah klasik: query produk, lalu query ulasan per produk (satu per satu), lalu dijahit manual di Python atau JavaScript — banyak round-trip, banyak kode tempel. PostgreSQL bisa mengerjakannya dalam satu query: fungsi to_jsonb mengubah baris menjadi objek, agregat jsonb_agg melipat banyak baris menjadi array, jsonb_object_agg menjadi objek, dan jsonb_build_object merakit semuanya menjadi dokumen bersarang. Chapter ini membangun pola tersebut bertahap di atas tabel produk dan ulasan Toko Maju Jaya, ditutup dengan resep yang paling sering dipakai produksi: respons API berisi produk dengan array ulasan tertanam.
Tabel Uji: produk dan ulasan #
Dua tabel berikut sengaja dibuat relasional normal — tanpa kolom jsonb sedikit pun. Fungsi agregasi JSON bekerja pada tabel biasa; JSON lahir di hasil query, bukan di penyimpanan. Tabel ulasan merujuk produk lewat foreign key, dan produk 5 sengaja dibiarkan tanpa ulasan:
-- Toko Maju Jaya: produk relasional plus tabel ulasan pelanggan.
-- Struktur ini bahan baku semua contoh di chapter ini.
CREATE TABLE produk (
id integer PRIMARY KEY,
nama text NOT NULL,
kategori text NOT NULL,
harga numeric(10,2) NOT NULL
);
CREATE TABLE ulasan (
id integer PRIMARY KEY,
produk_id integer NOT NULL REFERENCES produk(id),
pengulas text NOT NULL,
rating integer NOT NULL,
komentar text
);
INSERT INTO produk (id, nama, kategori, harga) VALUES
(1, 'Sepatu Lari Pagi', 'olahraga', 750000),
(2, 'Matras Yoga', 'olahraga', 180000),
(3, 'Kemeja Batik Kasih', 'pakaian', 320000),
(4, 'Tas Ransel Gunung', 'outdoor', 450000),
(5, 'Botol Minum Stainless', 'olahraga', 95000);
INSERT INTO ulasan (id, produk_id, pengulas, rating, komentar) VALUES
(1, 1, 'Budi', 5, 'Ringan dan nyaman untuk lari pagi.'),
(2, 1, 'Siti', 4, 'Ukuran pas di kaki.'),
(3, 2, 'Siti', 5, 'Empuk dan tidak licin.'),
(4, 3, 'Budi', 3, 'Bahan agak tebal untuk cuaca panas.'),
(5, 4, 'Dewi', 4, 'Kuat, muat banyak.');
-- Produk 5 sengaja tanpa ulasan; kita pakai untuk menguji
-- perilaku jsonb_agg pada himpunan kosong nanti.
SELECT p.id, p.nama, p.kategori, p.harga,
count(u.id) AS jumlah_ulasan
FROM produk p
LEFT JOIN ulasan u ON u.produk_id = p.id
GROUP BY p.id, p.nama, p.kategori, p.harga
ORDER BY p.id;Output yang Dihasilkan:
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 5
id | nama | kategori | harga | jumlah_ulasan
----+-----------------------+----------+-----------+---------------
1 | Sepatu Lari Pagi | olahraga | 750000.00 | 2
2 | Matras Yoga | olahraga | 180000.00 | 1
3 | Kemeja Batik Kasih | pakaian | 320000.00 | 1
4 | Tas Ransel Gunung | outdoor | 450000.00 | 1
5 | Botol Minum Stainless | olahraga | 95000.00 | 0
(5 rows)Query penutup memakai LEFT JOIN untuk memperlihatkan jumlah ulasan tiap produk: produk 1 punya dua ulasan, produk 5 nol. Angka-angka itu akan kita lihat lagi wujudnya sebagai array dan objek JSON.
to_jsonb: Satu Baris Menjadi Satu Objek #
Titik awal paling sederhana: fungsi to_jsonb menerima satu nilai apa pun dan mengubahnya menjadi jsonb. Bila nilai itu adalah baris tabel, setiap kolom menjadi kunci dan setiap nilai kolom menjadi nilai JSON — seperti melipat satu baris menjadi dokumen:
-- to_jsonb: mengubah SATU nilai/baris menjadi dokumen jsonb.
-- Satu baris tabel menjadi satu objek: kolom jadi kunci,
-- nilai kolom jadi nilai JSON. Urutan kunci mengikuti urutan kolom.
SELECT to_jsonb(p) AS produk_json
FROM produk p
WHERE p.id = 1;
-- row_to_json: pendahulu to_jsonb bertipe json (output rapit satu baris).
SELECT row_to_json(p) AS produk_json_lama
FROM produk p
WHERE p.id = 1;
-- to_jsonb juga bekerja pada nilai biasa, bukan hanya baris tabel:
SELECT to_jsonb(95000::numeric) AS angka,
to_jsonb('Matras Yoga'::text) AS teks;
-- Versi baris dapat dirangkai jadi array lewat jsonb_agg.
SELECT jsonb_agg(to_jsonb(p) ORDER BY p.id) AS semua_produk
FROM produk p;Output yang Dihasilkan:
produk_json
-----------------------------------------------------------------------------------
{"id": 1, "nama": "Sepatu Lari Pagi", "harga": 750000.00, "kategori": "olahraga"}
(1 row)
produk_json_lama
----------------------------------------------------------------------------
{"id":1,"nama":"Sepatu Lari Pagi","kategori":"olahraga","harga":750000.00}
(1 row)
angka | teks
-------+---------------
95000 | "Matras Yoga"
(1 row)
semua_produk
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"id": 1, "nama": "Sepatu Lari Pagi", "harga": 750000.00, "kategori": "olahraga"}, {"id": 2, "nama": "Matras Yoga", "harga": 180000.00, "kategori": "olahraga"}, {"id": 3, "nama": "Kemeja Batik Kasih", "harga": 320000.00, "kategori": "pakaian"}, {"id": 4, "nama": "Tas Ransel Gunung", "harga": 450000.00, "kategori": "outdoor"}, {"id": 5, "nama": "Botol Minum Stainless", "harga": 95000.00, "kategori": "olahraga"}]
(1 row)Query pertama melipat baris produk 1 menjadi objek dengan kunci id, nama, harga, kategori — urutan kunci mengikuti urutan kolom tabel, dan numeric tetap membawa skala desimalnya (750000.00). Query kedua menunjukkan row_to_json, pendahulu to_jsonb yang menghasilkan tipe json dengan tampilan rapit; to_jsonb adalah pilihan utama karena hasilnya bisa langsung dioperasikan operator jsonb lain. Query ketiga membuktikan to_jsonb tidak eksklusif untuk baris tabel: angka menjadi angka JSON, teks menjadi string JSON dengan kutip. Query keempat menggabungkan keduanya — to_jsonb(p) untuk setiap baris dilipat jsonb_agg menjadi satu array berisi lima objek; itulah jembatan menuju topik berikutnya.
jsonb_agg: Banyak Baris Menjadi Satu Array #
Agregat jsonb_agg(expression) melipat nilai dari banyak baris menjadi satu array jsonb. Sebagai agregat, fungsi ini hidup di dua tempat: tanpa GROUP BY seluruh hasil query dilipat jadi satu array; dengan GROUP BY setiap kelompok mendapat array-nya sendiri:
-- jsonb_agg: BANYAK baris menjadi SATU array jsonb.
-- Array nilai sederhana: nama semua produk,
-- diurutkan alfabetis dari dalam agregat.
SELECT jsonb_agg(nama ORDER BY nama) AS daftar_nama
FROM produk;
-- Tanpa ORDER BY di dalam agregat, urutan array hanya mengikuti
-- urutan baris yang kebetulan diterima agregat. Di sini subquery
-- mengirim baris terurut nama menurun -- array pun menurun.
-- Jangan andalkan urutan input: tulis ORDER BY di dalam agregat.
SELECT jsonb_agg(nama) AS urutan_masukan
FROM (SELECT nama FROM produk ORDER BY nama DESC) s;
-- Aggregate JSON di SQL biasa: gabungkan dengan agregat numerik
-- dalam satu query GROUP BY.
SELECT kategori,
count(*) AS jumlah,
jsonb_agg(nama ORDER BY id) AS daftar_produk
FROM produk
GROUP BY kategori
ORDER BY kategori;Output yang Dihasilkan:
daftar_nama
---------------------------------------------------------------------------------------------------------
["Botol Minum Stainless", "Kemeja Batik Kasih", "Matras Yoga", "Sepatu Lari Pagi", "Tas Ransel Gunung"]
(1 row)
urutan_masukan
---------------------------------------------------------------------------------------------------------
["Tas Ransel Gunung", "Sepatu Lari Pagi", "Matras Yoga", "Kemeja Batik Kasih", "Botol Minum Stainless"]
(1 row)
kategori | jumlah | daftar_produk
----------+--------+--------------------------------------------------------------
olahraga | 3 | ["Sepatu Lari Pagi", "Matras Yoga", "Botol Minum Stainless"]
outdoor | 1 | ["Tas Ransel Gunung"]
pakaian | 1 | ["Kemeja Batik Kasih"]
(3 rows)Query pertama menghasilkan array lima nama terurut alfabetis — perhatikan bahwa ORDER BY ditulis di dalam agregat (jsonb_agg(nama ORDER BY nama)), bukan di akhir query; di akhir query hanya mengurutkan baris hasil, dan query ini menghasilkan satu baris saja. Query kedua memperlihatkan perilaku yang perlu diwaspadai: tanpa ORDER BY internal, urutan array mengikuti urutan baris yang diterima agregat. Subquery kita mengirim nama terurut menurun, jadi array-nya pun menurun — pada query nyata urutan datang dari rencana eksekusi planner yang boleh berubah kapan saja. Query ketiga adalah pola kerja sehari-hari: GROUP BY kategori dengan count(*) dan jsonb_agg(nama ORDER BY id) berdampingan; setiap kategori mendapat array produknya sendiri dalam urutan id.
jsonb_object_agg: Banyak Baris Menjadi Satu Objek #
Kadang array kurang praktis karena klien ingin lookup, bukan daftar. Agregat jsonb_object_agg(kunci, nilai) melipat baris menjadi satu objek: baris pertama menyumbang pasangan kunci-nilai pertama, baris kedua pasangan kedua, dan seterusnya:
-- jsonb_object_agg(kunci, nilai): BANYAK baris menjadi SATU objek.
-- Tiap baris menyumbang satu pasangan kunci-nilai.
-- Peta kategori -> jumlah produk, dirakit dari hasil GROUP BY.
SELECT jsonb_object_agg(kategori, jumlah) AS produk_per_kategori
FROM (SELECT kategori, count(*) AS jumlah
FROM produk
GROUP BY kategori) s;
-- Peta id -> nama: lookup kecil yang siap dikirim ke aplikasi.
SELECT jsonb_object_agg(id, nama ORDER BY id) AS nama_by_id
FROM produk;
-- jsonb menormalkan urutan kunci (panjang lalu alfabetis),
-- jadi urutan pasangan dari agregat tidak memengaruhi tampilan akhir.Output yang Dihasilkan:
produk_per_kategori
---------------------------------------------
{"outdoor": 1, "pakaian": 1, "olahraga": 3}
(1 row)
nama_by_id
----------------------------------------------------------------------------------------------------------------------------------
{"1": "Sepatu Lari Pagi", "2": "Matras Yoga", "3": "Kemeja Batik Kasih", "4": "Tas Ransel Gunung", "5": "Botol Minum Stainless"}
(1 row)Query pertama merakit peta "kategori" -> jumlah dari hasil GROUP BY bersubquery — olahraga bernilai 3, dua kategori lain 1. Query kedua membangun peta "id" -> "nama" yang siap dipakai aplikasi untuk lookup. Dua detail layak dicatat dari outputnya. Pertama, kunci objek JSON selalu string — angka id otomatis dikutip ("1", "2"), bukan angka. Kedua, urutan pasangan tidak mengikuti urutan agregat: jsonb menormalkan urutan kunci berdasarkan panjang lalu alfabetis (lihat kembali pembahasan json vs jsonb di chapter JSON vs JSONB
), jadi ORDER BY di dalam jsonb_object_agg tidak mengubah tampilan akhir — berbeda dengan jsonb_agg yang urutan array-nya persis mengikuti ORDER BY.
Merakit Dokumen Respons API #
Sekarang pola produksinya. Bahan: jsonb_build_object(kunci1, nilai1, kunci2, nilai2, ...) yang menyusun objek dari argumen berpasangan, dan subquery berkorelasi yang memanggil jsonb_agg untuk mengumpulkan ulasan milik produk yang sedang diproses. Kombinasi keduanya menghasilkan dokumen bersarang dalam satu SELECT:
-- Merakit dokumen respons API: produk dengan array ulasan tertanam.
-- jsonb_build_object menyusun objek; jsonb_agg dalam subquery
-- mengumpulkan ulasan terkait menjadi array.
-- Satu produk lengkap, ditampilkan rapat lewat jsonb_pretty.
SELECT jsonb_pretty(jsonb_build_object(
'id', p.id,
'nama', p.nama,
'harga', p.harga,
'rating_rata', (SELECT round(avg(u.rating), 1)
FROM ulasan u
WHERE u.produk_id = p.id),
'ulasan', (SELECT jsonb_agg(
jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating,
'komentar', u.komentar)
ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id)
)) AS dokumen_api
FROM produk p
WHERE p.id = 1;
-- Semua produk sekaligus, versi ringkas tanpa jsonb_pretty.
-- Produk tanpa ulasan mendapat NULL -- perhatikan produk 5.
SELECT p.id,
jsonb_build_object(
'nama', p.nama,
'ulasan', (SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id)
) AS dokumen
FROM produk p
ORDER BY p.id;
-- Perbaikannya: COALESCE mengganti NULL dengan array kosong.
SELECT p.id,
jsonb_build_object(
'nama', p.nama,
'ulasan', COALESCE((SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id),
'[]'::jsonb)
) AS dokumen
FROM produk p
ORDER BY p.id;Output yang Dihasilkan:
dokumen_api
---------------------------------------------------------------
{ +
"id": 1, +
"nama": "Sepatu Lari Pagi", +
"harga": 750000.00, +
"ulasan": [ +
{ +
"rating": 5, +
"komentar": "Ringan dan nyaman untuk lari pagi.",+
"pengulas": "Budi" +
}, +
{ +
"rating": 4, +
"komentar": "Ukuran pas di kaki.", +
"pengulas": "Siti" +
} +
], +
"rating_rata": 4.5 +
}
(1 row)
id | dokumen
----+----------------------------------------------------------------------------------------------------------------
1 | {"nama": "Sepatu Lari Pagi", "ulasan": [{"rating": 5, "pengulas": "Budi"}, {"rating": 4, "pengulas": "Siti"}]}
2 | {"nama": "Matras Yoga", "ulasan": [{"rating": 5, "pengulas": "Siti"}]}
3 | {"nama": "Kemeja Batik Kasih", "ulasan": [{"rating": 3, "pengulas": "Budi"}]}
4 | {"nama": "Tas Ransel Gunung", "ulasan": [{"rating": 4, "pengulas": "Dewi"}]}
5 | {"nama": "Botol Minum Stainless", "ulasan": null}
(5 rows)
id | dokumen
----+----------------------------------------------------------------------------------------------------------------
1 | {"nama": "Sepatu Lari Pagi", "ulasan": [{"rating": 5, "pengulas": "Budi"}, {"rating": 4, "pengulas": "Siti"}]}
2 | {"nama": "Matras Yoga", "ulasan": [{"rating": 5, "pengulas": "Siti"}]}
3 | {"nama": "Kemeja Batik Kasih", "ulasan": [{"rating": 3, "pengulas": "Budi"}]}
4 | {"nama": "Tas Ransel Gunung", "ulasan": [{"rating": 4, "pengulas": "Dewi"}]}
5 | {"nama": "Botol Minum Stainless", "ulasan": []}
(5 rows)Query pertama menghasilkan dokumen lengkap produk 1 dan menampilkannya lewat jsonb_pretty agar bertingkat dan mudah dibaca manusia. Mari kita bedah bagiannya:
jsonb_build_object('id', p.id, 'nama', p.nama, ...): menyusun objek level atas — argumen bergantian kunci, nilai, kunci, nilai.- Subquery
'rating_rata': agregat biasaround(avg(u.rating), 1)menghasilkan4.5— nilai SQL biasa ikut masuk sebagai angka JSON. - Subquery
'ulasan': untuk produk yang sedang diproses,jsonb_agg(jsonb_build_object(...) ORDER BY u.id)melipat seluruh ulasannya menjadi array objek. KorelasiWHERE u.produk_id = p.idmenjaga hanya ulasan milik produk itu yang terkumpul.
Query kedua menjalankan pola yang sama untuk semua produk dalam bentuk ringkas, dan di situlah masalah muncul: produk 5 tidak punya ulasan, subquery-nya mengembalikan himpunan kosong, dan jsonb_agg atas himpunan kosong menghasilkan NULL — bukan array kosong. Hasilnya "ulasan": null, yang biasanya membuat klien API tersandung. Query ketiga menunjukkan perbaikannya: bungkus subquery dengan COALESCE((...), '[]'::jsonb) sehingga produk tanpa ulasan mendapat "ulasan": [].
GROUP BY + jsonb_agg: Dokumen per Kategori #
Pola terakhir menggabungkan semuanya: GROUP BY untuk membagi baris per kelompok, jsonb_agg untuk melipat isi kelompok, dan subquery berkorelasi lagi untuk tingkat kedalaman berikutnya — ulasan di dalam produk di dalam kategori:
-- GROUP BY + jsonb_agg: dokumen per kategori.
-- Satu baris hasil per kategori, produk-produknya tertanam sebagai array.
SELECT kategori,
count(*) AS jumlah,
jsonb_agg(jsonb_build_object(
'id', id,
'nama', nama,
'harga', harga
) ORDER BY id) AS produk
FROM produk
GROUP BY kategori
ORDER BY kategori;
-- Digabung ulasan: dokumen kategori berisi produk
-- yang masing-masing membawa array ulasannya sendiri.
SELECT k.kategori,
jsonb_pretty(jsonb_agg(
jsonb_build_object(
'id', p.id,
'nama', p.nama,
'ulasan', COALESCE((SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id),
'[]'::jsonb)
) ORDER BY p.id
)) AS dokumen_kategori
FROM (SELECT DISTINCT kategori FROM produk) k
JOIN produk p ON p.kategori = k.kategori
GROUP BY k.kategori
ORDER BY k.kategori;Output yang Dihasilkan:
kategori | jumlah | produk
----------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
olahraga | 3 | [{"id": 1, "nama": "Sepatu Lari Pagi", "harga": 750000.00}, {"id": 2, "nama": "Matras Yoga", "harga": 180000.00}, {"id": 5, "nama": "Botol Minum Stainless", "harga": 95000.00}]
outdoor | 1 | [{"id": 4, "nama": "Tas Ransel Gunung", "harga": 450000.00}]
pakaian | 1 | [{"id": 3, "nama": "Kemeja Batik Kasih", "harga": 320000.00}]
(3 rows)
kategori | dokumen_kategori
----------+------------------------------------------
olahraga | [ +
| { +
| "id": 1, +
| "nama": "Sepatu Lari Pagi", +
| "ulasan": [ +
| { +
| "rating": 5, +
| "pengulas": "Budi" +
| }, +
| { +
| "rating": 4, +
| "pengulas": "Siti" +
| } +
| ] +
| }, +
| { +
| "id": 2, +
| "nama": "Matras Yoga", +
| "ulasan": [ +
| { +
| "rating": 5, +
| "pengulas": "Siti" +
| } +
| ] +
| }, +
| { +
| "id": 5, +
| "nama": "Botol Minum Stainless",+
| "ulasan": [ +
| ] +
| } +
| ]
outdoor | [ +
| { +
| "id": 4, +
| "nama": "Tas Ransel Gunung", +
| "ulasan": [ +
| { +
| "rating": 4, +
| "pengulas": "Dewi" +
| } +
| ] +
| } +
| ]
pakaian | [ +
| { +
| "id": 3, +
| "nama": "Kemeja Batik Kasih", +
| "ulasan": [ +
| { +
| "rating": 3, +
| "pengulas": "Budi" +
| } +
| ] +
| } +
| ]
(3 rows)Query pertama memperlihatkan bentuk dasarnya: satu baris hasil per kategori dengan count(*) dan array dokumen produk yang dibangun jsonb_build_object lalu dilipat jsonb_agg(... ORDER BY id). Query kedua menambahkan satu tingkat lagi — setiap produk di dalam array membawa ulasan miliknya sendiri (dengan COALESCE '[]' sudah terpasang), dan jsonb_pretty menampilkan hasil akhirnya bertingkat: kategori olahraga berisi tiga produk, produk 1 membawa dua ulasan, produk 5 membawa array kosong. Perhatikan bahwa GROUP BY k.kategori membagi hasil per kategori, sementara ORDER BY p.id di dalam jsonb_agg menjaga urutan produk dalam tiap array tetap pasti.
Referensi #
Berkas Sumber
Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.
baris-ke-json.sql
Unduh berkas-- to_jsonb: mengubah SATU nilai/baris menjadi dokumen jsonb.
-- Satu baris tabel menjadi satu objek: kolom jadi kunci,
-- nilai kolom jadi nilai JSON. Urutan kunci mengikuti urutan kolom.
SELECT to_jsonb(p) AS produk_json
FROM produk p
WHERE p.id = 1;
-- row_to_json: pendahulu to_jsonb bertipe json (output rapit satu baris).
SELECT row_to_json(p) AS produk_json_lama
FROM produk p
WHERE p.id = 1;
-- to_jsonb juga bekerja pada nilai biasa, bukan hanya baris tabel:
SELECT to_jsonb(95000::numeric) AS angka,
to_jsonb('Matras Yoga'::text) AS teks;
-- Versi baris dapat dirangkai jadi array lewat jsonb_agg.
SELECT jsonb_agg(to_jsonb(p) ORDER BY p.id) AS semua_produk
FROM produk p;
buat-tabel.sql
Unduh berkas-- Toko Maju Jaya: produk relasional plus tabel ulasan pelanggan.
-- Struktur ini bahan baku semua contoh di chapter ini.
CREATE TABLE produk (
id integer PRIMARY KEY,
nama text NOT NULL,
kategori text NOT NULL,
harga numeric(10,2) NOT NULL
);
CREATE TABLE ulasan (
id integer PRIMARY KEY,
produk_id integer NOT NULL REFERENCES produk(id),
pengulas text NOT NULL,
rating integer NOT NULL,
komentar text
);
INSERT INTO produk (id, nama, kategori, harga) VALUES
(1, 'Sepatu Lari Pagi', 'olahraga', 750000),
(2, 'Matras Yoga', 'olahraga', 180000),
(3, 'Kemeja Batik Kasih', 'pakaian', 320000),
(4, 'Tas Ransel Gunung', 'outdoor', 450000),
(5, 'Botol Minum Stainless', 'olahraga', 95000);
INSERT INTO ulasan (id, produk_id, pengulas, rating, komentar) VALUES
(1, 1, 'Budi', 5, 'Ringan dan nyaman untuk lari pagi.'),
(2, 1, 'Siti', 4, 'Ukuran pas di kaki.'),
(3, 2, 'Siti', 5, 'Empuk dan tidak licin.'),
(4, 3, 'Budi', 3, 'Bahan agak tebal untuk cuaca panas.'),
(5, 4, 'Dewi', 4, 'Kuat, muat banyak.');
-- Produk 5 sengaja tanpa ulasan; kita pakai untuk menguji
-- perilaku jsonb_agg pada himpunan kosong nanti.
SELECT p.id, p.nama, p.kategori, p.harga,
count(u.id) AS jumlah_ulasan
FROM produk p
LEFT JOIN ulasan u ON u.produk_id = p.id
GROUP BY p.id, p.nama, p.kategori, p.harga
ORDER BY p.id;
dokumen-api.sql
Unduh berkas-- Merakit dokumen respons API: produk dengan array ulasan tertanam.
-- jsonb_build_object menyusun objek; jsonb_agg dalam subquery
-- mengumpulkan ulasan terkait menjadi array.
-- Satu produk lengkap, ditampilkan rapat lewat jsonb_pretty.
SELECT jsonb_pretty(jsonb_build_object(
'id', p.id,
'nama', p.nama,
'harga', p.harga,
'rating_rata', (SELECT round(avg(u.rating), 1)
FROM ulasan u
WHERE u.produk_id = p.id),
'ulasan', (SELECT jsonb_agg(
jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating,
'komentar', u.komentar)
ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id)
)) AS dokumen_api
FROM produk p
WHERE p.id = 1;
-- Semua produk sekaligus, versi ringkas tanpa jsonb_pretty.
-- Produk tanpa ulasan mendapat NULL -- perhatikan produk 5.
SELECT p.id,
jsonb_build_object(
'nama', p.nama,
'ulasan', (SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id)
) AS dokumen
FROM produk p
ORDER BY p.id;
-- Perbaikannya: COALESCE mengganti NULL dengan array kosong.
SELECT p.id,
jsonb_build_object(
'nama', p.nama,
'ulasan', COALESCE((SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id),
'[]'::jsonb)
) AS dokumen
FROM produk p
ORDER BY p.id;
group-by-dokumen.sql
Unduh berkas-- GROUP BY + jsonb_agg: dokumen per kategori.
-- Satu baris hasil per kategori, produk-produknya tertanam sebagai array.
SELECT kategori,
count(*) AS jumlah,
jsonb_agg(jsonb_build_object(
'id', id,
'nama', nama,
'harga', harga
) ORDER BY id) AS produk
FROM produk
GROUP BY kategori
ORDER BY kategori;
-- Digabung ulasan: dokumen kategori berisi produk
-- yang masing-masing membawa array ulasannya sendiri.
SELECT k.kategori,
jsonb_pretty(jsonb_agg(
jsonb_build_object(
'id', p.id,
'nama', p.nama,
'ulasan', COALESCE((SELECT jsonb_agg(jsonb_build_object(
'pengulas', u.pengulas,
'rating', u.rating) ORDER BY u.id)
FROM ulasan u
WHERE u.produk_id = p.id),
'[]'::jsonb)
) ORDER BY p.id
)) AS dokumen_kategori
FROM (SELECT DISTINCT kategori FROM produk) k
JOIN produk p ON p.kategori = k.kategori
GROUP BY k.kategori
ORDER BY k.kategori;
jsonb-agg.sql
Unduh berkas-- jsonb_agg: BANYAK baris menjadi SATU array jsonb.
-- Array nilai sederhana: nama semua produk,
-- diurutkan alfabetis dari dalam agregat.
SELECT jsonb_agg(nama ORDER BY nama) AS daftar_nama
FROM produk;
-- Tanpa ORDER BY di dalam agregat, urutan array hanya mengikuti
-- urutan baris yang kebetulan diterima agregat. Di sini subquery
-- mengirim baris terurut nama menurun -- array pun menurun.
-- Jangan andalkan urutan input: tulis ORDER BY di dalam agregat.
SELECT jsonb_agg(nama) AS urutan_masukan
FROM (SELECT nama FROM produk ORDER BY nama DESC) s;
-- Aggregate JSON di SQL biasa: gabungkan dengan agregat numerik
-- dalam satu query GROUP BY.
SELECT kategori,
count(*) AS jumlah,
jsonb_agg(nama ORDER BY id) AS daftar_produk
FROM produk
GROUP BY kategori
ORDER BY kategori;
jsonb-object-agg.sql
Unduh berkas-- jsonb_object_agg(kunci, nilai): BANYAK baris menjadi SATU objek.
-- Tiap baris menyumbang satu pasangan kunci-nilai.
-- Peta kategori -> jumlah produk, dirakit dari hasil GROUP BY.
SELECT jsonb_object_agg(kategori, jumlah) AS produk_per_kategori
FROM (SELECT kategori, count(*) AS jumlah
FROM produk
GROUP BY kategori) s;
-- Peta id -> nama: lookup kecil yang siap dikirim ke aplikasi.
SELECT jsonb_object_agg(id, nama ORDER BY id) AS nama_by_id
FROM produk;
-- jsonb menormalkan urutan kunci (panjang lalu alfabetis),
-- jadi urutan pasangan dari agregat tidak memengaruhi tampilan akhir.