ARRAY_AGG / ARRAY_CONSTRUCT

Category: Array Functions

Platform Support:

✅ Snowflake (ARRAY_AGG, ARRAY_CONSTRUCT) | ✅ BigQuery (ARRAY_AGG) | ✅ Databricks (COLLECT_LIST, ARRAY)

Description

Aggregates values into arrays. ARRAY_AGG collects values from multiple rows into a single array (aggregate function), while ARRAY_CONSTRUCT creates an array from multiple columns or values (scalar function). Essential for working with nested data structures and creating complex data types.

Syntax by Platform

Snowflake:

-- Aggregate function
ARRAY_AGG(expression) [WITHIN GROUP (ORDER BY ...)] 

-- Scalar function
ARRAY_CONSTRUCT(value1, value2, ...)

BigQuery:

-- Aggregate function
ARRAY_AGG(expression [ORDER BY ...] [LIMIT n])

-- Scalar function (literal syntax)
[value1, value2, ...]

Databricks:

-- Aggregate function
COLLECT_LIST(expression)  -- with duplicates
COLLECT_SET(expression)   -- distinct values

-- Scalar function
ARRAY(value1, value2, ...)

Platform-Specific Notes

Snowflake:

• ARRAY_AGG preserves order with WITHIN GROUP
• ARRAY_CONSTRUCT for creating arrays from columns
• Supports nested arrays (ARRAY of ARRAY)
• ARRAY_UNIQUE_AGG for distinct values

BigQuery:

• ARRAY_AGG can include ORDER BY and LIMIT
• ARRAY_AGG(DISTINCT ...) for unique values
• Literal syntax with square brackets
• ARRAY_CONCAT_AGG for flattening arrays

Databricks:

• COLLECT_LIST keeps duplicates
• COLLECT_SET removes duplicates
• ARRAY function for literals
• Compatible with Spark SQL

Example 1: ARRAY_AGG - Group Related Items

Snowflake:

SELECT 
    customer_id,
    customer_name,
    ARRAY_AGG(order_id) WITHIN GROUP (ORDER BY order_date) as order_ids,
    ARRAY_AGG(order_total) WITHIN GROUP (ORDER BY order_date) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id, customer_name;

BigQuery:

SELECT 
    customer_id,
    customer_name,
    ARRAY_AGG(order_id ORDER BY order_date) as order_ids,
    ARRAY_AGG(order_total ORDER BY order_date) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id, customer_name;

Databricks:

SELECT 
    customer_id,
    customer_name,
    COLLECT_LIST(order_id) as order_ids,
    COLLECT_LIST(order_total) as order_totals,
    COUNT(*) as order_count,
    SUM(order_total) as total_spent
FROM orders
GROUP BY customer_id, customer_name;

Sample Data (orders table):

Result:

Example 2: ARRAY_CONSTRUCT - Create Arrays from Columns

Snowflake:

SELECT 
    product_id,
    product_name,
    ARRAY_CONSTRUCT(price, cost, msrp) as price_array,
    ARRAY_CONSTRUCT(category, subcategory, brand) as taxonomy_array,
    ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('type', 'price', 'value', price),
        OBJECT_CONSTRUCT('type', 'cost', 'value', cost),
        OBJECT_CONSTRUCT('type', 'msrp', 'value', msrp)
    ) as price_objects
FROM products;

BigQuery:

SELECT 
    product_id,
    product_name,
    [price, cost, msrp] as price_array,
    [category, subcategory, brand] as taxonomy_array,
    [
        STRUCT('price' as type, price as value),
        STRUCT('cost' as type, cost as value),
        STRUCT('msrp' as type, msrp as value)
    ] as price_objects
FROM products;

Databricks:

SELECT 
    product_id,
    product_name,
    ARRAY(price, cost, msrp) as price_array,
    ARRAY(category, subcategory, brand) as taxonomy_array,
    ARRAY(
        STRUCT('price' as type, price as value),
        STRUCT('cost' as type, cost as value),
        STRUCT('msrp' as type, msrp as value)
    ) as price_objects
FROM products;

Sample Data (products table):

Result:

Example 3: Aggregate with DISTINCT

Snowflake:

SELECT 
    product_id,
    product_name,
    ARRAY_AGG(DISTINCT customer_id) as unique_customers,
    ARRAY_AGG(DISTINCT region) WITHIN GROUP (ORDER BY region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id, product_name;

BigQuery:

SELECT 
    product_id,
    product_name,
    ARRAY_AGG(DISTINCT customer_id) as unique_customers,
    ARRAY_AGG(DISTINCT region ORDER BY region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id, product_name;

Databricks:

SELECT 
    product_id,
    product_name,
    COLLECT_SET(customer_id) as unique_customers,
    COLLECT_SET(region) as regions_sold,
    COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY product_id, product_name;

Sample Data (sales table):

Result:

Example 4: Nested Arrays for Hierarchical Data

All Platforms:

WITH order_items AS (
    SELECT 
        order_id,
        ARRAY_AGG(  -- or COLLECT_LIST for Databricks
            STRUCT(
                product_id,
                product_name,
                quantity,
                unit_price,
                quantity * unit_price as line_total
            ) ORDER BY product_id
        ) as items
    FROM order_line_items
    GROUP BY order_id
)
SELECT 
    o.customer_id,
    o.customer_name,
    ARRAY_AGG(
        STRUCT(
            o.order_id,
            o.order_date,
            o.order_total,
            oi.items
        ) ORDER BY o.order_date
    ) as orders
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.customer_name;

Result Structure:

{
  "customer_id": 101,
  "customer_name": "John Smith",
  "orders": [
    {
      "order_id": 1001,
      "order_date": "2024-01-15",
      "order_total": 250.00,
      "items": [
        {"product_id": 1, "product_name": "Mouse", "quantity": 2, "unit_price": 25.00, "line_total": 50.00},
        {"product_id": 2, "product_name": "Keyboard", "quantity": 1, "unit_price": 200.00, "line_total": 200.00}
      ]
    }
  ]
}

Example 5: Array Operations

Snowflake:

SELECT 
    customer_id,
    tags,
    ARRAY_SIZE(tags) as tag_count,
    ARRAY_CONTAINS('VIP'::VARIANT, tags) as is_vip,
    ARRAY_SLICE(tags, 0, 2) as first_two_tags,
    ARRAY_APPEND(tags, 'NEW') as tags_with_new,
    ARRAY_CAT(tags, ARRAY_CONSTRUCT('EXTRA1', 'EXTRA2')) as extended_tags
FROM customer_tags;

BigQuery:

SELECT 
    customer_id,
    tags,
    ARRAY_LENGTH(tags) as tag_count,
    'VIP' IN UNNEST(tags) as is_vip,
    ARRAY(SELECT tag FROM UNNEST(tags) tag LIMIT 2) as first_two_tags,
    ARRAY_CONCAT(tags, ['NEW']) as tags_with_new,
    ARRAY_CONCAT(tags, ['EXTRA1', 'EXTRA2']) as extended_tags
FROM customer_tags;

Databricks:

SELECT 
    customer_id,
    tags,
    SIZE(tags) as tag_count,
    ARRAY_CONTAINS(tags, 'VIP') as is_vip,
    SLICE(tags, 1, 2) as first_two_tags,
    ARRAY_APPEND(tags, 'NEW') as tags_with_new,
    CONCAT(tags, ARRAY('EXTRA1', 'EXTRA2')) as extended_tags
FROM customer_tags;

Example 6: STRING_AGG Alternative

Snowflake:

SELECT 
    department,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) as employees_list,
    ARRAY_AGG(employee_name) WITHIN GROUP (ORDER BY employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY department;

BigQuery:

SELECT 
    department,
    STRING_AGG(employee_name, ', ' ORDER BY employee_name) as employees_list,
    ARRAY_AGG(employee_name ORDER BY employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY department;

Databricks:

SELECT 
    department,
    CONCAT_WS(', ', COLLECT_LIST(employee_name)) as employees_list,
    COLLECT_LIST(employee_name) as employees_array,
    COUNT(*) as employee_count
FROM employees
GROUP BY department;

Common Array Functions by Platform

Common Use Cases

1. Denormalization: Aggregate child records into parent
2. JSON generation: Create complex nested structures
3. Tag management: Store multiple tags per entity
4. Event tracking: Collect event sequences per user
5. Product catalogs: Group variants and options
6. Data export: Prepare hierarchical data for APIs

Performance Considerations

• Large arrays can impact query performance
• Consider array size limits (varies by platform)
• Flattening arrays for analysis can be expensive
• Index considerations for array columns
• Memory usage grows with array size

Best Practices

1. Limit array size: Keep arrays reasonably small
2. Order matters: Use ORDER BY in ARRAY_AGG for consistency
3. Handle NULLs: Arrays can contain NULL values
4. Document structure: Explain nested array schemas
5. Consider alternatives: Sometimes normalized tables are better
6. Test performance: Benchmark array operations on your data