JSON

JSONB_AGG

PostgreSQL

Aggregate function yang mengumpulkan nilai menjadi JSON array. Seperti ARRAY_AGG tapi hasilnya JSONB array.

Tipe hasil: jsonb

Syntax

SQL

JSONB_AGG(expression)
JSONB_AGG(expression ORDER BY sort_expression)

Parameter

expressionanywajib

Nilai yang akan dikumpulkan ke array

ORDER BYclauseopsional

Opsional. Menentukan urutan elemen dalam array

Contoh Penggunaan

Aggregate ke JSON Array

SQL

1 SELECT 
2   category,
3   JSONB_AGG(name) AS products
4 FROM products
5 GROUP BY category;

Mengumpulkan nama produk per kategori ke JSON array.

Hasil

category	products
Electronics	["Laptop", "Phone", "Tablet"]
Clothing	["Shirt", "Pants"]

Aggregate Objects

SQL

1 SELECT 
2   category,
3   JSONB_AGG(
4     JSONB_BUILD_OBJECT(id, id, name, name, price, price)
5     ORDER BY price DESC
6   ) AS products
7 FROM products
8 GROUP BY category;

Aggregate objects dengan urutan tertentu.

Hasil

category	products
Electronics	[{"id": 1, "name": "Laptop", "price": ...}]

Build API Response

SQL

1 SELECT JSONB_BUILD_OBJECT(
2   items, JSONB_AGG(TO_JSONB(t.*)),
3   count, COUNT(*)
4 ) AS response
5 FROM (SELECT id, name FROM products LIMIT 10) t;

Membuat response API dengan array of objects.

Fungsi Equivalen di Database Lain

JSON_ARRAYAGG

MySQL

ARRAY_AGG

Snowflake

JSON_GROUP_ARRAY

DuckDB

Fungsi Terkait

STRING_AGGARRAY_AGGJSON_AGGJSON_OBJECT_AGGJSONB_OBJECT_AGG

Kembali ke PostgreSQL Functions

1	SELECT
2	category,
3	JSONB_AGG(name) AS products
4	FROM products
5	GROUP BY category;

1	SELECT
2	category,
3	JSONB_AGG(
4	JSONB_BUILD_OBJECT(id, id, name, name, price, price)
5	ORDER BY price DESC
6	) AS products
7	FROM products
8	GROUP BY category;

1	SELECT JSONB_BUILD_OBJECT(
2	items, JSONB_AGG(TO_JSONB(t.*)),
3	count, COUNT(*)
4	) AS response
5	FROM (SELECT id, name FROM products LIMIT 10) t;