Skip to main content
This page catalogs the operators and functions available in Braintrust SQL queries. For clauses, data sources, and field access, see SQL query structure. For copy-and-run patterns, see Example queries.

SQL operators

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

Comparison operators

Null operators

Text-matching operators

For regular expressions, use regexp_like. For wildcard behavior and the 65KB limit, see Pattern matching. For MATCH and search(), see Full-text search.

Logical operators

Arithmetic operators

The infix operators INCLUDES, NOT INCLUDES, and CONTAINS raise a syntax error in SQL mode. Use contains(text, substring) to find a case-sensitive substring. For exact array membership, use array_contains(array, value), 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

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. 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 parameter sets (UTC by default). A null timestamp returns null, and an unsupported field or a malformed timestamp returns an error.

String functions

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

Patterns use Rust 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.

Numeric functions

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

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

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 instead. See Extract data from JSON strings for worked examples.

Null-handling functions

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

Type conversion and cast functions

Search functions

search(query) is equivalent to writing input MATCH query OR output MATCH query OR ... for each text field. When log search optimization 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. Queries with a very large number of MATCH or search() terms can exceed the query limits and be blocked.

Cost functions

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. 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:

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.
COUNT_IF(condition) counts rows where the condition is true. It is equivalent to count(CASE WHEN condition THEN 1 ELSE NULL END).
IF composes with other aggregates. For example, count distinct users who hit a condition:

Next steps