Aggregate

ARRAY_AGG

BigQuery

Collects values from multiple rows into an ARRAY. A powerful BigQuery function for building nested data structures.

Tipe hasil: ARRAY<T>Diperbarui: 13 Jun 2026

Syntax

SQL

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

Parameter

expressionanywajib

A column or expression to collect into an array

DISTINCTmodifieropsional

Collect only unique values

IGNORE NULLSmodifieropsional

Exclude NULL values (default behavior)

RESPECT NULLSmodifieropsional

Include NULL values in the array

ORDER BYclauseopsional

Sort elements within the array

LIMITclauseopsional

Limit the number of elements in the array

Contoh Penggunaan

Collect Values into an 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;

Creates an array of product IDs per customer.

Hasil

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

ARRAY_AGG with ORDER BY and 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;

Retrieves the top 3 best-selling products per category.

Hasil

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

ARRAY_AGG with 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;

Creates an array of structs for the top 5 highest-paid employees.

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;

Collects unique categories visited by each user.

Hasil

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

Pertanyaan Umum tentang ARRAY_AGG

Apa itu fungsi ARRAY_AGG di BigQuery?

Collects values from multiple rows into an ARRAY. A powerful BigQuery function for building nested data structures. Di BigQuery, fungsi ARRAY_AGG termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.

Bagaimana cara menggunakan ARRAY_AGG di BigQuery?

Gunakan sintaks berikut: ARRAY_AGG([DISTINCT] expression [IGNORE NULLS | RESPECT NULLS] [ORDER BY key] [LIMIT n]). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.

Apa nilai yang dikembalikan oleh fungsi ARRAY_AGG?

Fungsi ARRAY_AGG mengembalikan nilai bertipe ARRAY<T>. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda — selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.

Apa saja parameter fungsi ARRAY_AGG?

Fungsi ini memiliki 1 parameter wajib dan 5 parameter opsional. Parameter yang digunakan: expression (any, wajib): A column or expression to collect into an array; DISTINCT (modifier, opsional): Collect only unique values; IGNORE NULLS (modifier, opsional): Exclude NULL values (default behavior); RESPECT NULLS (modifier, opsional): Include NULL values in the array; ORDER BY (clause, opsional): Sort elements within the array; LIMIT (clause, opsional): Limit the number of elements in the array.

Fungsi Terkait

STRING_AGGARRAY_CONCAT

Ngulik Langsung

Sudah paham ARRAY_AGG? Latih langsung di browser

Latihan interaktif, langsung di browser.

Latihan Langsung →

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;