---
title: "json_agg() and Array Operations Tutorial"
---

# 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

```sql
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:

```sql
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`

```sql
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:

```sql
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]`

```sql
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:

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

---

## 5. Array Concatenation — `||`

```sql
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:

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

Or stitch JSON aggregates together:

```sql
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:

```sql
-- 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):

```sql
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](https://www.heliosdb.com/docs/lite/guides/tutorials/materialized_views_tutorial/) for the full lifecycle.

---

## 8. Practical Patterns

### Per-row to per-group reshaping

```sql
-- 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:

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

### Tag manipulation

```sql
-- 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

- [MATERIALIZED_VIEWS_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/materialized_views_tutorial/) — refresh strategies + JSON aggregation patterns.
- [DATABASE_BRANCHING_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/database_branching_tutorial/) — branch-isolated experiments with array data.
- [REST_API_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/rest_api_tutorial/) — serve `json_agg` results directly over HTTP.
- [Parameterized Queries](https://www.heliosdb.com/docs/lite/guides/parameterized_queries/) — bind parameters safely.