Merupakan fungsi bawaan database PostgreSQL 9.2 dan versi selanjutnya yang dapat menghasikan format JSON dengan menjalankan sebuah query.
Fungsi row_to_json
| Function | Description | Example | Example Result |
|---|---|---|---|
| rowtojson(record [, pretty_bool]) | Returns the row as a JSON object. Line feeds will be added between level-1 elements if pretty_bool is true. | rowtojson(row(1,‘foo’)) | {“f1”:1,“f2”:“foo”} |
Contoh Penggunaan
# Tabel author
id | name
----+-------
1 | Jaka
2 | Ahmad
3 | Putri
4 | Dani
(4 rows)# Tabel books
id | author_id | title
----+-----------+---------------
1 | 1 | Buku Jaka v1
2 | 1 | Buku Jaka v2
3 | 2 | Buku Ahmad v1
4 | 4 | Buku Putri v1
5 | 4 | Buku Putri v2
6 | 4 | Buku Putri v3
7 | 4 | Buku Putri v4
(7 rows)- Merubah setiap row (baris) menjadi json object
Query:
select row_to_json(author) as json from author;Hasil:
json
-------------------------
{"id":1,"name":"Jaka"}
{"id":2,"name":"Ahmad"}
{"id":3,"name":"Putri"}
{"id":4,"name":"Dani"}
(4 rows)- Merubah setiap row (baris) menjadi json object lalu memasukannya ke dalam array
Query:
select array_to_json(array_agg(baris)) as json
from (
select id, name from author
) baris;Hasil:
json
-------------------------------------------------------------------------------------------------
[{"id":1,"name":"Jaka"},{"id":2,"name":"Ahmad"},{"id":3,"name":"Putri"},{"id":4,"name":"Dani"}]
(1 row)Format:
[{
"id": 1,
"name": "Jaka"
}, {
"id": 2,
"name": "Ahmad"
}, {
"id": 3,
"name": "Putri"
}, {
"id": 4,
"name": "Dani"
}]- Contoh yang lebih kompleks
Query:
select row_to_json(baris) as json
from (
select name as author, (
select array_to_json(array_agg(j))
from (
select title
from books
where books.author_id=author.id
) j
) as books
from author
where id=4
) baris;Hasil:
json
-------------------------------------------------------------------------------------------------------------------------------------
{"author":"Dani","books":[{"title":"Buku Putri v1"},{"title":"Buku Putri v2"},{"title":"Buku Putri v3"},{"title":"Buku Putri v4"}]}
(1 row)Format:
{
"author": "Dani",
"books": [{
"title": "Buku Putri v1"
}, {
"title": "Buku Putri v2"
}, {
"title": "Buku Putri v3"
}, {
"title": "Buku Putri v4"
}]
}