Skip to content
HeliosDB HeliosDB Lite

json_agg() and Array Operations Tutorial

Build: default — no feature flag required

UVP

PostgreSQL-shaped JSON and array operations, embedded. json_agg() collects rows into a single JSON array — including nested objects via json_build_object() and ordered output via json_agg(... ORDER BY...). The 1-based array subscript arr[n] returns NULL out-of-bounds (no panic). The || operator concatenates arrays with automatic type coercion. Type verification covers UUID, Boolean, and BYTEA. Everything composes with materialized views, so your pre-computed analytics stay JSON-shaped without an extra service.

Prerequisites

  • HeliosDB Lite
  • A REPL or psql client
  • 10 minutes

1. json_agg() — Rows Into Arrays

CREATE TABLE users (
    id   INT,
    name TEXT
);


INSERT INTO users VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie');


SELECT json_agg(name) AS names FROM users;
-- ["Alice", "Bob", "Charlie"]

Group then aggregate — one JSON array per group:

CREATE TABLE products (
    id       INT,
    name     TEXT,
    category TEXT,
    price    DECIMAL
);


INSERT INTO products VALUES
    (1, 'Laptop',  'Electronics', 999.99),
    (2, 'Mouse',   'Electronics',  29.99),
    (3, 'Desk',    'Furniture',   299.99);


SELECT category, json_agg(name) AS items
FROM products
GROUP BY category;
-- Electronics  ["Laptop", "Mouse"]
-- Furniture    ["Desk"]

2. Nested Objects via json_build_object

SELECT
    department,
    json_agg(
        json_build_object(
            'name',   name,
            'salary', salary,
            'role',   role
        )
    ) AS employees
FROM staff
GROUP BY department;

Every row becomes a JSON object inside the per-group array — the canonical shape for “give me a department roster as JSON.”

3. Ordered Aggregation

json_agg honours an ORDER BY clause inside its own argument:

SELECT json_agg(product ORDER BY price DESC) AS products_by_price
FROM products;

The order of items inside the resulting array follows the per-group ordering — you don’t need a downstream sort.

4. Array Subscript — arr[n]

SELECT ARRAY[10, 20, 30, 40, 50][1] AS first;
-- 10


SELECT ARRAY[10, 20, 30, 40, 50][3] AS third;
-- 30


-- Out-of-bounds returns NULL, not an error.
SELECT ARRAY[10, 20, 30][10] AS out_of_bounds;
-- NULL

The subscript is 1-based, matching PostgreSQL. Use it inside any SQL expression:

SELECT * FROM products WHERE tags[1] = 'featured';

5. Array Concatenation — ||

SELECT ARRAY[1, 2, 3] || ARRAY[4, 5, 6] AS combined;
-- [1, 2, 3, 4, 5, 6]


-- Automatic type coercion when types differ.
SELECT ARRAY[1, 2] || ARRAY[3.5, 4.5] AS mixed;
-- [1.0, 2.0, 3.5, 4.5]

Concatenate per-row to build derived columns:

SELECT
    user_id,
    favorite_items || recent_items AS all_items
FROM user_preferences;

Or stitch JSON aggregates together:

SELECT
    category,
    json_agg(name) || json_agg(description) AS combined_info
FROM products
GROUP BY category;

6. Type Verification — UUID, Boolean, BYTEA

Lite verifies three types explicitly that are easy to coerce ambiguously:

-- UUID literals validated at parse time.
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID;


-- Boolean coerces from canonical strings.
SELECT 'true'::BOOLEAN, 'false'::BOOLEAN, 't'::BOOLEAN, 'f'::BOOLEAN;


-- BYTEA accepts hex literals.
SELECT '\xdeadbeef'::BYTEA;

The same guards apply when these types appear inside json_agg outputs or array elements — invalid values surface as errors, not as silently-corrupted JSON.

7. Materialized View Integration

JSON aggregation works inside a materialized view. Readers get the stored result; refresh the view on a schedule (automatic background refresh is planned):

CREATE MATERIALIZED VIEW customer_orders AS
SELECT
    customer_id,
    json_agg(
        json_build_object(
            'order_id', order_id,
            'total',    total,
            'date',     order_date
        ) ORDER BY order_date DESC
    ) AS order_history
FROM orders
GROUP BY customer_id;


-- Refresh, for example every 5 minutes from cron.
REFRESH MATERIALIZED VIEW customer_orders INCREMENTALLY;


-- Read.
SELECT order_history FROM customer_orders WHERE customer_id = 42;

See MATERIALIZED_VIEWS_TUTORIAL for the full lifecycle.

8. Practical Patterns

Per-row to per-group reshaping

-- Pivot order line items into a single JSON column on the order header.
CREATE MATERIALIZED VIEW order_with_items AS
SELECT
    o.id,
    o.customer_id,
    o.total,
    json_agg(
        json_build_object(
            'sku',      li.sku,
            'qty',      li.qty,
            'subtotal', li.qty * li.unit_price
        ) ORDER BY li.line_no
    ) AS items
FROM orders o
JOIN line_items li ON li.order_id = o.id
GROUP BY o.id, o.customer_id, o.total;

API-shaped responses

A REST handler can serve the JSON directly without re-marshalling:

SELECT json_build_object(
    'id',       id,
    'customer', customer_id,
    'total',    total,
    'items',    items
) AS payload
FROM order_with_items
WHERE id = $1;

Tag manipulation

-- Append a tag without TOAST overhead.
UPDATE products
SET tags = tags || ARRAY['promo']
WHERE category = 'Electronics';


-- Filter by first tag.
SELECT * FROM products WHERE tags[1] = 'featured';

Where Next