Skip to content
HeliosDB HeliosDB Lite

json_agg() and Array Operations Tutorial

json_agg() and Array Operations Tutorial

Available since: v3.4.0 Build: default — no feature flag required Modules: heliosdb_lite::compute::aggregation, heliosdb_lite::sql::executor::aggregate

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 v3.4+
  • 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. The Rust API

Arrays are first-class values:

use heliosdb_lite::{EmbeddedDatabase, Value};


let db = EmbeddedDatabase::new("./mydb.helio")?;


db.execute("CREATE TABLE users (id INT, name TEXT, tags TEXT[])")?;


db.execute_params(
    "INSERT INTO users VALUES ($1, $2, $3)",
    &[
        Value::Int4(1),
        Value::String("Alice".to_string()),
        Value::Array(vec![
            Value::String("admin".to_string()),
            Value::String("verified".to_string()),
        ]),
    ],
)?;


// Subscript in WHERE.
let results = db.query(
    "SELECT name FROM users WHERE tags[1] = 'admin'",
    &[],
)?;


// Subscript in SELECT.
let result = db.query(
    "SELECT ARRAY[10, 20, 30, 40][2] AS element",
    &[],
)?;
assert_eq!(result[0].get("element"), Value::Int4(20));


// Concatenation.
let result = db.query(
    "SELECT ARRAY[1, 2] || ARRAY[3, 4] AS combined",
    &[],
)?;
// combined: [1, 2, 3, 4]

7. Type Verification — UUID, Boolean, BYTEA

v3.4 adds explicit verification for three types that previously had ambiguous coercions:

-- 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.

8. Materialized View Integration

The whole point of v3.4 was making JSON aggregation work cleanly inside a materialized view. The auto-refresh scheduler maintains the view; readers get instant results:

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;


-- Tune refresh.
ALTER MATERIALIZED VIEW customer_orders SET (
    auto_refresh             = true,
    staleness_threshold_sec  = 300,
    cpu_threshold            = 0.5
);


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

See MATERIALIZED_VIEWS_TUTORIAL for the full lifecycle.

9. 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