JSON API dashboard: query any JSON endpoint with SQL

How rows come out of a response

The connector takes one URL. On every run it sends a GET to that URL, parses the body as JSON, and loads it into an in-memory SQLite table named data. Your query then runs against data. The text of the query never reaches the API, which is also why you cannot add a path or a filter parameter from the SQL editor. Those belong in the URL.

The shape of the response decides the rows:

• A top-level array gives one row per element. Column names are the union of the keys across all elements.
• A top-level object gives exactly one row, with its keys as columns.
• An array of plain values gives one row each in a single column named _value.
• Nested objects and arrays land in their column as JSON text. Booleans become 1 and 0.

That second rule is the one that surprises people. {"items": [...], "total": 2} is a single row whose items column holds the array as text. You unpack it in SQL with json_each and json_extract, both built into the bundled SQLite. Everything below was run on a table shaped the way the connector builds it.

Connect a JSON endpoint

The form has three fields: the URL, an optional username and an optional password. When the username is set, the request carries HTTP Basic auth. That is the only auth there is. There are no custom headers, so an API that wants a bearer token or an API key header cannot be queried, and a key in the query string sits in a field that is not marked secret. Prefer an endpoint you can protect with Basic auth, or one that is public.

Other limits that matter for monitoring:

• A request times out after 30 seconds.
• Redirects are not followed, so use the final URL.
• Hosts that resolve to private or loopback addresses are refused. The endpoint must be reachable from the public internet.
• A 4xx or 5xx status fails the run with an error.
• More than 10,000 elements in the response fails the run, and so does a query result above 10,000 rows. Keep the endpoint paginated at the source or point at a summary endpoint.

The general steps for testing a connection are in Connectors overview.

A freshness check from a timestamp field

A JSON endpoint has no table to ask when it was last written, so freshness is a field in the payload: an updated_at on each record, or a generated_at on the whole response. If the API returns an array of records with an ISO 8601 updated_at:

SELECT CAST((julianday('now') - julianday(max(updated_at))) * 86400 AS INTEGER)
         AS seconds_since_update
FROM data;

If the timestamp is Unix seconds, subtract directly:

SELECT CAST(strftime('%s', 'now') AS INTEGER) - max(updated_ts) AS seconds_since_update
FROM data;

If the response is an object with a top-level generated_at, the single row has that column:

SELECT CAST((julianday('now') - julianday(generated_at)) * 86400 AS INTEGER) AS age_seconds
FROM data;

Monitor the number with > and a threshold in seconds, as described in Alerts overview. Timestamps without a zone are read as UTC, so check what the API means by them.

An empty array is a trap. With no elements there are no keys, so the table has only a _value column, and a query that names updated_at errors with a missing column instead of returning a number. The run fails and nothing is evaluated. A row count does not have this problem, because count(*) still returns 0.

Volume and a metric alert

SELECT count(*) AS row_count FROM data;

Monitor row_count with < and a floor to catch an endpoint that suddenly returns an empty list. For a metric, count the bad states:

SELECT count(*) AS failed FROM data WHERE status = 'failed';

Monitor failed with > and 0. For a response wrapped in an object, expand the array first:

SELECT count(*) AS item_count FROM data, json_each(data.items);

Every run is an HTTP request

Unlike a database, this connector has no connection that sits idle. A scheduled query on the connector calls the API each time it fires, and so does every dashboard tile that is stale when someone opens it. Put the freshness check on a sensible cadence, such as every 15 minutes, and watch the rate limit of the API you are calling. After 10 consecutive failures, a schedule pauses itself, so a flaky endpoint stops being polled rather than being hammered. See Scheduled refresh.

Because the data is the live response, there is no history. If you need to compare against yesterday, save the result somewhere that keeps it, or let a baseline monitor learn the normal value from its own recorded observations.

Dashboard tiles

For an endpoint that returns job runs:

-- status mix
SELECT status, count(*) AS n FROM data GROUP BY status ORDER BY n DESC;

-- retries per region, from a nested JSON column
SELECT json_extract(meta, '$.region') AS region,
       sum(json_extract(meta, '$.retries')) AS retries
FROM data
GROUP BY 1;

-- running jobs with no update in the last hour
SELECT count(*) AS stale_jobs
FROM data
WHERE status = 'running' AND julianday('now') - julianday(updated_at) > 1 / 24.0;

The third tile doubles as a metric alert: monitor stale_jobs with > and 0.

When this is not enough

This connector fits status pages, small internal APIs and exports published as JSON. It does not fit an API that needs headers, paginates, or returns more than 10,000 records. For those, land the data in a database and connect that. To combine a JSON response with a database table, save a query on each and join the results in Query Results.

For an uploaded file instead of an endpoint, see the CSV connector.

docs

JSON over HTTP setup in the docs

Connection fields, an example configuration and caveats.