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;

Monetization 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

Monetization 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;

Monetization 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;

Monetization Strategies

Path A: Data Report SaaS

Package STRING_AGG and other aggregation functions into automated report services:

• Input: Customer ID list
• Output: Purchase report PDF/HTML
• Pricing: $29/month/enterprise, 50 enterprises = $1,450/month

Path B: API Data Product

Wrap aggregation results into REST API:

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/customer/{customer_id}/profile")
def get_customer_profile(customer_id: int):
    con = duckdb.connect("orders.duckdb")
    result = con.execute("""
        SELECT 
            STRING_AGG(product_name, ', ') AS products,
            MAP_AGG(product_name, price) AS prices,
            SUM(price) AS total_spent
        FROM order_items
        WHERE customer_id = ?
        GROUP BY customer_id
    """, [customer_id]).fetchone()
    return {"customer_id": customer_id, "profile": result}

• Pricing: $0.01/call, 1M calls = $10,000/month

Path C: Data Cleaning Service

Provide data cleaning services for e-commerce clients, using ARRAY_AGG + MAP_AGG to quickly build user profiles:

• Project fee: $500-2000/project
• 5 projects/month = $2,500-10,000/month

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.