Skip to main content
POST/analytics/query

Query Datasets with SQL

Runs a read-only SQL SELECT over the project's datasets and returns the rows.

Body: sql (at most 100,000 characters) and datasets (the dataset slugs the query reads, 1 to 64).

Reference each as dataset.<slug>; a table that is not a declared dataset is refused with 400 K9O_QUERY_INVALID.

Requires the PROJECT_ADMIN, PROJECT_AUTHOR or PROJECT_EDITOR role and the datasets:query permission on every dataset it reads.

Callers must be members of the project (an organization role alone does not grant access); project service tokens and extension tokens cannot call it.

Returns 503 K9O_ANALYTICS_UNAVAILABLE until the region's analytics storage is provisioned.

Parameters
projectSlug *path

The project's slug: the path segment after @.

It is unique within the org.

Request body
sql *string

DuckDB SQL; every table is dataset.<slug> and must be listed in datasets.

datasets *string[]

The slugs of every dataset the SQL reads, without the dataset. prefix.

The query is refused if the SQL reads a dataset not listed here, or one the caller may not query.

Responses
HTTPK9O CodeDescription
200Success
400K9O_INVALID_FIELD_VALUEThe body is not an object, `sql` is empty or too long, or `datasets` is empty, too long or names an invalid slug.
400K9O_QUERY_INVALIDThe SQL cannot be parsed, or reads a table that is not one of the declared datasets.
401K9O_UNAUTHENTICATEDThe caller is not signed in.
403K9O_ACCESS_DENIEDThe caller is not a member of this project; an organization role alone does not grant access.
403K9O_RBAC_DENYOnly PROJECT_ADMIN, PROJECT_AUTHOR and PROJECT_EDITOR may query datasets.
403K9O_IAM_POLICY_DENIEDThe caller's role or IAM policy does not grant datasets:query.
404K9O_DATASET_NOT_FOUNDNo active dataset with this slug exists in the project.
500K9O_QUERY_EXECUTION_FAILEDThe query failed while running.
503K9O_ANALYTICS_UNAVAILABLEThe region's analytics storage is not available yet; retry later.
Response body (200)
data *object[]

The result rows, keyed by the query's column names.

meta *object

Request metadata: cost, timing and correlation ids.

cost *object

Metered usage for this request; empty when nothing was metered.

timing *object
gatewayMs *number
totalMs *number
context *object
requestId *string
traceId *string
projectIdstring
warningsobject[]
code *string
message *string
TypeScript
// const k9o = new K9o({ orgSlug: 'playground', projectSlug: 'storefront', apiKey: 'k9o_…' })
const { data } = await k9o.analytics.query({})

Showing example in TypeScript

Request example
{
  "sql": "string",
  "datasets": [
    "string"
  ]
}
Response example (200)
{
  "data": [],
  "meta": {
    "timing": {
      "gatewayMs": 0,
      "totalMs": 0
    },
    "context": {
      "requestId": "string",
      "traceId": "string",
      "projectId": "string"
    },
    "warnings": [
      {
        "code": "string",
        "message": "string"
      }
    ]
  }
}

More in Analytics & Intelligence