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

# SQL functions and operators

> Look up Braintrust SQL functions and operators for comparison, text, date/time, JSON, cost, and aggregation, with definitions and examples.

This page catalogs the operators and functions available in Braintrust SQL queries. For clauses, data sources, and field access, see [SQL query structure](https://docs.braintrust.dev/reference/sql/query-structure.md). For copy-and-run patterns, see [Example queries](https://docs.braintrust.dev/reference/sql/examples.md).

## SQL operators

Use these operators in `WHERE`, `HAVING`, `SELECT`, and other clauses.

### Comparison operators

| Operator | Description | Example |
| - | - | - |
| `=` | Equal to (alias `eq`). | `metadata.model = 'gpt-4'` |
| `!=` | Not equal to (alias `ne`; `<>` also works). | `status != 'error'` |
| `>` | Greater than (alias `gt`). | `scores.Factuality > 0.8` |
| `<` | Less than (alias `lt`). | `metrics.tokens < 1000` |
| `>=` | Greater than or equal (alias `ge`). | `created >= now() - interval 1 day` |
| `<=` | Less than or equal (alias `le`). | `scores.Factuality <= 0.5` |
| `<=>` | Null-safe equal: true if both sides are equal, or both are null. | `metadata.env <=> null` |
| `<!=>` | Null-safe not-equal: true if values differ, treating null as a distinct value. | `metadata.env <!=> 'prod'` |
| `IN` | Value exists in a list of values. | `metadata.model IN ('gpt-4', 'gpt-4-turbo')` |
| `NOT IN` | Value does not exist in a list of values. | `error.type NOT IN ('timeout')` |

### Null operators

| Operator | Description | Example |
| - | - | - |
| `IS NULL` | Value is null. For JSON fields, this includes missing keys and empty or null-only objects and arrays. See [Null checks on JSON fields](https://docs.braintrust.dev/reference/sql/filtering.md#null-checks-on-json-fields). | `error IS NULL` |
| `IS NOT NULL` | Value is not null. | `scores.Factuality IS NOT NULL` |

### Text-matching operators

| Operator | Description | Example |
| - | - | - |
| `LIKE` | Case-sensitive pattern match with the `%` wildcard (strings under 65KB). | `input LIKE 'How%'` |
| `NOT LIKE` | Negated case-sensitive pattern match. | `input NOT LIKE 'How%'` |
| `ILIKE` | Case-insensitive pattern match with the `%` wildcard (strings under 65KB). | `input ILIKE '%question%'` |
| `NOT ILIKE` | Negated case-insensitive pattern match. | `input NOT ILIKE '%spam%'` |
| `MATCH` | Full-word search. Indexed and fast, but matches whole terms only (`'app'` does not match `'apple'`). | `output MATCH 'timeout'` |
| `NOT MATCH` | Negated full-word search. | `output NOT MATCH 'timeout'` |

For regular expressions, use [`regexp_like`](#regular-expression-functions). For wildcard behavior and the 65KB limit, see [Pattern matching](https://docs.braintrust.dev/reference/sql/query-structure.md#pattern-matching). For `MATCH` and `search()`, see [Full-text search](https://docs.braintrust.dev/reference/sql/query-structure.md#full-text-search).

### Logical operators

| Operator | Description | Example |
| - | - | - |
| `AND` | Both conditions must be true. | `error IS NULL AND scores.Factuality > 0.8` |
| `OR` | Either condition must be true. | `status = 'error' OR metrics.latency > 5000` |
| `NOT` | Unary operator that negates a condition. | `NOT metadata.is_production` |

### Arithmetic operators

| Operator | Description | Example |
| - | - | - |
| `+` | Addition (alias `add`). | `metrics.prompt_tokens + metrics.completion_tokens` |
| `-` | Subtraction (alias `sub`). | `now() - interval 7 day` |
| `*` | Multiplication (alias `mul`). | `metrics.tokens * 2` |
| `/` | Division (alias `div`). | `count(error) / count(1)` |
| `%` | Modulo (alias `mod`). | `hour(created) % 2` |
| `-x` | Unary negation (alias `neg`). | `-metrics.latency` |

> [!NOTE]
>
> The infix operators `INCLUDES`, `NOT INCLUDES`, and `CONTAINS` raise a syntax error in SQL mode. Use [`contains(text, substring)`](#string-functions) to find a case-sensitive substring. For exact array membership, use [`array_contains(array, value)`](#array-functions), `IN`, or `NOT IN`.
>
> `MATCH` searches for tokens, so `tags MATCH 'value'` also matches `value-added`.

## SQL functions

Use these functions in `SELECT`, `WHERE`, and `GROUP BY` clauses, and in aggregate measures.

### Date and time functions

| Function | Description | Example |
| - | - | - |
| `second(timestamp)` | Truncate a timestamp to the start of its second (same as `date_trunc('second', timestamp)`). | `second(created)` |
| `minute(timestamp)` | Truncate a timestamp to the start of its minute (same as `date_trunc('minute', timestamp)`). | `minute(created)` |
| `hour(timestamp)` | Truncate a timestamp to the start of its hour (same as `date_trunc('hour', timestamp)`). | `hour(created)` |
| `day(timestamp)` | Truncate a timestamp to the start of its day (same as `date_trunc('day', timestamp)`). | `day(created)` |
| `week(timestamp)` | Truncate a timestamp to the start of its week, beginning on Monday (same as `date_trunc('week', timestamp)`). | `week(created)` |
| `month(timestamp)` | Truncate a timestamp to the start of its month (same as `date_trunc('month', timestamp)`). | `month(created)` |
| `year(timestamp)` | Truncate a timestamp to the start of its year (same as `date_trunc('year', timestamp)`). | `year(created)` |
| `date_trunc(interval, timestamp)` | Truncate a timestamp to a specified interval bucket. | `date_trunc('day', created)` |
| `date_part(part, timestamp)` | Return a part of a timestamp, such as the year or day of the week, as a number. | `date_part('dow', created)` |
| `EXTRACT(part FROM timestamp)` | Standard SQL form of `date_part`. | `EXTRACT(YEAR FROM created)` |
| `current_timestamp()` | Current timestamp (alias `now()`). | `now() - interval 7 day` |
| `current_date()` | Current date. | `current_date()` |

`date_trunc` accepts `'second'`, `'minute'`, `'hour'`, `'day'`, `'week'`, `'month'`, or `'year'`, or a positive integer multiple of `second(s)`, `minute(s)`, `hour(s)`, or `day(s)` (for example, `'15 minutes'` or `'2 hours'`). The `'week'`, `'month'`, and `'year'` intervals don't accept multiples because they have variable durations.

#### `date_part` and `EXTRACT` fields

Field names are case-insensitive and follow PostgreSQL conventions.

| Field | Returns |
| - | - |
| `year`, `quarter`, `month`, `day`, `hour`, or `minute` | That part of the timestamp. |
| `second` | Seconds, including fractional seconds. |
| `millisecond(s)` or `microsecond(s)` | The seconds field in milliseconds or microseconds, including whole seconds. For example, `12.5` seconds returns `12500` milliseconds. |
| `week` | The ISO week number, from `1` to `53`. |
| `isoyear` | The ISO week-numbering year, which can differ from `year` in the first or last few days of a year. |
| `dow` | The day of the week, from `0` (Sunday) to `6` (Saturday). |
| `isodow` | The ISO day of the week, from `1` (Monday) to `7` (Sunday). |
| `doy` | The day of the year, from `1` to `366`. |
| `epoch` | Seconds since the Unix epoch, including fractional seconds. |

Timestamps can be RFC 3339 strings or `YYYY-MM-DD` dates, which are treated as midnight UTC. `epoch` doesn't depend on the time zone. Other fields use the query's time zone, which the [`tz_offset`](https://docs.braintrust.dev/api-reference/query#query-data) parameter sets (UTC by default). A null timestamp returns null, and an unsupported field or a malformed timestamp returns an error.

### String functions

| Function | Description | Example |
| - | - | - |
| `lower(text)` | Convert text to lowercase. | `lower(metadata.model)` |
| `upper(text)` | Convert text to uppercase. | `upper(status)` |
| `length(text)` | Number of characters (Unicode code points, not bytes). Aliases `char_length` and `character_length`. | `length(output)` |
| `octet_length(text)` | Number of bytes in the text's UTF-8 encoding. | `octet_length(output)` |
| `bit_length(text)` | Number of bits in the text's UTF-8 encoding. | `bit_length(output)` |
| `strpos(text, substring)` | Position of the first occurrence of `substring`, or `0` if it doesn't occur. Also written `position(substring IN text)`. | `strpos(input, 'error')` |
| `contains(text, substring)` | Return true if the text contains the case-sensitive literal substring. | `contains(input, 'error')` |
| `instr(text, substring)` | Alias for `strpos`. Return the first occurrence's 1-based position in Unicode code points, or `0` if absent. | `instr(input, 'error')` |
| `substring(text, start [, count])` | Up to `count` characters starting at position `start`, or the rest of the text when you omit `count`. Alias `substr`. Also written `substring(text FROM start [FOR count])`. Positions before `1` count toward `count` but return no characters, and a negative `count` returns an error. | `substring(input, 1, 100)` |
| `left(text, n)` | First `n` characters. A negative `n` returns all but the last `-n` characters. | `left(metadata.request_id, 8)` |
| `right(text, n)` | Last `n` characters. A negative `n` returns all but the first `-n` characters. | `right(metadata.request_id, 4)` |
| `trim(text [, characters])` | Remove any of `characters` (a space by default) from both ends of the text. Alias `btrim`. Also written `trim([BOTH \| LEADING \| TRAILING] [characters] FROM text)`. | `trim(metadata.user_id)` |
| `ltrim(text [, characters])` | Remove any of `characters` (a space by default) from the start of the text. | `ltrim(metadata.path, '/')` |
| `rtrim(text [, characters])` | Remove any of `characters` (a space by default) from the end of the text. | `rtrim(metadata.path, '/')` |
| `replace(text, from, to)` | Replace every occurrence of `from` with `to`. An empty `from` returns the text unchanged. | `replace(metadata.model, '-latest', '')` |
| `reverse(text)` | Reverse the order of the characters. | `reverse(metadata.path)` |
| `split_part(text, delimiter, n)` | The `n`th field after splitting the text on `delimiter`. A negative `n` counts from the end, an out-of-range `n` returns an empty string, and `0` returns an error. | `split_part(metadata.model, '/', 1)` |
| `starts_with(text, prefix)` | Return true if the text starts with `prefix` (case-sensitive). | `starts_with(metadata.model, 'gpt-')` |
| `ends_with(text, suffix)` | Return true if the text ends with `suffix` (case-sensitive). | `ends_with(metadata.path, '.json')` |
| `concat(text1, text2, ...)` | Concatenate strings. | `concat(metadata.model, '-', status)` |
| `concat_ws(separator, value1, value2, ...)` | Join values with `separator`, skipping null values. Return NULL if `separator` is NULL. | `concat_ws('/', metadata.provider, metadata.model)` |
| `is_uuid(text)` | Return true if the text parses as a UUID, false otherwise. Accepts hyphenated, unhyphenated, braced (`{...}`), and URN (`urn:uuid:...`) forms of any UUID version. Return NULL if the argument is NULL, so `NOT is_uuid(field)` doesn't match rows where the field is NULL or missing. | `is_uuid(metadata.user_id)` |

Character positions are 1-based and count Unicode code points. Except for `concat` and `concat_ws`, these functions return NULL when any argument is NULL. `concat_ws` and `regexp_replace` return an error when a single result exceeds 16 MiB.

### Regular expression functions

| Function | Description | Example |
| - | - | - |
| `regexp_like(text, pattern [, flags])` | Return true if `pattern` matches anywhere in the text. | `regexp_like(output, 'time ?out', 'i')` |
| `regexp_replace(text, pattern, replacement [, flags])` | Replace the first match of `pattern` with `replacement`, or every match with the `g` flag. Return the text unchanged when nothing matches. | `regexp_replace(input, '\d{3}-\d{4}', '<phone>', 'g')` |
| `regexp_substr(text, pattern [, start [, occurrence [, flags [, group]]]])` | Return a match of `pattern`, or NULL if there's no match. By default, return the whole first match from the start of the text. Set `group` to return a capture group instead. | `regexp_substr(input, 'user_(\d+)', 1, 1, '', 1)` |
| `regexp_extract(text, pattern [, group])` | Return the first capture group, or the whole match if the pattern has no capture groups. Pass `0` for the whole match. Return NULL if the match or group is absent or any argument is NULL. An invalid pattern or negative group returns an error. | `regexp_extract(input, 'user_([0-9]+)')` |
| `regexp_count(text, pattern [, start [, flags]])` | Count the non-overlapping matches of `pattern`. | `regexp_count(output, 'error', 1, 'i')` |

Patterns use [Rust `regex` syntax](https://docs.rs/regex/latest/regex/#syntax), which differs from PostgreSQL. Backreferences, lookaround, and Unicode word boundaries aren't supported. Use `(?-u:\b)` for an ASCII word boundary. When several alternatives match at the same position, the first one wins, not the longest.

In replacement text, `\1` through `\9` insert a capture group, `\&` inserts the whole match, and `\\` inserts a backslash. `$` has no special meaning.

`start` is a 1-based position that counts Unicode code points. `occurrence` picks which non-overlapping match `regexp_substr` returns, so `occurrence = 2` returns the second match. Both must be positive. Any NULL argument returns NULL. An invalid pattern, an unsupported flag, or a pattern that compiles to more than 10 MiB returns an error.

#### Flags

Combine flags in one string, such as `'ig'`. When flags conflict, the last one wins.

| Flag | Effect |
| - | - |
| `i` | Match case-insensitively. |
| `c` | Match case-sensitively (default). |
| `x` | Ignore whitespace and allow `#` comments in the pattern. |
| `t` | Treat whitespace and `#` as literal characters (default). |
| `g` | Replace every match. Only `regexp_replace` accepts it. |
| `s` | `.` matches a newline. `^` and `$` match only at the start and end of the text (default). |
| `n` or `m` | `.` doesn't match a newline. `^` and `$` also match at line breaks. |
| `p` | `.` doesn't match a newline. `^` and `$` match only at the start and end of the text. |
| `w` | `.` matches a newline. `^` and `$` also match at line breaks. |

### Numeric functions

| Function | Description | Example |
| - | - | - |
| `abs(number)` | Absolute value. | `abs(scores.Factuality - scores.Relevance)` |
| `ceil(number)` | Round up to the nearest integer. Alias `ceiling`. | `ceil(metrics.latency)` |
| `floor(number)` | Round down to the nearest integer. | `floor(metrics.latency)` |
| `sign(number)` | Return `-1`, `0`, or `1` for a negative, zero, or positive number. | `sign(scores.Factuality - 0.5)` |
| `round(number [, precision])` | Round to `precision` decimal places (`0` by default). Ties round away from zero. | `round(scores.Factuality, 2)` |
| `trunc(number [, precision])` | Truncate toward zero to `precision` decimal places (`0` by default). | `trunc(metrics.latency, 1)` |
| `mod(dividend, divisor)` | Remainder of a division, the same as the `%` operator. Return NULL if `divisor` is `0`. | `mod(metrics.tokens, 1000)` |

A negative `precision` rounds or truncates to the left of the decimal point, so `round(1234, -2)` returns `1200`. `abs`, `ceil`, `floor`, and `sign` return an integer for an integer input. These functions return NULL when any argument is NULL, and return an error when a result overflows the supported integer or floating-point range.

### Array functions

| Function | Description | Example |
| - | - | - |
| `len(expr)` | Length of an array. Returns `1` for scalar values (string, number, or boolean), `0` for null, and NULL for objects. To count the characters in a string, use [`length`](#string-functions). | `len(tags)` |
| `array_contains(array, value)` | Test exact membership in a native or JSON-encoded array. Numeric values such as `1` and `1.0` compare equal. | `array_contains(tags, 'reviewed')` |

`array_contains` returns NULL for invalid JSON, nonarrays, or NULL arguments. If the value is absent, it returns NULL when the array contains a NULL element and false otherwise.

### JSON functions

| Function | Description | Example |
| - | - | - |
| `json_extract(json_str, path)` | Extract a value from a JSON-encoded string field using a JSONPath expression. | `json_extract(output, 'choices[0].message.content')` |
| `to_json(value)` | Preserve the native value, including strings and NULL. | `to_json(metadata)` |
| `to_json_string(value)`, `json_format(value)` | Serialize a value as JSON text, including quotes around strings. Return NULL for NULL input. Results are limited to 16 MiB. | `to_json_string(metadata)` |
| `json_array_length(value)` | Count elements in a native or JSON-encoded array, including NULL elements. Return NULL for invalid JSON, nonarrays, or NULL. | `json_array_length('[1, null, 3]')` |
| `json_extract_path(value, key, ...)` | Extract from native or JSON-encoded input using literal keys. Dots within a key are literal. Pass array indexes as strings, starting at `'0'`. Negative indexes count from the end. | `json_extract_path(output, 'choices', '0', 'message')` |
| `json_extract_string(value, path)` | Extract from native or JSON-encoded input using the same JSONPath syntax as `json_extract`. Return strings unquoted and other non-NULL values as JSON text. | `json_extract_string(output, '$.choices[0].message.content')` |

`json_extract_path` and `json_extract_string` return NULL for invalid JSON, a missing path, or a NULL path or value.

Use `json_extract` when a field's *value* is a JSON-encoded string rather than a native object. For native object and array access, use [field access](https://docs.braintrust.dev/reference/sql/query-structure.md#field-access) instead. See [Extract data from JSON strings](https://docs.braintrust.dev/reference/sql/examples.md#extract-data-from-json-strings) for worked examples.

### Null-handling functions

| Function | Description | Example |
| - | - | - |
| `coalesce(val1, val2, ...)` | Return the first non-null value. | `coalesce(metadata.env, 'unknown')` |
| `nullif(val1, val2)` | Return null if `val1` equals `val2`. | `nullif(status, '')` |
| `least(val1, val2, ...)` | Return the smallest non-null value. | `least(metrics.latency, 60)` |
| `greatest(val1, val2, ...)` | Return the largest non-null value. | `greatest(scores.a, scores.b)` |

> [!NOTE]
>
> `coalesce(field, 'x') != 'y'` is automatically rewritten to `field <!=> 'y'` for better index performance.

### Type conversion and cast functions

| Function | Description | Example |
| - | - | - |
| `to_string(value)` | Cast a value to string (alias `toString`). | `to_string(metrics.tokens)` |
| `to_boolean(value)` | Cast a value to boolean. | `to_boolean(metadata.flag)` |
| `to_integer(value)` | Cast a value to integer. | `to_integer(metadata.count)` |
| `to_number(value)` | Cast a value to number. | `to_number(metadata.score)` |
| `to_date(value)` | Cast a value to date. | `to_date(metadata.day)` |
| `to_datetime(value)` | Cast a value to datetime. | `to_datetime(metadata.ts)` |
| `to_interval(value)` | Cast a value to interval. | `to_interval('7 day')` |

### Search functions

| Function | Description | Example |
| - | - | - |
| `search(query)` | Full-text search across all text fields (`input`, `output`, `expected`, `metadata`, `span_attributes`). | `search('timeout')` |
| `match(expr, query)` | Perform the same full-word search as the [`MATCH` operator](#text-matching-operators). | `match(output, 'timeout')` |

`search(query)` is equivalent to writing `input MATCH query OR output MATCH query OR ...` for each text field. When [log search optimization](https://docs.braintrust.dev/admin/projects.md#speed-up-log-filtering) is enabled on the project, `search()` uses bloom filters to skip irrelevant segments automatically. Shingled search optimization extends this to multi-word and phrase queries. See [Full-text search](https://docs.braintrust.dev/reference/sql/query-structure.md#full-text-search).

Queries with a very large number of `MATCH` or `search()` terms can exceed the [query limits](https://docs.braintrust.dev/reference/sql/best-practices.md#query-limits) and be blocked.

### Cost functions

| Function | Description | Example |
| - | - | - |
| `estimated_cost()` | Estimated cost of a span in USD. Uses `metrics.estimated_cost` when available; otherwise computes from token metrics and model-registry pricing. Works in the `spans`, `traces`, and `summary` shapes, and inside aggregates. | `sum(estimated_cost())` |
| `estimated_cost_breakdown()` | Per-component costs for a span as a serialized JSON string (not a structured object). Returns NULL when cost cannot be computed. | `estimated_cost_breakdown()` |
| `estimated_cost_component(name)` | Numeric cost for a single component of the breakdown. Returns NULL when cost cannot be computed or `name` is unrecognized. | `estimated_cost_component('completionTokensCost')` |

> [!NOTE]
>
> On the `spans` and `traces` shapes, `estimated_cost()` and `metrics.estimated_cost` are populated on LLM spans, not the root/task span. Filtering to `is_root = true` returns null cost. To get a trace's total cost, sum across its spans with `sum(estimated_cost())`. The `summary` shape rolls this up per trace automatically.
>
> Valid component names for `estimated_cost_breakdown()` and `estimated_cost_component()`: `promptUncachedTokensCost`, `promptCachedTokensCost`, `promptCacheCreationTokensCost` (single-tier cache creation), `promptCacheCreation5mTokensCost` and `promptCacheCreation1hTokensCost` (two-tier cache creation, e.g., Anthropic), `completionTokensCost`, and `totalCost`.

### Aggregate functions

Aggregate functions are available only in measures or with [`GROUP BY`](https://docs.braintrust.dev/reference/sql/query-structure#group-by-for-aggregations).

| Function | Description | Example |
| - | - | - |
| `count(expr)` | Number of rows. | `count(1)` |
| `count_distinct(expr)` | Number of distinct values. | `count_distinct(root_span_id)` |
| `count_if(expr)` | Number of rows where `expr` is true (alias `countIf`). Equivalent to `count(CASE WHEN expr THEN 1 ELSE NULL END)`. | `count_if(error IS NOT NULL)` |
| `sum(expr)` | Sum of numeric values. | `sum(metrics.total_tokens)` |
| `avg(expr)` | Mean (average) of numeric values. | `avg(scores.Factuality)` |
| `min(expr)` | Minimum value. | `min(metrics.latency)` |
| `max(expr)` | Maximum value. | `max(metrics.latency)` |
| `any_value(expr)` | An arbitrary non-null value from the group. | `any_value(metadata.tenant_id)` |
| `array_agg(value)` | Collect group values, including NULLs, into an array in unspecified order. Return NULL for empty input. | `array_agg(id)` |
| `percentile(expr, p)` | A percentile where `p` is between 0 and 1. | `percentile(metrics.latency, 0.95)` |
| `GROUPING(expr)` | Returns `1` if `expr` is a rolled-up (subtotal or grand-total) dimension in the current `ROLLUP`/`GROUPING SETS` row, `0` if it is a real value. | `GROUPING(metadata.model)` |

`array_agg` accepts up to 100,000 values and 16 MiB of serialized values per group. `DISTINCT`, in-call `ORDER BY`, and `FILTER` return errors.

## Conditional expressions

SQL supports conditional logic through `CASE WHEN` for multi-branch conditions, `IF` for two-branch conditions, and `COUNT_IF` for conditional aggregation.

### `CASE WHEN`

Use `CASE WHEN ... THEN ... ELSE ... END` for one or more branches:

```sql theme={null}
-- Basic conditions
SELECT
  CASE WHEN scores.Factuality > 0.8 THEN 'high' ELSE 'low' END AS quality,
  CASE WHEN error IS NOT NULL THEN -1 ELSE metrics.tokens END AS valid_tokens
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
```

```sql theme={null}
-- Nested conditions
SELECT
  CASE
    WHEN scores.Factuality > 0.9 THEN 'excellent'
    WHEN scores.Factuality > 0.7 THEN 'good'
    WHEN scores.Factuality > 0.5 THEN 'fair'
    ELSE 'poor'
  END AS rating
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
```

```sql theme={null}
-- Use in calculations
SELECT
  CASE WHEN metadata.model = 'gpt-4' THEN metrics.tokens * 2 ELSE metrics.tokens END AS adjusted_tokens,
  CASE WHEN error IS NULL THEN metrics.latency ELSE 0 END AS valid_latency
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
```

### `IF` and `COUNT_IF`

For two-branch conditions, `IF(condition, then_value, else_value)` is a shorter alternative to `CASE WHEN`. Use `CASE WHEN` when you need more than two branches.

```sql theme={null}
-- IF as a shorter alternative to a two-branch CASE WHEN
SELECT
  IF(scores.Factuality > 0.8, 'high', 'low') AS quality,
  IF(error IS NOT NULL, -1, metrics.tokens) AS valid_tokens
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
```

`COUNT_IF(condition)` counts rows where the condition is true. It is equivalent to `count(CASE WHEN condition THEN 1 ELSE NULL END)`.

```sql theme={null}
-- Count high-quality and errored spans per model
SELECT
  metadata.model AS model,
  count(1) AS total,
  COUNT_IF(scores.Factuality > 0.8) AS high_quality,
  COUNT_IF(error IS NOT NULL) AS errored
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
GROUP BY metadata.model
```

`IF` composes with other aggregates. For example, count distinct users who hit a condition:

```sql theme={null}
SELECT count(DISTINCT IF(error IS NOT NULL, metadata.user_id, NULL)) AS users_with_errors
FROM project_logs('my-project-id')
WHERE created > now() - interval 7 day
```

## Next steps

* Learn how to structure queries in the [SQL reference](https://docs.braintrust.dev/reference/sql.md).
* Browse copy-and-run patterns in [Example queries](https://docs.braintrust.dev/reference/sql/examples.md).
* Optimize slow or incorrect queries with [SQL best practices](https://docs.braintrust.dev/reference/sql/best-practices.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.