Execute DQL Queries

from docent import Docent

client = Docent()

result = client.execute_dql(
    "my-collection-id",
    "SELECT id, metadata_json->>'model' AS model FROM agent_runs LIMIT 10",
)

result = client.execute_dql(
    "my-collection-id",
    "SELECT id AS run_id, metadata_json->>'model' AS model FROM agent_runs LIMIT 5",
)

rows = client.dql_result_to_dicts(result)
for row in rows:
    print(row)  # {"run_id": "abc-123", "model": "gpt-4"}

result = client.execute_dql(
    collection_id,
    """
    SELECT id, metadata_json->>'score' AS score
    FROM agent_runs
    WHERE metadata_json->>'model' = 'gpt-4'
      AND CAST(metadata_json->>'score' AS DOUBLE PRECISION) > 0.8
    """
)

result = client.execute_dql(
    collection_id,
    """
    SELECT
        ar.id,
        jr.output->>'label' AS label,
        jr.output->>'explanation' AS explanation
    FROM agent_runs ar
    JOIN judge_results jr ON ar.id = jr.agent_run_id
    WHERE jr.rubric_id = 'rubric-123'
    """
)

result = client.execute_dql(
    collection_id,
    """
    SELECT
        jr.output->>'label' AS label,
        COUNT() AS count
    FROM judge_results jr
    WHERE jr.rubric_id = 'rubric-123'
    GROUP BY jr.output->>'label'
    """
)

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
  AVG(CAST(metadata_json->>'latency_ms' AS DOUBLE PRECISION)) AS avg_latency_ms
FROM agent_runs
WHERE metadata_json ? 'latency_ms';

jsonb_array_length(convert_from(messages, 'UTF8')::jsonb)

SELECT DISTINCT ar.id AS agent_run_id
FROM agent_runs ar
JOIN transcripts t ON t.agent_run_id = ar.id
WHERE jsonb_array_length(convert_from(t.messages, 'UTF8')::jsonb) >= 10;

SELECT
  transcript_id,
  agent_run_id,
  message_count
FROM (
  SELECT
    t.id AS transcript_id,
    t.agent_run_id,
    jsonb_array_length(convert_from(t.messages, 'UTF8')::jsonb) AS message_count
  FROM transcripts t
) AS counted
WHERE message_count >= 10
ORDER BY message_count DESC;

SELECT
  id,
  meta->'conversation'->>'speaker' AS speaker,
  meta->'conversation'->>'topic' AS topic
FROM (
  SELECT
    id,
    convert_from(metadata_json, 'UTF8')::jsonb AS meta
  FROM transcripts
) AS t
WHERE meta->>'status' = 'flagged';

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
  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;

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