JSON

The JSON component converts SQL query results into machine-readable JSON format, making it easy to build APIs for external systems, mobile apps, and JavaScript integrations.

The JSON component must be at the very top of your SQL file, before any other component. An HTTP response can only have one content type, declared in the headers.

Basic Usage

Return query results as a JSON array:

SELECT 'json' AS component;
SELECT * FROM users;

Output:

[
    {"username":"James","userid":1},
    {"username":"John","userid":2}
]

JSON Response Types

The JSON component supports three output modes:

Array Mode (Default)

Returns all rows in a single JSON array:

SELECT 'json' AS component;
SELECT name, email FROM users;

JSON Lines Mode

Returns one JSON object per line, without wrapping array:

SELECT 'json' AS component, 'jsonlines' AS type;
SELECT * FROM users WHERE id = $user_id LIMIT 1;

Output:

{"username":"James","userid":1}

Use LIMIT 1 with jsonlines mode. Multiple rows will output multiple JSON objects separated by newlines, which most JSON parsers won’t accept as a single valid JSON document.

Server-Sent Events (SSE)

Stream results in real-time for large datasets:

SELECT 'json' AS component, 'sse' AS type;
SELECT * FROM large_dataset;

Consume in JavaScript:

const eventSource = new EventSource("data.sql");
eventSource.onmessage = function (event) {
    const row = JSON.parse(event.data);
    console.log(row);
};
eventSource.onerror = () => eventSource.close();

Building Complex APIs

Use the contents parameter for custom JSON structure:

SELECT 'json' AS component,
    JSON_OBJECT(
        'users', (
            SELECT JSON_GROUP_ARRAY(
                JSON_OBJECT(
                    'username', username,
                    'userid', id
                )
            ) FROM users
        )
    ) AS contents;

Output:

{
    "users": [
        {"username":"James","userid":1}
    ]
}

Database-Specific JSON Functions

Each database has its own JSON aggregation functions: SQLite

SELECT 'json' AS component,
    json_object(
        'orders', (
            SELECT json_group_array(
                json_object('id', id, 'total', total)
            ) FROM orders
        )
    ) AS contents;

PostgreSQL

SELECT 'json' AS component,
    json_build_object(
        'orders', (
            SELECT json_agg(
                json_build_object('id', id, 'total', total)
            ) FROM orders
        )
    ) AS contents;

MySQL

SELECT 'json' AS component,
    JSON_OBJECT(
        'orders', (
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT('id', id, 'total', total)
            ) FROM orders
        )
    ) AS contents;

SQL Server

SELECT 'json' AS component,
    (
        SELECT id, total
        FROM orders
        FOR JSON PATH
    ) AS contents;

RESTful API Patterns

List Resources

GET /api/users.sql

SELECT 'json' AS component;
SELECT id, username, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 100;

Get Single Resource

GET /api/user.sql?id=123

SELECT 'json' AS component, 'jsonlines' AS type;
SELECT id, username, email, created_at
FROM users
WHERE id = $id
LIMIT 1;

Nested Resources

SELECT 'json' AS component;
SELECT 
    u.id,
    u.username,
    (
        SELECT json_group_array(
            json_object('id', o.id, 'total', o.total)
        )
        FROM orders o
        WHERE o.user_id = u.id
    ) AS orders
FROM users u
WHERE u.id = $user_id;

Error Responses

Return error information:

SELECT 'json' AS component, 'jsonlines' AS type;
SELECT 
    CASE 
        WHEN NOT EXISTS (SELECT 1 FROM users WHERE id = $user_id)
        THEN json_object('error', 'User not found', 'code', 404)
        ELSE (
            SELECT json_object('id', id, 'username', username)
            FROM users WHERE id = $user_id
        )
    END AS response;

Content Negotiation

SQLPage automatically returns JSON when the client requests it:

curl -H "Accept: application/json" http://localhost:8080/users.sql

Without the JSON component, SQLPage checks the Accept header and returns JSON or HTML accordingly.

Pagination API

SELECT 'json' AS component,
    json_object(
        'page', COALESCE($page, 1),
        'per_page', 20,
        'total', (SELECT COUNT(*) FROM products),
        'data', (
            SELECT json_group_array(
                json_object(
                    'id', id,
                    'name', name,
                    'price', price
                )
            )
            FROM (
                SELECT * FROM products
                ORDER BY created_at DESC
                LIMIT 20
                OFFSET (COALESCE($page, 1) - 1) * 20
            )
        )
    ) AS contents;

Usage: /api/products.sql?page=2

Filtering and Search

SELECT 'json' AS component;
SELECT id, name, price
FROM products
WHERE 
    ($category IS NULL OR category = $category)
    AND ($min_price IS NULL OR price >= $min_price)
    AND ($search IS NULL OR name LIKE '%' || $search || '%')
ORDER BY price ASC;

Usage: /api/products.sql?category=electronics&min_price=50&search=laptop

CORS Headers

Enable cross-origin requests:

SELECT 'http_header' AS component,
    'Access-Control-Allow-Origin' AS name,
    '*' AS value;

SELECT 'json' AS component;
SELECT * FROM data;

For specific origins:

SELECT 'http_header' AS component,
    'Access-Control-Allow-Origin' AS name,
    'https://yourdomain.com' AS value;

Custom Routes with 404.sql

Handle custom API routes like /api/users/:id: 404.sql

SET path_parts = (SELECT json_extract(sqlpage.path(), '$[0]'));
SET resource = (SELECT json_extract(sqlpage.path(), '$[1]'));
SET resource_id = (SELECT json_extract(sqlpage.path(), '$[2]'));

-- Route: /api/users/:id
SELECT 'json' AS component, 'jsonlines' AS type
WHERE $path_parts = 'api' AND $resource = 'users' AND $resource_id IS NOT NULL;

SELECT *
FROM users
WHERE id = $resource_id
LIMIT 1;

POST/PUT/DELETE Methods

Handle different HTTP methods:

-- Check request method
SET method = (SELECT sqlpage.request_method());

-- GET: Return data
SELECT 'json' AS component WHERE $method = 'GET';
SELECT * FROM users;

-- POST: Create new user
INSERT INTO users (username, email)
SELECT :username, :email
WHERE $method = 'POST'
RETURNING
    'json' AS component,
    'jsonlines' AS type,
    id,
    username,
    email;

-- DELETE: Remove user
DELETE FROM users
WHERE id = $id AND $method = 'DELETE'
RETURNING
    'json' AS component,
    'jsonlines' AS type,
    json_object('deleted', true, 'id', id) AS result;

Authentication for APIs

Implement API key authentication:

-- Validate API key
SELECT 'json' AS component, 'jsonlines' AS type,
    json_object('error', 'Invalid API key', 'code', 401) AS error
WHERE NOT EXISTS (
    SELECT 1 FROM api_keys
    WHERE key = sqlpage.header('X-API-Key')
    AND expires_at > CURRENT_TIMESTAMP
);

-- Return data
SELECT 'json' AS component;
SELECT * FROM protected_data;

Webhooks

Process incoming webhook data:

SET payload = (SELECT sqlpage.request_body());

INSERT INTO webhook_logs (source, payload, received_at)
VALUES ('github', $payload, CURRENT_TIMESTAMP);

-- Process webhook
INSERT INTO events (type, data)
SELECT 
    json_extract($payload, '$.action') AS type,
    $payload AS data;

SELECT 'json' AS component, 'jsonlines' AS type,
    json_object('received', true) AS response;

Top-level Parameters

contents

json

Complete JSON payload to send. When provided, subsequent queries are ignored. Use database JSON functions to build the structure.

type

string

default:"array"

Output format:

array: Single JSON array containing all rows
jsonlines: One JSON object per line (no wrapping array)
sse: Server-sent events format for streaming

Best Practices

Limit Result Size

Always use LIMIT to prevent memory issues:

SELECT 'json' AS component;
SELECT * FROM large_table LIMIT 1000;

Use Indices

Optimize API queries with database indices:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Cache Results

Use HTTP caching headers:

SELECT 'http_header' AS component,
    'Cache-Control' AS name,
    'public, max-age=3600' AS value;

SELECT 'json' AS component;
SELECT * FROM static_data;

Validate Input

Sanitize and validate parameters:

SELECT 'json' AS component, 'jsonlines' AS type,
    json_object('error', 'Invalid user ID') AS error
WHERE $user_id NOT GLOB '[0-9]*';

Complete API Example

A full CRUD API: api/tasks.sql

SET method = (SELECT sqlpage.request_method());

-- GET: List tasks
SELECT 'json' AS component WHERE $method = 'GET' AND $id IS NULL;
SELECT id, title, completed, created_at FROM tasks ORDER BY created_at DESC;

-- GET: Single task
SELECT 'json' AS component, 'jsonlines' AS type WHERE $method = 'GET' AND $id IS NOT NULL;
SELECT id, title, description, completed, created_at FROM tasks WHERE id = $id;

-- POST: Create task
INSERT INTO tasks (title, description)
SELECT :title, :description
WHERE $method = 'POST'
RETURNING
    'json' AS component,
    'jsonlines' AS type,
    id, title, description, completed, created_at;

-- PUT: Update task
UPDATE tasks
SET title = COALESCE(:title, title),
    description = COALESCE(:description, description),
    completed = COALESCE(:completed, completed)
WHERE id = $id AND $method = 'PUT'
RETURNING
    'json' AS component,
    'jsonlines' AS type,
    id, title, description, completed, created_at;

-- DELETE: Remove task
DELETE FROM tasks
WHERE id = $id AND $method = 'DELETE'
RETURNING
    'json' AS component,
    'jsonlines' AS type,
    json_object('deleted', true, 'id', id) AS result;

Overview

Display Components

Input Components

Layout Components

Special Components

Basic Usage

JSON Response Types

Array Mode (Default)

JSON Lines Mode

Server-Sent Events (SSE)

Building Complex APIs

Database-Specific JSON Functions

RESTful API Patterns

List Resources

Get Single Resource

Nested Resources

Error Responses

Content Negotiation

Filtering and Search

CORS Headers

Custom Routes with 404.sql

POST/PUT/DELETE Methods

Authentication for APIs

Webhooks

Top-level Parameters

Best Practices

Limit Result Size

Use Indices

Cache Results

Validate Input

Complete API Example

Build docs developers (and LLMs) love

Overview

Display Components

Input Components

Layout Components

Special Components

​Basic Usage

​JSON Response Types

​Array Mode (Default)

​JSON Lines Mode

​Server-Sent Events (SSE)

​Building Complex APIs

​Database-Specific JSON Functions

​RESTful API Patterns

​List Resources

​Get Single Resource

​Nested Resources

​Error Responses

​Content Negotiation

​Pagination API

​Filtering and Search

​CORS Headers

​Custom Routes with 404.sql

​POST/PUT/DELETE Methods

​Authentication for APIs

​Webhooks

​Top-level Parameters

​Best Practices

​Limit Result Size

​Use Indices

​Cache Results

​Validate Input

​Complete API Example

Build docs developers (and LLMs) love

Basic Usage

JSON Response Types

Array Mode (Default)

JSON Lines Mode

Server-Sent Events (SSE)

Building Complex APIs

Database-Specific JSON Functions

RESTful API Patterns

List Resources

Get Single Resource

Nested Resources

Error Responses

Content Negotiation

Pagination API

Filtering and Search

CORS Headers

Custom Routes with 404.sql

POST/PUT/DELETE Methods

Authentication for APIs

Webhooks

Top-level Parameters

Best Practices

Limit Result Size

Use Indices

Cache Results

Validate Input

Complete API Example