The json component

For advanced users, allows you to easily build an API over your database. The json component responds to the current HTTP request with a JSON object. This component must appear at the top of your SQL file, before any other data has been sent to the browser.

Creates an API endpoint that will allow developers to easily query a list of users stored in your database.

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.