SQL Arrays, JSON, and Advanced Aggregation Functions: A Deep Dive

In modern applications, data rarely fits neatly into simple rows and columns. You often encounter arrays of tags, JSON payloads from APIs, or need to aggregate data into complex structures for reporting. SQL databases like PostgreSQL have evolved to handle these scenarios natively with arrays, JSON support, and advanced aggregation functions.

Imagine you're building an e-commerce platform. Products have multiple categories (array), customer preferences are stored as JSON, and you need to generate a sales report that groups orders by region and product category, with subtotals and percentages. Basic GROUP BY and SUM won't cut it—you need advanced aggregation.

This tutorial will take you from basic array usage to sophisticated JSON queries and aggregation functions that go beyond simple COUNT and SUM. You'll learn how to create arrays from query results, parse JSON documents, and use advanced aggregation features like FILTER, ORDER BY within aggregates, and ordered-set aggregates. We'll also cover performance considerations and real-world debugging scenarios.

By the end, you'll be able to handle complex data structures directly in SQL, reducing the need for application-level processing and making your queries more powerful and efficient.

1. SQL Arrays: Creation and Basic Operations

SQL arrays are ordered collections of elements of the same data type. PostgreSQL supports arrays natively, allowing you to store multiple values in a single column. You can create arrays using array constructors or by aggregating values from rows.

Creating arrays from literals: ``sql SELECT ARRAY[1, 2, 3] AS numbers; SELECT ARRAY['apple', 'banana', 'cherry'] AS fruits; ``

Creating arrays from query results with array_agg(): array_agg() is an aggregate function that collects values into an array. For example, to get all product categories for each order:

``sql SELECT order_id, array_agg(product_name) AS products FROM order_items GROUP BY order_id; ``

Accessing array elements: Use subscript notation with 1-based indexing: ``sql SELECT (ARRAY['a', 'b', 'c'])[2]; -- returns 'b' ``

Array functions: - array_length(array, dimension) - returns length - unnest(array) - expands array into rows - array_remove(array, element) - removes all occurrences - array_cat(array1, array2) - concatenates arrays

Example: Using unnest to flatten arrays for aggregation: ``sql SELECT order_id, unnest(products) AS product FROM ( SELECT order_id, array_agg(product_name) AS products FROM order_items GROUP BY order_id ) sub; ``

Production insight: Be cautious with array_agg() on large datasets—it can consume significant memory. Use ORDER BY within the aggregate to control element order: array_agg(col ORDER BY col). Also, consider using array_agg(DISTINCT col) to avoid duplicates.

-- Create an array from a query
SELECT department_id,
       array_agg(employee_name ORDER BY hire_date) AS employees
FROM employees
GROUP BY department_id;

-- Unnest to get individual rows
SELECT department_id, unnest(employees) AS employee
FROM (
  SELECT department_id,
         array_agg(employee_name ORDER BY hire_date) AS employees
  FROM employees
  GROUP BY department_id
) dept_emps;

-- Array functions
SELECT array_length(ARRAY[1,2,3,4], 1) AS length; -- 4
SELECT array_remove(ARRAY[1,2,3,2], 2) AS removed; -- {1,3}
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS concatenated; -- {1,2,3,4}

2. JSON and JSONB Data Types

PostgreSQL offers two JSON data types: json and jsonb. json stores an exact copy of the input text, while jsonb stores data in a decomposed binary format that allows indexing and efficient querying. For most use cases, prefer jsonb.

Creating JSONB data: ``sql SELECT '{"name": "Alice", "age": 30}'::jsonb; SELECT jsonb_build_object('name', 'Alice', 'age', 30); SELECT row_to_json(employees)::jsonb FROM employees LIMIT 1; ``

Querying JSONB: - -> returns JSON object field (as JSONB) - ->> returns JSON object field as text - #> and #>> for path access

Example: ``sql SELECT data->'name' AS name_json, data->>'name' AS name_text FROM users; ``

JSONB operators: - @> contains - ? key exists - ?| any of keys exist - ?& all keys exist

Indexing JSONB: Use GIN indexes for efficient containment and existence queries: ``sql CREATE INDEX idx_users_data ON users USING gin (data); ``

Production insight: Avoid using json type for querying; always use jsonb if you need to filter or index. When extracting paths, use #>> for text to avoid casting.

-- Create a table with JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attributes JSONB
);

-- Insert sample data
INSERT INTO products (attributes) VALUES
('{"name": "Laptop", "specs": {"ram": "16GB", "storage": "512GB"}, "tags": ["electronics", "computers"]}'),
('{"name": "Phone", "specs": {"ram": "8GB", "storage": "256GB"}, "tags": ["electronics", "mobile"]}');

-- Query nested JSON
SELECT attributes->>'name' AS name,
       attributes->'specs'->>'ram' AS ram
FROM products
WHERE attributes @> '{"tags": ["electronics"]}';

-- Check key existence
SELECT * FROM products WHERE attributes ? 'specs';

3. Aggregating Rows into JSON with jsonb_agg() and jsonb_object_agg()

PostgreSQL provides aggregate functions to convert rows into JSON structures. jsonb_agg() aggregates values into a JSON array, while jsonb_object_agg() creates a JSON object from key-value pairs.

jsonb_agg(): Collects rows into a JSON array of objects. ``sql SELECT department_id, jsonb_agg(jsonb_build_object('id', employee_id, 'name', employee_name)) AS employees FROM employees GROUP BY department_id; ``

jsonb_object_agg(): Creates an object where keys come from one column and values from another. ``sql SELECT department_id, jsonb_object_agg(employee_name, salary) AS salaries FROM employees GROUP BY department_id; ``

Ordering within aggregates: Use ORDER BY inside the aggregate: ``sql jsonb_agg(employee_name ORDER BY hire_date) ``

Filtering within aggregates: Use FILTER clause: ``sql jsonb_agg(employee_name) FILTER (WHERE active = true) ``

Production insight: Be mindful of the size of the resulting JSON. For large datasets, consider paginating or using LIMIT within a subquery before aggregation.

-- Sample data
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    department_id INT,
    name TEXT,
    salary NUMERIC,
    active BOOLEAN
);

INSERT INTO employees (department_id, name, salary, active) VALUES
(1, 'Alice', 70000, true),
(1, 'Bob', 60000, true),
(2, 'Charlie', 80000, false),
(2, 'Diana', 75000, true);

-- jsonb_agg with ordering and filtering
SELECT department_id,
       jsonb_agg(
           jsonb_build_object('name', name, 'salary', salary)
           ORDER BY salary DESC
       ) FILTER (WHERE active) AS active_employees
FROM employees
GROUP BY department_id;

-- jsonb_object_agg
SELECT department_id,
       jsonb_object_agg(name, salary) AS salary_map
FROM employees
GROUP BY department_id;

4. Advanced Aggregation Functions: Ordered-Set and Hypothetical-Set

Beyond standard aggregates, PostgreSQL offers ordered-set aggregates that operate on ordered input. Common examples include percentile_cont(), percentile_disc(), and mode().

Percentile functions: ``sql SELECT department_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees GROUP BY department_id; ` percentile_cont returns a continuous value (interpolated), while percentile_disc` returns a discrete value from the set.

Mode: ``sql SELECT department_id, mode() WITHIN GROUP (ORDER BY salary) AS most_common_salary FROM employees GROUP BY department_id; ``

Hypothetical-set aggregates: These are used with WITHIN GROUP (ORDER BY ...) and assume a hypothetical row. Example: rank() hypothetical.

Production insight: Ordered-set aggregates require sorting, which can be expensive. Ensure the ORDER BY column is indexed. For large datasets, consider approximate algorithms if exact percentiles are not required.

-- Percentile and mode examples
SELECT department_id,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc,
       mode() WITHIN GROUP (ORDER BY salary) AS mode_salary
FROM employees
GROUP BY department_id;

-- Multiple percentiles
SELECT department_id,
       percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY salary) AS quartiles
FROM employees
GROUP BY department_id;

5. Combining Arrays, JSON, and Aggregations: Real-World Examples

Let's combine these concepts to solve real-world problems.

Example 1: Product catalog with nested categories Each product has multiple categories (array) and attributes (JSONB). We want to generate a report of all products grouped by category, with their attributes as JSON.

``sql SELECT cat, jsonb_agg( jsonb_build_object('id', p.id, 'name', p.name, 'attrs', p.attributes) ORDER BY p.name ) AS products FROM products p CROSS JOIN LATERAL unnest(p.categories) AS cat GROUP BY cat; ``

Example 2: Employee skills matrix Employees have skills stored as an array of strings. We need to find the top 3 most common skills across departments.

``sql SELECT skill, COUNT(*) AS cnt FROM employees e CROSS JOIN LATERAL unnest(e.skills) AS skill GROUP BY skill ORDER BY cnt DESC LIMIT 3; ``

Example 3: Sales report with JSON aggregation For each region, create a JSON object mapping product categories to total sales.

``sql SELECT region, jsonb_object_agg(category, total_sales) AS sales_by_category FROM ( SELECT region, category, SUM(amount) AS total_sales FROM sales GROUP BY region, category ) sub GROUP BY region; ``

Production insight: When using CROSS JOIN LATERAL unnest(), be aware that it can multiply rows significantly. Ensure proper WHERE clauses to limit data.

-- Example 1: Products grouped by category
SELECT cat, 
       jsonb_agg(
           jsonb_build_object('id', p.id, 'name', p.name, 'attrs', p.attributes)
           ORDER BY p.name
       ) AS products
FROM products p
CROSS JOIN LATERAL unnest(p.categories) AS cat
GROUP BY cat;

-- Example 2: Top skills
SELECT skill, COUNT(*) AS cnt
FROM employees e
CROSS JOIN LATERAL unnest(e.skills) AS skill
GROUP BY skill
ORDER BY cnt DESC
LIMIT 3;

-- Example 3: Sales by region and category
SELECT region,
       jsonb_object_agg(category, total_sales) AS sales_by_category
FROM (
    SELECT region, category, SUM(amount) AS total_sales
    FROM sales
    GROUP BY region, category
) sub
GROUP BY region;

6. Performance Optimization and Indexing Strategies

When working with arrays and JSONB, indexing is crucial for performance.

Indexing JSONB: - GIN indexes are the standard for JSONB. They support @>, ?, ?|, ?& operators. - Create a GIN index on the JSONB column: CREATE INDEX ON table USING gin (jsonb_col); - For specific paths, consider a B-tree index on an expression: CREATE INDEX ON table ((jsonb_col->>'key'));

Indexing Arrays: - GIN indexes also support array operations like @> (contains), && (overlap), etc. - Example: CREATE INDEX ON table USING gin (array_col);

Query planning: - Use EXPLAIN ANALYZE to check if indexes are used. - For aggregates, ensure the GROUP BY columns are indexed.

Avoiding pitfalls: - Don't index entire JSONB if you only query a few keys; use partial indexes. - For arrays, consider using unnest() with a WHERE clause to reduce rows before aggregation.

Production insight: Monitor query performance with pg_stat_statements and adjust indexes based on actual query patterns.

-- GIN index on JSONB column
CREATE INDEX idx_products_attributes ON products USING gin (attributes);

-- B-tree index on a specific JSONB key
CREATE INDEX idx_products_name ON products ((attributes->>'name'));

-- GIN index on array column
CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- Partial index for active employees only
CREATE INDEX idx_active_employees ON employees (department_id) WHERE active = true;

-- Check query plan
EXPLAIN ANALYZE
SELECT * FROM products
WHERE attributes @> '{"tags": ["electronics"]}';

7. Common Pitfalls and Best Practices

Pitfall 1: Mixing JSON and JSONB Using json type for querying leads to poor performance. Always use jsonb for queryable data.

Pitfall 2: Unbounded aggregation Aggregating thousands of rows into a single JSON object can cause memory issues. Always limit the input or paginate the output.

Pitfall 3: Ignoring NULLs in array_agg array_agg includes NULLs by default. Use FILTER (WHERE col IS NOT NULL) or array_remove() to clean up.

Best Practices: - Use jsonb_build_object and jsonb_build_array for constructing JSON programmatically. - Prefer jsonb_agg over array_agg when you need nested objects. - Use ORDER BY within aggregates to control element order. - For large datasets, consider materialized views to pre-aggregate data. - Test with realistic data volumes in staging before deploying to production.

-- Good: Use jsonb_build_object for clarity
SELECT jsonb_agg(
    jsonb_build_object('id', id, 'name', name)
    ORDER BY name
) FROM employees;

-- Bad: Concatenating strings to build JSON
SELECT '[' || string_agg('{"id":' || id || ',"name":"' || name || '"}', ',') || ']'
FROM employees;

-- Good: Filter out NULLs
SELECT array_agg(name) FILTER (WHERE name IS NOT NULL) FROM employees;

-- Bad: Relying on default
SELECT array_agg(name) FROM employees;

-- Good: Limit aggregation input
SELECT jsonb_agg(sub.*) FROM (
    SELECT * FROM employees ORDER BY id LIMIT 100
) sub;