Aggregate

ARRAY_CONCAT_AGG

BigQuery

Menggabungkan beberapa array menjadi satu array. Berguna ketika setiap baris sudah memiliki array dan ingin digabungkan menjadi satu.

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

Syntax

SQL

ARRAY_CONCAT_AGG(expression [ORDER BY key])

Parameter

expressionARRAYwajib

Kolom array yang akan digabungkan

ORDER BYclauseopsional

Mengurutkan array sebelum digabungkan

Contoh Penggunaan

Menggabungkan Array Tags

SQL

1 SELECT
2   category,
3   ARRAY_CONCAT_AGG(tags) as all_tags
4 FROM `project.dataset.products`
5 GROUP BY category;

Menggabungkan semua tags dari produk dalam satu kategori.

Hasil

category	all_tags
Electronics	["gadget", "tech", "smart", "portable"]
Fashion	["style", "trend", "casual", "formal"]

ARRAY_CONCAT_AGG dengan ORDER BY

SQL

1 SELECT
2   user_id,
3   ARRAY_CONCAT_AGG(daily_activities ORDER BY activity_date) as all_activities
4 FROM `project.dataset.user_logs`
5 GROUP BY user_id;

Menggabungkan aktivitas harian per user secara kronologis.

Hasil

user_id	all_activities
U001	["login", "view", "purchase", "logout", ...]

Flatten Nested Array

SQL

1 SELECT
2   store_id,
3   ARRAY_CONCAT_AGG(product_ids) as all_product_ids,
4   ARRAY_LENGTH(ARRAY_CONCAT_AGG(product_ids)) as total_products
5 FROM `project.dataset.store_inventory`
6 GROUP BY store_id;

Menggabungkan semua product_ids dari berbagai shipment.

Hasil

store_id	all_product_ids	total_products
S001	[101, 102, 201, 202]	4
S002	[101, 301, 302, 303]	4

Kembali ke BigQuery Functions

1	SELECT
2	category,
3	ARRAY_CONCAT_AGG(tags) as all_tags
4	FROM `project.dataset.products`
5	GROUP BY category;

1	SELECT
2	user_id,
3	ARRAY_CONCAT_AGG(daily_activities ORDER BY activity_date) as all_activities
4	FROM `project.dataset.user_logs`
5	GROUP BY user_id;

1	SELECT
2	store_id,
3	ARRAY_CONCAT_AGG(product_ids) as all_product_ids,
4	ARRAY_LENGTH(ARRAY_CONCAT_AGG(product_ids)) as total_products
5	FROM `project.dataset.store_inventory`
6	GROUP BY store_id;