JSON

JSON_AGG

PostgreSQL

Mengagregasi nilai menjadi array JSON. Sangat berguna untuk membuat nested data atau mengkonversi hasil query menjadi format JSON.

Tipe hasil: JSON

Syntax

SQL

JSON_AGG(expression [ORDER BY ...])

expressionANYwajib

Nilai atau ekspresi yang akan diagregasi menjadi array JSON

ORDER BYclauseopsional

Menentukan urutan elemen dalam array (opsional)

SQL

1 SELECT 
2   department,
3   JSON_AGG(employee_name) AS employees
4 FROM employees
5 GROUP BY department;

Membuat array JSON berisi nama karyawan per departemen.

SQL

1 SELECT 
2   category,
3   JSON_AGG(
4     JSON_BUILD_OBJECT('name', name, 'price', price)
5   ) AS products
6 FROM products
7 GROUP BY category;

Membuat array JSON berisi objek produk per kategori.

JSON_ARRAYAGG

MySQL

ARRAY_AGG

Snowflake

JSON_GROUP_ARRAY

DuckDB

1	SELECT
2	department,
3	JSON_AGG(employee_name) AS employees
4	FROM employees
5	GROUP BY department;

1	SELECT
2	category,
3	JSON_AGG(
4	JSON_BUILD_OBJECT('name', name, 'price', price)
5	) AS products
6	FROM products
7	GROUP BY category;