The json component

Send query results as a single JSON array: 'array' as type

The default array mode sends the query results as a single JSON array.

If a query returns an error, the array will contain an object with an error property.

If multiple queries are executed, all query results will be concatenated into a single array of heterogeneous objects.

SQL

select 'json' AS component;
select * from users;

Result

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

Send a single JSON object: 'jsonlines' as type

In jsonlines mode, each query result is rendered as a JSON object in a separate line, without a top-level array.

If there is a single query result, the response will be a valid JSON object. If there are multiple query results, you will need to parse each line of the response as a separate JSON object.

If a query returns an error, the response will be a JSON object with an error property.

SQL

The following SQL creates an API endpoint that takes a user_id URL parameter and returns a single JSON object containing the user's details, with one json object key per column in the users table.

select 'json' AS component, 'jsonlines' AS type;
select * from users where id = $user_id LIMIT 1;

Note the LIMIT 1 clause. The jsonlines type will send one JSON object per result row, separated only by a single newline character (\n). So if your query returns multiple rows, the result will not be a single valid JSON object, like most JSON parsers expect.

Result

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

Create a complex API endpoint: the 'contents' property

You can create an API endpoint that will return a JSON value in any format you want, to implement a complex API.

You should use the json functions provided by your database to form the value you pass to the contents property. To build a json array out of rows from the database, you can use:

• json_group_array() in SQLite,

• json_agg() in Postgres, or

• JSON_ARRAYAGG() in MySQL.

• FOR JSON PATH in SQL Server.

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

This will return a JSON response that looks like this:

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

If you want to handle custom API routes, like POST /api/users/:id, you can use

• the 404.sql file to handle the request despite the URL not matching any file,
• the request_method function to differentiate between GET and POST requests,
• and the path function to extract the :id parameter from the URL.

Access query results in real-time with server-sent events: 'sse' as type

Using server-sent events, you can stream large query results to the client in real-time, row by row.

This allows building sophisticated dynamic web applications that will start processing and displaying the first rows of data in the browser while the database server is still processing the end of the query.

SQL

select 'json' AS component, 'sse' AS type;
select * from users;

JavaScript

const eventSource = new EventSource("users.sql");
eventSource.onmessage = function (event) {
    const user = JSON.parse(event.data);
    console.log(user.username);
}
eventSource.onerror = () => eventSource.close(); // do not reconnect after reading all the data