> ## Documentation Index
> Fetch the complete documentation index at: https://braintrust.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Query by SQL

> Query logs, experiments, and datasets with SQL through a single REST endpoint.

The `/btql` endpoint runs [SQL](/docs/reference/sql) over your logs, experiments, and datasets. Use this API endpoint to send a query request, and Braintrust runs the query synchronously and returns the rows in the response.

<Tip>
  You can run the same SQL interactively in the [SQL sandbox](https://www.braintrust.dev/app/~/sql), or from your terminal with [`bt sql`](/docs/reference/cli/sql).
</Tip>

## Base URL

The base URL depends on your organization's [data plane region](/docs/admin/organizations#data-plane-region):

| Region      | Base URL                        |
| ----------- | ------------------------------- |
| US          | `https://api.braintrust.dev`    |
| EU          | `https://api-eu.braintrust.dev` |
| Self-hosted | Your custom data plane URL      |

Authenticate every request with your API key in the `Authorization` header. The examples below use the US base URL.

## Query data

Send a `POST /btql` request with a [SQL](/docs/reference/sql) `query` in the body. Data-source functions like `project_logs()` and `experiment()` accept an object name or its ID. See [Querying by name](/docs/reference/sql/query-structure#querying-by-name) for details.

Braintrust runs the query synchronously and returns the results in a single response.

<CodeGroup dropdown>
  ```bash theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  curl -X POST https://api.braintrust.dev/btql \
    -H "Authorization: Bearer $BRAINTRUST_API_KEY" \
    -H "Content-Type: application/json" \
    -d '{
      "query": "SELECT id, input, output, scores FROM project_logs('\''your-project-id'\'', shape => '\''traces'\'') WHERE scores.accuracy > 0.8 LIMIT 100",
      "fmt": "json"
    }'
  ```

  ```typescript theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  const API_URL = "https://api.braintrust.dev";
  const headers = {
    Authorization: `Bearer ${process.env.BRAINTRUST_API_KEY}`,
    "Content-Type": "application/json",
  };

  const query = `
    SELECT id, input, output, scores
    FROM project_logs('your-project-id', shape => 'traces')
    WHERE scores.accuracy > 0.8
    LIMIT 100
  `;

  const response = await fetch(`${API_URL}/btql`, {
    method: "POST",
    headers,
    body: JSON.stringify({ query, fmt: "json" }),
  });

  const { data } = await response.json();
  for (const row of data) {
    console.log(row);
  }
  ```

  ```python theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  import os
  import requests

  API_URL = "https://api.braintrust.dev"
  headers = {"Authorization": "Bearer " + os.environ["BRAINTRUST_API_KEY"]}

  query = """
  SELECT id, input, output, scores
  FROM project_logs('your-project-id', shape => 'traces')
  WHERE scores.accuracy > 0.8
  LIMIT 100
  """

  response = requests.post(
      f"{API_URL}/btql",
      headers=headers,
      json={"query": query, "fmt": "json"},
  ).json()

  for row in response["data"]:
      print(row)
  ```
</CodeGroup>

To run the same query from your terminal, use [`bt sql`](/docs/reference/cli/sql):

```bash theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
bt sql "SELECT id, input, output, scores FROM project_logs('your-project-id') WHERE scores.accuracy > 0.8 LIMIT 100"
```

### Request body

Send a JSON body with the query and options.

| Field       | Type                                 | Required | Description                                                                                                                                                                                                      |
| ----------- | ------------------------------------ | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `query`     | string or object                     | Yes      | The query to run. Pass a [SQL](/docs/reference/sql) string, or a structured query object. Use the object form to paginate, since the `cursor` is a field on that object. See [Pagination](#pagination).               |
| `fmt`       | `"json"` \| `"jsonl"` \| `"parquet"` | No       | Output format. Defaults to `json`.                                                                                                                                                                               |
| `lint_mode` | `"default"` \| `"strict"`            | No       | How [lint warnings](/docs/reference/sql/best-practices#lint-warnings) surface. `default` returns them alongside results; `strict` blocks the query when any warning fires.                                            |
| `version`   | string                               | No       | Query the data as of a specific version (an `_xact_id`). Supported for `experiment` and `dataset` sources; not for `project_logs`. Useful for [recovering deleted rows](/docs/kb/recovering-deleted-experiment-rows). |
| `tz_offset` | number                               | No       | Timezone offset in minutes for time-based operations, following the `Date.prototype.getTimezoneOffset()` convention (positive is behind UTC).                                                                    |

<Note>
  For correct day boundaries, set `tz_offset` to match your timezone. For example, use `480` for US Pacific Standard Time.
</Note>

```json Request theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
{
  "query": "SELECT id, input, output, scores FROM project_logs('your-project-id', shape => 'traces') WHERE scores.accuracy > 0.8 LIMIT 100",
  "fmt": "json"
}
```

### Response

For `fmt: "json"`, the response is an object with a `data` array of row objects. Each row's fields match the columns your query selects.

```json Response theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
{
  "data": [
    {
      "id": "6f3b1c9e-...",
      "input": { "question": "What is 2+2?" },
      "output": "4",
      "scores": { "accuracy": 1.0 }
    }
  ],
  "schema": {
    "id": "string",
    "input": "object",
    "output": "string",
    "scores": "object"
  }
}
```

| Field    | Type             | Description                                                                                               |
| -------- | ---------------- | --------------------------------------------------------------------------------------------------------- |
| `data`   | array of objects | The result rows. Present for the `json` format.                                                           |
| `schema` | object           | The inferred type of each result column. Present for `json` responses; omitted for `jsonl` and `parquet`. |

Use `jsonl` to stream one row per line, or `parquet` for a binary column format suited to large exports. When results overflow the synchronous response size, the response includes a cursor in the `x-bt-cursor` header. Send it back as `cursor` to fetch the next page. See [Pagination](#pagination) below for details.

### Pagination

For large result sets, the response returns a pagination cursor in the `x-bt-cursor` header. To fetch the next page, resend the query with that cursor and repeat until the header comes back empty.

The cursor applies to the query itself, so how you pass it depends on the `query` format:

* **Structured query object**: set a `cursor` field on the object, as shown in the example below.
* **SQL string**: append `OFFSET '<cursor>'` to the query. See [Syntax styles](/docs/reference/sql#syntax-styles) for the full clause mapping.

<Note>
  If you're using the Python or TypeScript SDK, pagination is handled automatically. Only use this code if you're developing with other tools.
</Note>

<CodeGroup dropdown>
  ```typescript theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  // If you're self-hosting Braintrust, then use your stack's Universal API URL, e.g.
  //   https://dfwhllz61x709.cloudfront.net
  export const BRAINTRUST_API_URL = "https://api.braintrust.dev";
  export const API_KEY = process.env.BRAINTRUST_API_KEY;

  export async function* paginateDataset(args: {
    project: string;
    dataset: string;
    version?: string;
    // Number of rows to fetch per request. You can adjust this to be a lower number
    // if your rows are very large (e.g. several MB each).
    perRequestLimit?: number;
  }) {
    const { project, dataset, version, perRequestLimit } = args;
    const headers = {
      Accept: "application/json",
      "Accept-Encoding": "gzip",
      Authorization: `Bearer ${API_KEY}`,
    };
    const fullURL = `${BRAINTRUST_API_URL}/v1/dataset?project_name=${encodeURIComponent(
      project,
    )}&dataset_name=${encodeURIComponent(dataset)}`;
    const ds = await fetch(fullURL, {
      method: "GET",
      headers,
    });
    if (!ds.ok) {
      throw new Error(
        `Error fetching dataset metadata: ${ds.status}: ${await ds.text()}`,
      );
    }
    const dsJSON = await ds.json();
    const dsMetadata = dsJSON.objects[0];
    if (!dsMetadata?.id) {
      throw new Error(`Dataset not found: ${project}/${dataset}`);
    }

    let cursor: string | null = null;
    while (true) {
      const body: string = JSON.stringify({
        query: {
          from: {
            op: "function",
            name: { op: "ident", name: ["dataset"] },
            args: [{ op: "literal", value: dsMetadata.id }],
          },
          select: [{ op: "star" }],
          limit: perRequestLimit,
          cursor,
        },
        fmt: "jsonl",
        version,
      });
      const response = await fetch(`${BRAINTRUST_API_URL}/btql`, {
        method: "POST",
        headers,
        body,
      });
      if (!response.ok) {
        throw new Error(
          `Error fetching rows for ${dataset}: ${
            response.status
          }: ${await response.text()}`,
        );
      }

      cursor =
        response.headers.get("x-bt-cursor") ??
        response.headers.get("x-amz-meta-bt_cursor");

      // Parse jsonl line-by-line
      const allRows = await response.text();
      const rows = allRows.split("\n");
      let rowCount = 0;
      for (const row of rows) {
        if (!row.trim()) {
          continue;
        }
        yield JSON.parse(row);
        rowCount++;
      }

      if (rowCount === 0) {
        break;
      }
    }
  }

  async function main() {
    for await (const row of paginateDataset({
      project: "Your project name", // Replace with your project name
      dataset: "Your dataset name", // Replace with your dataset name
      perRequestLimit: 100,
    })) {
      console.log(row);
    }
  }

  main();
  ```
</CodeGroup>

### Timeouts

All requests time out after 30 seconds, which is enough time for interactive queries, dashboards, and most routine reads. To optimize queries, see [SQL best practices](/docs/reference/sql/best-practices).

## Examples

### Fetch experiment results

Query an experiment to check review status or other metrics:

<CodeGroup dropdown>
  ```python theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  import os
  import requests

  API_URL = "https://api.braintrust.dev"
  headers = {"Authorization": "Bearer " + os.environ["BRAINTRUST_API_KEY"]}

  def fetch_experiment_review_status(experiment_id: str) -> dict:
      # Replace "response quality" with the name of your review score column
      query = f"""
      SELECT
        sum(CASE WHEN scores."response quality" IS NOT NULL THEN 1 ELSE 0 END) AS reviewed,
        sum(CASE WHEN is_root THEN 1 ELSE 0 END) AS total
      FROM experiment('{experiment_id}')
      """

      return requests.post(
          f"{API_URL}/btql",
          headers=headers,
          json={"query": query, "fmt": "json"},
      ).json()

  EXPERIMENT_ID = "your-experiment-id"
  print(fetch_experiment_review_status(EXPERIMENT_ID))
  ```
</CodeGroup>

### Fetch child spans by trace metadata

Retrieve specific child spans based on trace-level metadata:

<CodeGroup dropdown>
  ```python theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  import os
  import requests

  API_URL = "https://api.braintrust.dev"
  headers = {"Authorization": "Bearer " + os.environ["BRAINTRUST_API_KEY"]}

  PROJECT_ID = "your-project-id"
  SPAN_NAME = "root"  # or any specific span name

  # Find all rows matching a certain metadata value
  query = f"""
  SELECT span_attributes, metrics
  FROM project_logs('{PROJECT_ID}', shape => 'traces')
  WHERE metadata.orgName = 'qawolf'
  LIMIT 10
  """

  response = requests.post(f"{API_URL}/btql", headers=headers, json={"query": query}).json()

  durations = []
  for trace in response["data"]:
      if trace["span_attributes"]["name"] == SPAN_NAME:
          metrics = trace["metrics"]
          if metrics.get("end") and metrics.get("start"):
              duration = metrics["end"] - metrics["start"]
              durations.append(duration)
              print(f"Duration: {duration}ms")
          else:
              print("Start or end not found for this span")

  if durations:
      print(f"\nAverage duration: {sum(durations) / len(durations)}ms")
  ```
</CodeGroup>

### Export data

Export logs, experiments, or datasets to JSON or Parquet by setting `fmt`.

<CodeGroup dropdown>
  ```bash theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  # Export to JSON
  curl https://api.braintrust.dev/btql \
    -H "Authorization: Bearer $BRAINTRUST_API_KEY" \
    -H "Content-Type: application/json" \
    -d '{
      "query": "SELECT * FROM project_logs('\''your-project-id'\'', shape => '\''traces'\'')",
      "fmt": "json"
    }' > export.json

  # Export to Parquet
  curl https://api.braintrust.dev/btql \
    -H "Authorization: Bearer $BRAINTRUST_API_KEY" \
    -H "Content-Type: application/json" \
    -d '{
      "query": "SELECT * FROM project_logs('\''your-project-id'\'', shape => '\''traces'\'')",
      "fmt": "parquet"
    }' > export.parquet
  ```

  ```python theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
  import os
  import requests

  API_URL = "https://api.braintrust.dev"
  headers = {"Authorization": "Bearer " + os.environ["BRAINTRUST_API_KEY"]}

  query = """
  SELECT *
  FROM project_logs('your-project-id', shape => 'traces')
  WHERE created >= now() - interval '7 days'
  """

  # JSON format
  response = requests.post(
      f"{API_URL}/btql",
      headers=headers,
      json={"query": query, "fmt": "json"},
  ).json()

  # Parquet format (returns binary data)
  response = requests.post(
      f"{API_URL}/btql",
      headers=headers,
      json={"query": query, "fmt": "parquet"},
  )
  with open("export.parquet", "wb") as f:
      f.write(response.content)
  ```
</CodeGroup>

## Next steps

* Learn the [SQL syntax](/docs/reference/sql) for querying logs, experiments, and datasets.
* Follow [SQL best practices](/docs/reference/sql/best-practices) to keep queries fast and within limits.
* Run queries from your terminal with [`bt sql`](/docs/reference/cli/sql).
* Review [system limits](/docs/plans-and-limits) for query timeouts and result-size caps.