JSON

JSONB_OBJECT_AGG

PostgreSQL

Aggregate function yang membangun JSON object dari pasangan key-value. Setiap row memberikan satu key-value pair ke object hasil.

Tipe hasil: jsonb

Syntax

SQL

JSONB_OBJECT_AGG(key, value)

keytextwajib

Kolom yang menjadi key

valueanywajib

Kolom yang menjadi value

SQL

1 SELECT JSONB_OBJECT_AGG(key, value)
2 FROM (
3   VALUES (name, John), (age, 30), (city, Jakarta)
4 ) AS t(key, value);

Mengubah rows key-value menjadi single object.

Hasil

jsonb_object_agg
{"name": "John", "age": "30", "city": "Jakarta"}

SQL

1 SELECT 
2   user_id,
3   JSONB_OBJECT_AGG(setting_name, setting_value) AS settings
4 FROM user_settings
5 GROUP BY user_id;

Pivot settings menjadi JSON object per user.

Hasil

user_id	settings
1	{"theme": "dark", "language": "id", ...}

SQL

1 SELECT 
2   product_id,
3   JSONB_OBJECT_AGG(attribute_name, attribute_value) AS attributes
4 FROM product_attributes
5 GROUP BY product_id;

Mengkonversi dynamic attributes menjadi JSON object.

JSON_OBJECTAGG

MySQL

OBJECT_AGG

Snowflake

1	SELECT JSONB_OBJECT_AGG(key, value)
2	FROM (
3	VALUES (name, John), (age, 30), (city, Jakarta)
4	) AS t(key, value);

1	SELECT
2	user_id,
3	JSONB_OBJECT_AGG(setting_name, setting_value) AS settings
4	FROM user_settings
5	GROUP BY user_id;

1	SELECT
2	product_id,
3	JSONB_OBJECT_AGG(attribute_name, attribute_value) AS attributes
4	FROM product_attributes
5	GROUP BY product_id;