Salah satu kelebihan dari PostgreSQL adalah memiliki tipe data array dan json. Di tutorial yang bersumber dari link di atas, kita akan menggunakan keuntungan ini untuk menyusun object JSON sebelum di return ke client.
Fungsi2 yang akan kita gunakan untuk memudahkan operasi relasi antar tabel adalah:
1. row_to_json()
2. json_agg()
Pertama kali kita akan persiapkan dua tabel yang akan digunakan, yaitu tabel artists dan tabel albums.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE public.artists ( id serial4 NOT NULL, "name" varchar(120) NULL, CONSTRAINT pk_artists PRIMARY KEY (id) ); INSERT INTO public.artists (id, "name") VALUES(1, 'Peterpan'); INSERT INTO public.artists (id, "name") VALUES(2, 'Padi'); INSERT INTO public.artists (id, "name") VALUES(3, 'Sheila On 7'); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE TABLE public.albums ( id serial4 NOT NULL, title varchar(160) NOT NULL, artist_id int4 NOT NULL, CONSTRAINT pk_albums PRIMARY KEY (id) ); INSERT INTO public.albums (id, title, artist_id) VALUES(1, 'Taman Langit', 1); INSERT INTO public.albums (id, title, artist_id) VALUES(2, 'Bintang di Surga', 1); INSERT INTO public.albums (id, title, artist_id) VALUES(3, 'Hari yang Cerah', 1); INSERT INTO public.albums (id, title, artist_id) VALUES(4, 'Lain Dunia', 2); INSERT INTO public.albums (id, title, artist_id) VALUES(5, 'Sesuatu yang Tertunda', 2); INSERT INTO public.albums (id, title, artist_id) VALUES(6, 'Save my Soul', 2); INSERT INTO public.albums (id, title, artist_id) VALUES(7, 'Sheila on 7', 3); INSERT INTO public.albums (id, title, artist_id) VALUES(8, 'Kisah Klasik untuk Masa Depan', 3); INSERT INTO public.albums (id, title, artist_id) VALUES(9, '07 Des', 3); |
row_to_json()
Fungsi row_to_json() akan mengambil seluruh row data dan menjadikannya JSON object di masing-masing row.
|
1 2 3 4 |
select row_to_json(artists) from ( select * from artists ) as artists |
Tentu saja kita dapat melakukan filtering, misalnya hanya ingin menampilkan artist yang id = 3.
json_agg()
Kita dapat menggunakan json_agg() untuk melakukan aggregate pada tiap rows menjadi JSON Array. Misalnya kita ingin melakukan query untuk album yang dimiliki oleh artist dengan id = 3.
|
1 2 3 4 |
select json_agg(albums) as result from ( select * from albums where artist_id = 3 ) as albums; |
Hasilnya:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[{ "id": 7, "title": "Sheila on 7", "artist_id": 3 }, { "id": 8, "title": "Kisah Klasik untuk Masa Depan", "artist_id": 3 }, { "id": 9, "title": "07 Des", "artist_id": 3 } ] |
Selanjutnya kita juga bisa menggunakan json_agg untuk menampilkan seluruh artis beserta album yang dimilikinya:
|
1 2 3 4 5 6 7 8 9 |
select id,name, row_to_json(art) as artists from( select a.id, a.name, (select json_agg(alb) from ( select * from albums where artist_id = a.id ) as alb ) as albums from artists as a) art; |
Hasilnya:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "id": 1, "name": "Peterpan", "albums": [{ "id": 1, "title": "Taman Langit", "artist_id": 1 }, { "id": 2, "title": "Bintang di Surga", "artist_id": 1 }, { "id": 3, "title": "Hari yang Cerah", "artist_id": 1 } ] } |