Aggregate

ARRAY_AGG

BigQuery

Mengumpulkan nilai dari beberapa baris menjadi sebuah ARRAY. Fungsi powerful di BigQuery untuk membuat nested data structures.

Tipe hasil: ARRAY<T>Diperbarui: 7 Jan 2026

Syntax

SQL

ARRAY_AGG([DISTINCT] expression [IGNORE NULLS | RESPECT NULLS] [ORDER BY key] [LIMIT n])

Parameter

expressionanywajib

Kolom atau ekspresi yang akan dikumpulkan ke dalam array

DISTINCTmodifieropsional

Hanya kumpulkan nilai unik

IGNORE NULLSmodifieropsional

Abaikan nilai NULL (default behavior)

RESPECT NULLSmodifieropsional

Sertakan nilai NULL dalam array

ORDER BYclauseopsional

Mengurutkan elemen dalam array

LIMITclauseopsional

Membatasi jumlah elemen dalam array

Contoh Penggunaan

Mengumpulkan Nilai ke Array

SQL

1 SELECT
2   customer_id,
3   ARRAY_AGG(product_id) as product_ids
4 FROM `project.dataset.orders`
5 GROUP BY customer_id
6 LIMIT 3;

Membuat array product_id per customer.

Hasil

customer_id	product_ids
C001	[101, 205, 342]
C002	[101, 156]
C003	[205, 342, 401, 502]

ARRAY_AGG dengan ORDER BY dan LIMIT

SQL

1 SELECT
2   category,
3   ARRAY_AGG(product_name ORDER BY sales_count DESC LIMIT 3) as top_3_products
4 FROM `project.dataset.products`
5 GROUP BY category;

Mengambil 3 produk terlaris per kategori.

Hasil

category	top_3_products
Electronics	["iPhone", "MacBook", "iPad"]
Fashion	["Nike Shoes", "Levi's Jeans", "Zara Dress"]

ARRAY_AGG dengan STRUCT

SQL

1 SELECT
2   department,
3   ARRAY_AGG(
4     STRUCT(employee_name, salary, hire_date)
5     ORDER BY salary DESC
6     LIMIT 5
7   ) as top_paid_employees
8 FROM `project.dataset.employees`
9 GROUP BY department;

Membuat array of structs untuk top 5 karyawan bergaji tertinggi.

Hasil

department	top_paid_employees
Engineering	[{name: "Alice", salary: 25M, ...}, ...]

ARRAY_AGG DISTINCT

SQL

1 SELECT
2   user_id,
3   ARRAY_AGG(DISTINCT category ORDER BY category) as unique_categories
4 FROM `project.dataset.user_activity`
5 GROUP BY user_id;

Mengumpulkan kategori unik yang dikunjungi user.

Hasil

user_id	unique_categories
U001	["Books", "Electronics", "Sports"]
U002	["Fashion", "Food", "Home"]

Fungsi Terkait

STRING_AGGARRAY_CONCAT

Kembali ke BigQuery Functions

1	SELECT
2	customer_id,
3	ARRAY_AGG(product_id) as product_ids
4	FROM `project.dataset.orders`
5	GROUP BY customer_id
6	LIMIT 3;

1	SELECT
2	category,
3	ARRAY_AGG(product_name ORDER BY sales_count DESC LIMIT 3) as top_3_products
4	FROM `project.dataset.products`
5	GROUP BY category;

1	SELECT
2	department,
3	ARRAY_AGG(
4	STRUCT(employee_name, salary, hire_date)
5	ORDER BY salary DESC
6	LIMIT 5
7	) as top_paid_employees
8	FROM `project.dataset.employees`
9	GROUP BY department;

1	SELECT
2	user_id,
3	ARRAY_AGG(DISTINCT category ORDER BY category) as unique_categories
4	FROM `project.dataset.user_activity`
5	GROUP BY user_id;