Structured Queries

The Structured Query API lets you run read-only SQL over the structured data Cloudglue extracts from your videos — file attributes, extracted entities, and connector source metadata. Where search finds moments and answers, structured queries count, group, join, and aggregate that data — so you can measure and report on your library at scale. You write ordinary SQL (SELECT only) against a small, well-defined set of virtual tables built from your collections. It is not a general database you write to — it is a read-only analytical view over your own extracted data.

Ways to Query

Mode	How	Best for
Synchronous	`POST /query` with `sql`	Interactive, dashboard-style queries — results inline in the response
Natural language	`POST /query` with `query`	Ask in plain English; Cloudglue compiles it to SQL and runs it
Dry run	add `dry_run: true`	Validate/compile and see the output columns without executing — cheaper
Background export	add `background: true`, `format: csv \| jsonl`	Large result sets streamed to a downloadable file (24h signed URL)
From the agent	`/v1/responses` (nimbus-002)	The reasoning agent runs structured queries for you — see below

Synchronous runs return small-to-moderate result sets in one round trip; for anything large, aggregate server-side, raise max_rows, or use a background export.

Use Search or Deep Search to find content (“where does anyone mention pricing?”). Use Query to measure it (“how many videos per source platform?”, “which topics dominate our meetings?”).

When to Use Query vs Search

You want to…	Use
Find specific moments or answers in videos	Search / Deep Search
Count, group, or aggregate across files and entities	Query
Join extracted entities to file properties or metadata	Query
Filter files by connector source metadata at scale	Query
Read raw extraction results for a single file	Entities endpoints

Running a Query

Send one or more collection IDs and a single read-only SELECT statement. Each query costs 2 credits; failed runs are refunded automatically. Retrieving results is free.

curl --request POST \
  --url https://api.cloudglue.dev/v1/query \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY" \
  --header 'Content-Type: application/json' \
  --data '{
  "collections": ["YOUR_COLLECTION_ID"],
  "sql": "SELECT source, COUNT(*) AS file_count FROM files GROUP BY source ORDER BY file_count DESC"
}'

The response is a query_result object with the echoed SQL, columns, rows, and usage:

{
  "id": "9b2f6c3e-6b1a-4a5e-9f0d-0f6e5f3a2c11",
  "object": "query_result",
  "status": "completed",
  "created_at": 1753315200000,
  "collections": ["YOUR_COLLECTION_ID"],
  "sql": "SELECT source, COUNT(*) AS file_count FROM files GROUP BY source ORDER BY file_count DESC",
  "columns": [
    { "name": "source", "type": "VARCHAR" },
    { "name": "file_count", "type": "BIGINT" }
  ],
  "rows": [
    { "source": "connector", "file_count": 42 },
    { "source": "upload", "file_count": 17 }
  ],
  "row_count": 2,
  "truncated": false,
  "usage": {
    "files_scanned": 59,
    "entity_rows": 240,
    "segment_entity_rows": 1180,
    "engine_ms": 12,
    "total_ms": 310
  },
  "error": null
}

Completed and failed runs are stored: re-fetch a run with GET /query/{id} and browse history with GET /query.

The Virtual Schema

Every query executes against three virtual tables built on the fly from the collections you select.

The entities and segment_entities tables carry each file’s most recent completed extraction only. This is deliberately different from the collection entities endpoint, which merges all extraction jobs — analytics must not double-count re-extractions.

`files`

One row per (file, collection) in the selected collections — a file that belongs to more than one selected collection appears once per collection. Files without extractions appear here but have no entities or segment_entities rows, so use LEFT JOIN when joining those tables, and join on both file_id and collection_id (see Joining across tables).

Column	Type	Description
`file_id`	UUID	File id
`collection_id`	UUID	Collection the file belongs to
`filename`	VARCHAR	Original filename
`uri`	VARCHAR	Source URI
`source`	VARCHAR	Ingestion source (upload, connector, url, …)
`title`	VARCHAR	Best-effort display title from connector source metadata, falling back to filename
`created_at`	TIMESTAMPTZ	When the file was ingested into Cloudglue (not when the content was recorded — recording time lives in `source_metadata`)
`bytes`	BIGINT	File size
`duration_seconds`	DOUBLE	Media duration in seconds
`width`	INTEGER	Video width in pixels
`height`	INTEGER	Video height in pixels
`has_audio`	BOOLEAN	Whether the media has an audio track
`metadata`	JSON	User-supplied file metadata
`source_metadata`	JSON	Connector source metadata (meeting info, links, …)

`entities`

File-level extracted entities, one row per (field, value element), from each file’s most recent completed extraction only. Files without a completed extraction have no rows here — LEFT JOIN from files on both file_id and collection_id to keep them.

Column	Type	Description
`file_id`	UUID	File id
`collection_id`	UUID	Collection the extraction belongs to
`field`	VARCHAR	Entity field name from the extract schema
`value`	JSON	Entity value as JSON (string, object, …)
`value_text`	VARCHAR	Text rendering of the value for grouping/filtering (objects/arrays are JSON-stringified)

`segment_entities`

Segment-level extracted entities, one row per segment, from each file’s most recent completed extraction only (same job as the entities table). Files without a completed extraction have no rows here — LEFT JOIN from files on both file_id and collection_id to keep them.

Column	Type	Description
`file_id`	UUID	File id
`collection_id`	UUID	Collection the extraction belongs to
`segment_index`	INTEGER	Zero-based segment index
`start_time`	DOUBLE	Segment start time in seconds
`end_time`	DOUBLE	Segment end time in seconds
`entities`	JSON	Extracted entities for the segment as JSON

Discovering Your Fields

Before writing SQL, call GET /query/schema to see the virtual tables plus what each collection actually extracts:

curl --request GET \
  --url 'https://api.cloudglue.dev/v1/query/schema?collections=YOUR_COLLECTION_ID' \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY"

Alongside the three tables, the response includes a per-collection summary with the collection’s verbatim extract configuration:

{
  "object": "query_schema",
  "tables": [
    { "name": "files", "description": "...", "columns": [ ... ] },
    { "name": "entities", "description": "...", "columns": [ ... ] },
    { "name": "segment_entities", "description": "...", "columns": [ ... ] }
  ],
  "collections": [
    {
      "collection_id": "YOUR_COLLECTION_ID",
      "fields": [
        { "name": "company", "type": "string", "level": "file" },
        { "name": "people", "type": "list", "level": "segment" }
      ],
      "extract_schema": {
        "company": "The company being discussed",
        "people": [{ "name": "Person's name", "role": "Their role" }]
      },
      "prompt": "Extract the people who appear and the company discussed."
    }
  ]
}

fields is a flat summary: each extracted field’s name, type, and level.
extract_schema is the collection’s extract schema verbatim — it shows the nested value shapes, so you know which JSON paths exist before writing json_extract expressions.
prompt is the collection’s extraction prompt verbatim, when one was configured.

Where each field lives

The level on each field tells you which table to query:

level: "file" — the field appears as rows in the entities long table. Query it via field / value / value_text:

SELECT value_text AS company, COUNT(*) AS files
FROM entities
WHERE field = 'company'
GROUP BY value_text
ORDER BY files DESC

level: "segment" — the field lives inside the segment_entities.entities JSON column. Use json_extract / json_extract_string with a path derived from the extract_schema:

SELECT file_id, start_time,
       json_extract_string(entities, '$.people[0].name') AS first_person
FROM segment_entities
WHERE json_extract(entities, '$.people') IS NOT NULL

Metadata collections have extract_schema: null — there is no extraction to query. Query them through the files table’s metadata and source_metadata JSON columns instead.

Joining across tables

The same file can belong to more than one of the collections you select, in which case it appears once per collection in files (and its entities appear once per collection in entities / segment_entities). Always join on both file_id and collection_id so a shared file doesn’t cross-join across collections:

SELECT f.title, count(*) AS entity_rows
FROM files f
LEFT JOIN entities e USING (file_id, collection_id)
GROUP BY f.title

Dialect and Restrictions

A query must be a single read-only SELECT statement. Everything else is rejected:

DDL and DML (CREATE, INSERT, UPDATE, DELETE, DROP, …)
ATTACH, COPY, SET, PRAGMA, INSTALL, LOAD
Multiple statements in one request

Filesystem and network access are disabled, and each query runs in an isolated context that can only see your account’s data for the selected collections. Standard SQL is supported, including JSON functions (json_extract, json_extract_string, ->, ->>), analytics functions (date_trunc, window functions), and CTEs. See the worked examples below for the JSON extraction syntax. BIGINT values within the safe integer range are returned as JSON numbers.

Worked Examples

Structured queries span file attributes (duration, dimensions, size, timestamps), user metadata, connector source metadata, and extracted entities — you can filter, aggregate, and join across all of them. Count files per ingestion source:

SELECT source, COUNT(*) AS file_count
FROM files
GROUP BY source
ORDER BY file_count DESC

Count videos in each collection:

SELECT collection_id, COUNT(*) AS video_count
FROM files
GROUP BY collection_id
ORDER BY video_count DESC

Filter by file attributes — videos longer than 20 seconds:

SELECT filename, ROUND(duration_seconds, 1) AS seconds
FROM files
WHERE duration_seconds > 20
ORDER BY seconds DESC

Find portrait (“skinny”) videos by aspect ratio:

SELECT filename, width, height, ROUND(width::DOUBLE / height, 2) AS aspect_ratio
FROM files
WHERE width < height
ORDER BY aspect_ratio ASC

Filter segments by duration — segments shorter than 20 seconds:

SELECT file_id, segment_index, ROUND(end_time - start_time, 1) AS seg_seconds
FROM segment_entities
WHERE (end_time - start_time) < 20
ORDER BY seg_seconds ASC

Aggregate a segment-level field across a collection (given a segment-level topic field in the extract schema):

SELECT json_extract_string(entities, '$.topic') AS topic,
       COUNT(*) AS segments,
       ROUND(SUM(end_time - start_time)) AS total_seconds
FROM segment_entities
GROUP BY topic
ORDER BY segments DESC

Filter files by connector source metadata (here, Zoom recordings by host):

SELECT title,
       json_extract_string(source_metadata, '$.host_email') AS host,
       duration_seconds
FROM files
WHERE json_extract_string(source_metadata, '$.source_type') = 'zoom'
  AND json_extract_string(source_metadata, '$.host_email') LIKE '%@example.com'
ORDER BY created_at DESC

Filter by user-supplied metadata — files that have a project key set (use IS NOT NULL to test for a key’s presence):

SELECT filename, json_extract_string(metadata, '$.project') AS project
FROM files
WHERE json_extract(metadata, '$.project') IS NOT NULL

Match inside an array of source-metadata objects — Grain recordings with a given participant email. The [*] wildcard path extracts the whole list of email values, then list_contains tests membership:

SELECT title,
       json_extract_string(source_metadata, '$.participants[*].email') AS participant_emails
FROM files
WHERE source = 'grain'
  AND list_contains(
        json_extract_string(source_metadata, '$.participants[*].email'),
        'amy@example.com'
      )

Find files that have no completed extraction yet (LEFT JOIN keeps them):

SELECT f.title, COUNT(e.field) AS entity_rows
FROM files f
LEFT JOIN entities e USING (file_id, collection_id)
GROUP BY f.title
HAVING COUNT(e.field) = 0

Natural Language Queries

Instead of sql, send a query in plain English. Cloudglue compiles it to SQL against the same virtual schema and runs it, returning the compiled statement in sql so you can inspect, tweak, and resubmit it as a regular sql query.

curl --request POST \
  --url https://api.cloudglue.dev/v1/query \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY" \
  --header 'Content-Type: application/json' \
  --data '{
  "collections": ["YOUR_COLLECTION_ID"],
  "query": "how many files mention each company, most first?"
}'

The response is the same query_result shape as a SQL query, with sql set to the compiled statement. Natural language runs cost 4 credits (refunded if compilation fails). Provide exactly one of sql or query.

Natural language is best for straightforward analytical questions — counts, rankings, group-bys. A question that needs deep nested-JSON unnesting is hit-or-miss; when a question can’t be compiled it fails with 422 rather than guessing, so write the SQL directly for those.

Dry Run

Add dry_run: true to validate — and, for a natural-language query, compile — a statement and get back the effective SQL plus its output column schema without executing it over any data. No rows are produced, and it is billed at a reduced rate: 1 credit for sql, 2 for a natural-language query.

curl --request POST \
  --url https://api.cloudglue.dev/v1/query \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY" \
  --header 'Content-Type: application/json' \
  --data '{
  "collections": ["YOUR_COLLECTION_ID"],
  "sql": "SELECT source, COUNT(*) AS n FROM files GROUP BY source",
  "dry_run": true
}'

{
  "status": "completed",
  "dry_run": true,
  "sql": "SELECT source, COUNT(*) AS n FROM files GROUP BY source",
  "columns": [
    { "name": "source", "type": "VARCHAR" },
    { "name": "n", "type": "BIGINT" }
  ],
  "rows": null,
  "row_count": 0
}

Use it to confirm a query compiles and to preview its columns before running it for real. dry_run cannot be combined with background.

Background Exports

For result sets too large to return inline, run the query as a background export. It streams the full result to a gzipped CSV or JSONL file in cloud storage and hands back a 24-hour signed download URL. Set background: true and a format:

curl --request POST \
  --url https://api.cloudglue.dev/v1/query \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY" \
  --header 'Content-Type: application/json' \
  --data '{
  "collections": ["YOUR_COLLECTION_ID"],
  "sql": "SELECT file_id, filename, duration_seconds FROM files ORDER BY filename",
  "background": true,
  "format": "csv"
}'

The call returns immediately with status: "in_progress" and an id. Poll GET /query/{id} until status is completed (or failed), then download from download_url:

{
  "id": "…",
  "status": "completed",
  "row_count": 1240,
  "output_bytes": 48213,
  "download_url": "https://…signed…",
  "download_expires_at": 1753401600000
}

The URL is valid for 24 hours and is not refreshed on read — if it expires, re-run the export.
Output is capped at 2 GB compressed; a run that would exceed it fails with an actionable error and is refunded.
Natural-language (query) exports are supported too — the query is compiled server-side before streaming.
Billing: exports reserve 4 credits, reconciled to +1 credit per 100 MB of compressed output on completion. A failed or cancelled export is refunded.

Cancelling an export

POST /query/{id}/cancel stops an in-progress export — the run is aborted mid-stream (the partial upload is discarded) and the reserved credits are refunded:

curl --request POST \
  --url https://api.cloudglue.dev/v1/query/YOUR_QUERY_ID/cancel \
  --header "Authorization: Bearer $CLOUDGLUE_API_KEY"

Cancelling a run that has already completed returns it unchanged.

Querying from the Agent

The Responses API agent (nimbus-002-preview) can run structured queries on your behalf. When an entity collection is part of its knowledge base, the model is given get_structured_data_schema and run_sql_query tools and decides when to query — e.g. to answer “how many meetings had more than three attendees?” it writes and runs the SQL itself. Each query it runs is surfaced as a cloudglue_query_call item in the response output (with the SQL and row counts; result rows are not included), so you never call /v1/query directly.

Limits, Credits, and Errors

Limit	Value
Collections per query	20
SQL length	20,000 characters
Files in scope	2,000 completed files across the selected collections
Flattened dataset	250,000 total entity + segment entity rows
`max_rows`	Up to 10,000 rows per result (default 1,000)
Execution time	15 seconds per query
Concurrency	1 concurrent query per account; excess returns `429`
Rate limit	60 requests per minute on `POST /query`
Credits	Per `POST /query`: SQL 2, natural language 4, dry-run 1–2; exports reserve 4 + reconcile +1 / 100 MB. Refunded on failure/cancel; `GET` endpoints are free

Query shape — the number of output columns, joins, CTEs, and nesting depth — is not capped explicitly. Instead it is bounded by the dataset and execution-time limits above: a query that is too large or too complex fails with a 408 (time) or a 400 rather than a dedicated limit error. Aggregate server-side and select only the columns you need to stay well within these bounds.

Status	Meaning
`400`	Invalid request or rejected SQL — multiple statements, a non-`SELECT` statement, SQL that could not be parsed, or a face-analysis collection in scope
`402`	Insufficient credit balance
`404`	Collection or query result not found
`408`	Query exceeded the 15-second execution limit
`409`	The selected collections exceed the maximum queryable dataset size
`422`	A natural-language `query` could not be compiled into valid SQL — refine the question, or send `sql` directly
`429`	Too many concurrent queries, or the per-minute rate limit was exceeded

Caveats

Latest extraction only. entities and segment_entities reflect each file’s most recent completed extraction. Re-extracting a file replaces its rows in query results; historical extractions are not visible here.
Truncation. Results are truncated at max_rows, and very large results are also capped when stored — re-fetching such a run via GET /query/{id} replays the truncated rows. Check the truncated flag; if it is true, narrow the query, aggregate further, or run a background export.

Not sure a query is right? Add dry_run: true to check it compiles and preview its columns before spending credits on a full run.

Getting Started

Connect Your Data

Core Concepts

Deep Dives

Structured Queries

Ways to Query

When to Use Query vs Search

Running a Query

The Virtual Schema

`files`

`entities`

`segment_entities`

Discovering Your Fields

Where each field lives

Joining across tables

Dialect and Restrictions

Worked Examples

Natural Language Queries

Dry Run

Background Exports

Cancelling an export

Querying from the Agent

Limits, Credits, and Errors

Caveats

​Ways to Query

​When to Use Query vs Search

​Running a Query

​The Virtual Schema

​files

​entities

​segment_entities

​Discovering Your Fields

​Where each field lives

​Joining across tables

​Dialect and Restrictions

​Worked Examples

​Natural Language Queries

​Dry Run

​Background Exports

​Cancelling an export

​Querying from the Agent

​Limits, Credits, and Errors

​Caveats

Ways to Query

When to Use Query vs Search

Running a Query

The Virtual Schema

`files`

`entities`

`segment_entities`

Discovering Your Fields

Where each field lives

Joining across tables

Dialect and Restrictions

Worked Examples

Natural Language Queries

Dry Run

Background Exports

Cancelling an export

Querying from the Agent

Limits, Credits, and Errors

Caveats