Braintrust Query Language (BTQL)

-- Get specific fields
select:
  metadata.model as model,
  scores.Factuality as score,
  created as timestamp
from: project_logs('my-project-id')

select:
  -- Simple field access
  metadata.model,

  -- Computed values
  metrics.tokens > 1000 as is_long_response,

  -- Conditional logic
  (scores.Factuality > 0.8 ? "high" : "low") as quality
from: project_logs('my-project-id')

select:
  -- Date/time functions
  day(created) as date,
  hour(created) as hour,

  -- Numeric calculations
  round(scores.Factuality, 2) as rounded_score
from: project_logs('my-project-id')

select: *
from: project_logs('my-project-id') spans
filter: span_attributes.type = 'llm' and metrics.time_to_first_token > 0.1
limit: 10

filter:
  any_span(tags includes "production") and
  any_span(error IS NOT NULL)

-- Match any input containing "question"
filter: input ILIKE '%question%'

-- Match inputs starting with "How"
filter: input LIKE 'How%'

-- Match emails ending with specific domains
filter: metadata.email ILIKE '%@braintrust.com'

-- Escape literal wildcards with backslash
filter: metadata.description LIKE '%50\% off%'  -- Matches "50% off"

-- Examples with different units
select: *
from: project_logs('my-project-id')
filter:
  created > now() - interval 7 day and    -- Last week
  created > now() - interval 1 month      -- Last month

-- Sort by single field
sort: created desc

-- Sort by multiple fields
sort: scores.Factuality desc, created asc

-- Sort by computed values
sort: len(tags) desc

pivot: <measure1>, <measure2>, ...

-- Compare model performance metrics across models
dimensions: day(created) as date
measures:
  avg(scores.Factuality) as avg_factuality,
  avg(metrics.tokens) as avg_tokens,
  count(1) as call_count
from: project_logs('my-project-id')
pivot: avg_factuality, avg_tokens, call_count

-- Results will look like:
-- {
--   "date": "2024-01-01",
--   "gpt-4_avg_factuality": 0.92,
--   "gpt-4_avg_tokens": 150,
--   "gpt-4_call_count": 1000,
--   "gpt-3.5-turbo_avg_factuality": 0.85,
--   "gpt-3.5-turbo_avg_tokens": 120,
--   "gpt-3.5-turbo_call_count": 2000
-- }

-- Compare metrics across time periods
dimensions: metadata.model as model
measures:
  avg(scores.Factuality) as avg_score,
  percentile(latency, 0.95) as p95_latency
from: project_logs('my-project-id')
pivot: avg_score, p95_latency

-- Results will look like:
-- {
--   "model": "gpt-4",
--   "0_avg_score": 0.91,
--   "0_p95_latency": 2.5,
--   "1_avg_score": 0.89,
--   "1_p95_latency": 2.8,
--   ...
-- }

-- Compare tag distributions across models
dimensions: tags[0] as primary_tag
measures: count(1) as tag_count
from: project_logs('my-project-id')
pivot: tag_count

-- Results will look like:
-- {
--   "primary_tag": "quality",
--   "gpt-4_tag_count": 500,
--   "gpt-3.5-turbo_tag_count": 300
-- }

-- Convert wide format to long format for arbitrary scores
dimensions: created as date
measures: count(1) as count
from: project_logs('my-project-id')
unpivot: count as (score_name, score_value)

-- Results will look like:
-- {
--   "date": "2024-01-01",
--   "score_name": "Factuality",
--   "score_value": 0.92
-- },
-- {
--   "date": "2024-01-01",
--   "score_name": "Coherence",
--   "score_value": 0.88
-- }

-- Basic limit
limit: 100

-- Pagination using cursor (only works without sort)
select: *
from: project_logs('<PROJECT_ID>')
limit: 100
cursor: '<CURSOR_TOKEN>'  -- From previous query response

-- Offset-based pagination with sorting
-- Page 1 (first 100 results)
select: *
from: project_logs('<PROJECT_ID>')
sort: created desc
limit: 100

-- Page 2 (next 100 results)
select: *
from: project_logs('<PROJECT_ID>')
filter: created < '2024-01-15T10:30:00Z'  -- Last created timestamp from previous page
sort: created desc
limit: 100

-- Comparison operators
=           -- Equal to (alias for 'eq')
!=          -- Not equal to (alias for 'ne', can also use '<>')
>           -- Greater than (alias for 'gt')
<           -- Less than (alias for 'lt')
>=          -- Greater than or equal (alias for 'ge')
<=          -- Less than or equal (alias for 'le')
IN          -- Check if value exists in a list of values

-- Null operators
IS NULL     -- Check if value is null
IS NOT NULL -- Check if value is not null
ISNULL      -- Unary operator to check if null
ISNOTNULL   -- Unary operator to check if not null

-- Text matching
LIKE        -- Case-sensitive pattern matching (supports % wildcard)
NOT LIKE    -- Negated case-sensitive pattern matching
ILIKE       -- Case-insensitive pattern matching (supports % wildcard)
NOT ILIKE   -- Negated case-insensitive pattern matching
MATCH       -- Full-word semantic search (faster but requires exact word matches, e.g. 'apple' won't match 'app')
NOT MATCH   -- Negated full-word semantic search

-- Array operators
INCLUDES    -- Check if array/object contains value (alias: CONTAINS)
NOT INCLUDES -- Check if array/object does not contain value

-- Logical operators
AND         -- Both conditions must be true
OR          -- Either condition must be true
NOT         -- Unary operator to negate condition

-- Arithmetic operators
+           -- Addition (alias: add)
-           -- Subtraction (alias: sub)
*           -- Multiplication (alias: mul)
/           -- Division (alias: div)
%           -- Modulo (alias: mod)
-x          -- Unary negation (alias: neg)

-- Date/time functions
second(timestamp)          -- Extract second from timestamp
minute(timestamp)         -- Extract minute from timestamp
hour(timestamp)          -- Extract hour from timestamp
day(timestamp)           -- Extract day from timestamp
week(timestamp)          -- Extract week from timestamp
month(timestamp)         -- Extract month from timestamp
year(timestamp)          -- Extract year from timestamp
current_timestamp()      -- Get current timestamp (alias: now())
current_date()          -- Get current date

-- String functions
lower(text)                       -- Convert text to lowercase
upper(text)                       -- Convert text to uppercase
concat(text1, text2, ...)         -- Concatenate strings

-- Array functions
len(array)                        -- Get length of array
contains(array, value)            -- Check if array contains value (alias: includes)

-- Null handling functions
coalesce(val1, val2, ...)        -- Return first non-null value
nullif(val1, val2)               -- Return null if val1 equals val2
least(val1, val2, ...)           -- Return smallest non-null value
greatest(val1, val2, ...)        -- Return largest non-null value

-- Type conversion
round(number, precision)          -- Round to specified precision

-- Cast functions
to_string(value)                 -- Cast value to string
to_boolean(value)                -- Cast value to boolean
to_integer(value)                -- Cast value to integer
to_number(value)                 -- Cast value to number
to_date(value)                   -- Cast value to date
to_datetime(value)               -- Cast value to datetime
to_interval(value)               -- Cast value to interval

-- Aggregate functions (only in measures)
count(expr)                       -- Count number of rows
count_distinct(expr)              -- Count number of distinct values
sum(expr)                        -- Sum numeric values
avg(expr)                        -- Calculate mean of numeric values
min(expr)                        -- Find minimum value
max(expr)                        -- Find maximum value
percentile(expr, p)              -- Calculate percentile (p between 0 and 1)

-- Object field access
metadata.model             -- Access nested object field  e.g. {"metadata": {"model": "value"}}
metadata."field name"      -- Access field with spaces	  e.g. {"metadata": {"field name": "value"}}
metadata."field-name"      -- Access field with hyphens   e.g. {"metadata": {"field-name": "value"}}
metadata."field.name"      -- Access field with dots	  e.g. {"metadata": {"field.name": "value"}}

-- Array access (0-based indexing)
tags[0]                    -- First element
tags[-1]                   -- Last element

-- Combined array and object access
metadata.models[0].name    -- Field in first array element
responses[-1].tokens       -- Field in last array element
spans[0].children[-1].id   -- Nested array traversal

-- Basic conditions
select:
  (scores.Factuality > 0.8 ? "high" : "low") as quality,
  (error IS NOT NULL ? -1 : metrics.tokens) as valid_tokens
from: project_logs('my-project-id')

-- Nested conditions
select:
  (scores.Factuality > 0.9 ? "excellent" :
   scores.Factuality > 0.7 ? "good" :
   scores.Factuality > 0.5 ? "fair" : "poor") as rating
from: project_logs('my-project-id')

-- Use in calculations
select:
  (metadata.model = "gpt-4" ? metrics.tokens * 2 : metrics.tokens) as adjusted_tokens,
  (error IS NULL ? metrics.latency : 0) as valid_latency
from: project_logs('my-project-id')

{
  "time": "2024-11-09T00:00:00Z",
  "total_tokens": 100000,
  "input_tokens": 50000,
  "output_tokens": 50000
}

-- Exclude known error types from analysis
select: *
from: project_logs('<PROJECT_ID>')
filter:
  error IS NOT NULL and
  error.type NOT IN ["rate_limit", "timeout", "network_error"] and
  metadata.is_production = true and
  created > now() - interval 1 day
sort: created desc
limit: 100

-- Track p95 latency by endpoint
dimensions:
  metadata.endpoint as endpoint,
  hour(created) as hour
measures:
  percentile(metrics.latency, 0.95) as p95_latency,
  percentile(metrics.latency, 0.50) as median_latency,
  count(1) as requests
filter: created > now() - interval 1 day
sort: hour desc, p95_latency desc

-- Find slow requests
select:
  metadata.endpoint,
  metrics.latency,
  metrics.tokens,
  input,
  created
from: project_logs('<PROJECT_ID>')
filter:
  metrics.latency > 5000 and  -- Requests over 5 seconds
  created > now() - interval 1 hour
sort: metrics.latency desc
limit: 20

-- Track prompt token efficiency
dimensions:
  metadata.prompt_template as template,
  day(created) as date
measures:
  avg(metrics.prompt_tokens) as avg_prompt_tokens,
  avg(metrics.completion_tokens) as avg_completion_tokens,
  avg(metrics.completion_tokens) / avg(metrics.prompt_tokens) as token_efficiency,
  avg(scores.Factuality) as avg_factuality
filter: created > now() - interval 7 day
sort: date desc, token_efficiency desc

-- Find similar prompts
select: *
from: project_logs('<PROJECT_ID>')
filter:
  input MATCH 'explain the concept of recursion' and
  scores.Factuality > 0.8
sort: created desc
limit: 10

-- Monitor feedback patterns
dimensions:
  tags[0] as primary_tag,
  metadata.model as model
measures:
  count(1) as feedback_count,
  avg(scores.Factuality > 0.8 ? 1 : 0) as high_quality_rate
filter:
  tags includes 'feedback' and
  created > now() - interval 30 day
sort: feedback_count desc

-- Track issue resolution
select:
  created,
  tags,
  metadata.model,
  scores.Factuality,
  response
from: project_logs('<PROJECT_ID>')
filter:
  tags includes 'needs-review' and
  NOT tags includes 'resolved' and
  created > now() - interval 1 day
sort: scores.Factuality asc