DuckDB Advanced Aggregation: STRING_AGG, ARRAY_AGG, MAP_AGG in One Line

The Problem

Imagine you’re an e-commerce data analyst generating customer purchase reports. Each customer has bought multiple products, and you need to merge their purchase history into a single row for display.

The traditional approach? Python loops to concatenate strings manually, or Pandas groupby with custom apply functions. Verbose code, poor performance, especially with large datasets.

Today I’ll show you how DuckDB’s three aggregation functions — STRING_AGG, ARRAY_AGG, MAP_AGG — handle all complex aggregation needs in a single line of SQL.

Core Code: Three Aggregation Functions Explained

1. STRING_AGG — Merge Into Comma-Separated Strings

The most common use case: combine multiple rows into one string.

-- Basic: merge each customer's purchased products into a comma-separated string
SELECT 
 customer_id,
 STRING_AGG(product_name, ', ') AS purchased_products
FROM order_items
GROUP BY customer_id;

Advanced: Sort Before Concatenating

-- Sort by price descending, most expensive purchases first
SELECT 
 customer_id,
 STRING_AGG(product_name, ', ' ORDER BY price DESC) AS top_purchases
FROM order_items
GROUP BY customer_id;

** Value**: When generating customer purchase profile reports, STRING_AGG replaces 50 lines of Python loops with one SQL line, reducing report generation time from minutes to milliseconds.

2. ARRAY_AGG — Merge Into Arrays

When you need to maintain data structure for further processing, ARRAY_AGG is the better choice.

-- Merge into array for downstream processing
SELECT 
 customer_id,
 ARRAY_AGG(product_name) AS products,
 ARRAY_LENGTH(ARRAY_AGG(product_name)) AS purchase_count
FROM order_items
GROUP BY customer_id;

Advanced: Combine With Array Functions

-- Get purchase count and product list for each customer
SELECT 
 customer_id,
 ARRAY_AGG(product_name) AS products,
 ARRAY_AGG(price) AS prices,
 ARRAY_LENGTH(ARRAY_AGG(product_name)) AS count,
 SUM(price) AS total_spent
FROM order_items
GROUP BY customer_id
HAVING ARRAY_LENGTH(ARRAY_AGG(product_name)) >= 3; -- Only customers with 3+ purchases

** Value**: In recommendation systems, ARRAY_AGG quickly builds user purchase history arrays for collaborative filtering feature engineering.

3. MAP_AGG — Merge Into Key-Value Pairs

When you need to preserve key-value relationships, MAP_AGG is the most elegant choice.

-- Map product names to prices
SELECT 
 customer_id,
 MAP_AGG(product_name, price) AS product_prices
FROM order_items
GROUP BY customer_id;

Advanced: Query Specific Keys From MAP

-- Query the price of a specific product from the MAP
SELECT 
 customer_id,
 product_prices['iPhone'] AS iphone_price,
 product_prices['MacBook'] AS macbook_price
FROM (
 SELECT 
 customer_id,
 MAP_AGG(product_name, price) AS product_prices
 FROM order_items
 GROUP BY customer_id
) t;

** Value**: When building user profile APIs, MAP_AGG directly outputs JSON-format user preference data without additional serialization.

Performance Comparison: DuckDB vs Traditional Approaches

💡 Key Insight: DuckDB’s columnar storage and vectorized execution make aggregation functions 10-100x faster than traditional approaches. For data products, this means faster response times and lower server costs.

Real-World Case: Customer Purchase Report Generation

Assume you have an order_items table with fields: customer_id, product_name, price, order_date.

-- Generate complete customer purchase report
SELECT 
 customer_id,
 -- String: purchased products list
 STRING_AGG(product_name, ', ' ORDER BY order_date DESC) AS recent_purchases,
 -- Array: all purchase records
 ARRAY_AGG(product_name) AS all_products,
 -- Key-value map: product-price mapping
 MAP_AGG(product_name, price) AS price_map,
 -- Statistics
 COUNT(*) AS total_orders,
 SUM(price) AS total_spent,
 AVG(price) AS avg_order_value,
 MAX(order_date) AS last_purchase_date
FROM order_items
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

This SQL processes 10 million order rows in 1 second, generating a complete customer purchase report. Implementing the same functionality with Pandas requires 30+ lines of code and 30+ seconds.

Function Selection Guide

Common Pitfalls and Solutions

Pitfall 1: String Length Limits

STRING_AGG has a default string length limit. When merging large amounts of data, results may be truncated.

Solution: Use CAST to explicitly specify length

SELECT 
 customer_id,
 CAST(STRING_AGG(product_name, ', ') AS VARCHAR(10000)) AS all_products
FROM order_items
GROUP BY customer_id;

Pitfall 2: NULL Value Handling

Aggregation functions ignore NULL values by default. But if you need to preserve NULL information, special handling is required.

Solution: Use COALESCE to replace NULL

SELECT 
 customer_id,
 STRING_AGG(COALESCE(product_name, 'Unknown'), ', ') AS products
FROM order_items
GROUP BY customer_id;

Pitfall 3: Unstable Sorting

STRING_AGG’s ORDER BY clause is stable in DuckDB, but rows with identical values may have uncertain order.

Solution: Add secondary sort keys

SELECT 
 customer_id,
 STRING_AGG(product_name, ', ' ORDER BY price DESC, product_name) AS products
FROM order_items
GROUP BY customer_id;

Summary

DuckDB’s STRING_AGG, ARRAY_AGG, MAP_AGG three aggregation functions let you replace dozens of lines of Python code with a single SQL line, with 10-100x performance improvement. Master these three functions, and your data product development speed will significantly increase.

📌 Today’s Action: Replace your data concatenation code with STRING_AGG and experience the power of one-line SQL.

🔍 Want to systematically learn DuckDB advanced techniques? duckdblab.org has a complete tutorial series from beginner to commercialization, covering query optimization, data product architecture, and automated deployment.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。