Docent Query Language (DQL)

from docent import Docent

client = Docent()

# Execute a DQL query
result = client.execute_dql(
    collection_id="your-collection-id",
    dql="SELECT id, name FROM agent_runs LIMIT 10"
)

# Result contains columns, rows, and metadata
print(result["columns"])  # ['id', 'name']
print(result["rows"])     # [[...], [...], ...]

schema = client.get_dql_schema("your-collection-id")
# Explore available tables and their columns
for table in schema["tables"]:
    print(f"{table['name']}: {[col['name'] for col in table['columns']]}")

# Convert to list of dictionaries
result = client.execute_dql("your-collection-id", "SELECT id, name FROM agent_runs")
rows = client.dql_result_to_dicts(result)
for row in rows:
    print(f"Run: {row['name']} (ID: {row['id']})")

# Convert to pandas DataFrame (experimental)
df = client.dql_result_to_df_experimental(result)
print(df.head())

result = client.execute_dql(
    collection_id="my-collection",
    dql="SELECT id, name FROM agent_runs WHERE metadata_json->>'status' = 'completed' LIMIT 10"
)

schema = client.get_dql_schema("my-collection")
# Explore available tables and their columns
for table in schema["tables"]:
    print(f"{table['name']}: {[col['name'] for col in table['columns']]}")

result = client.execute_dql("your-collection-id", "SELECT id, name FROM agent_runs")
rows = client.dql_result_to_dicts(result)
for row in rows:
    print(f"Run: {row['name']} (ID: {row['id']})")

result = client.execute_dql("your-collection-id", "SELECT id, name, created_at FROM agent_runs")
df = client.dql_result_to_df_experimental(result)
print(df.head())

# Get IDs of completed runs from production
run_ids = client.select_agent_run_ids(
    "your-collection-id",
    where_clause="metadata_json->>'status' = 'completed' AND metadata_json->>'environment' = 'prod'",
    limit=100
)

-- Filter agent runs by a metadata attribute
SELECT id, name
FROM agent_runs
WHERE metadata_json->>'environment' = 'staging';

-- Cast numeric metadata for aggregation
SELECT
  AVG(CAST(metadata_json->>'latency_ms' AS DOUBLE PRECISION)) AS avg_latency_ms
FROM agent_runs
WHERE metadata_json ? 'latency_ms';

SELECT id, name FROM agent_runs
WHERE metadata_json->>'environment' = 'prod'

SELECT id, name, created_at FROM agent_runs
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC

SELECT
  metadata_json->>'model' AS model,
  COUNT() AS run_count
FROM agent_runs
GROUP BY metadata_json->>'model'
ORDER BY run_count DESC

SELECT id, name FROM agent_runs
WHERE metadata_json ? 'custom_field'

SELECT id, name FROM agent_runs
WHERE CAST(metadata_json->>'score' AS DOUBLE PRECISION) > 0.8

SELECT
  ar.name AS run_name,
  t.name AS transcript_name
FROM agent_runs ar
JOIN transcripts t ON t.agent_run_id = ar.id

SELECT
  id,
  name,
  metadata_json->'model'->>'name' AS model_name,
  created_at
FROM agent_runs
WHERE metadata_json->>'status' = 'completed'
ORDER BY created_at DESC
LIMIT 10;

WITH normalized_runs AS (
  SELECT
    metadata_json->>'environment' AS environment,
    metadata_json->>'status' AS status
  FROM agent_runs
  WHERE metadata_json ? 'environment'
)
SELECT
  environment,
  COUNT() AS total_runs,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_runs,
  CAST(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS DOUBLE PRECISION)
    / NULLIF(COUNT(), 0) AS completion_rate
FROM normalized_runs
GROUP BY environment
ORDER BY total_runs DESC;

SELECT
  tg.id AS group_id,
  tg.name AS group_name,
  COUNT(t.id) AS transcript_count
FROM transcript_groups tg
JOIN transcripts t ON t.transcript_group_id = tg.id
GROUP BY tg.id, tg.name
HAVING COUNT(t.id) > 1
ORDER BY transcript_count DESC;

SELECT
  tg.id AS group_id,
  tg.name AS group_name,
  COUNT(t.id) AS transcript_count
FROM transcript_groups tg
LEFT JOIN transcripts t
  ON t.transcript_group_id = tg.id
  AND t.collection_id = tg.collection_id
WHERE tg.metadata_json->>'priority' = 'must_have'
GROUP BY tg.id, tg.name
HAVING COUNT(t.id) = 0
ORDER BY group_name;

SELECT
  jr.agent_run_id,
  jr.rubric_id,
  jr.result_metadata->>'label' AS label,
  jr.output->>'score' AS score
FROM judge_results jr
WHERE jr.result_metadata->>'severity' = 'high'
  AND EXISTS (
    SELECT 1
    FROM agent_runs ar
    WHERE ar.id = jr.agent_run_id
      AND ar.metadata_json->>'environment' = 'prod'
  )
ORDER BY score DESC
LIMIT 25;

WITH latest_scores AS (
  SELECT
    agent_run_id,
    MAX(rubric_version) AS rubric_version
  FROM judge_results
  WHERE rubric_id = 'helpful_response_v1'
  GROUP BY agent_run_id
)
SELECT
  ar.id,
  ar.metadata_json->'model'->>'name' AS model_name,
  jr.output->>'score' AS score,
  jr.result_metadata->>'label' AS label
FROM latest_scores ls
JOIN judge_results jr
  ON jr.agent_run_id = ls.agent_run_id
  AND jr.rubric_version = ls.rubric_version
  AND jr.rubric_id = 'helpful_response_v1'
JOIN agent_runs ar ON ar.id = jr.agent_run_id
WHERE ar.metadata_json->>'environment' = 'prod'
ORDER BY CAST(jr.output->>'score' AS DOUBLE PRECISION) DESC
LIMIT 15;

-- Wrong: string comparison
WHERE metadata_json->>'score' > '0.5'

-- Correct: numeric comparison
WHERE CAST(metadata_json->>'score' AS DOUBLE PRECISION) > 0.5

-- First page
SELECT id, name FROM agent_runs LIMIT 1000 OFFSET 0
-- Second page
SELECT id, name FROM agent_runs LIMIT 1000 OFFSET 1000