> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cloudglue.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Structured Queries

> Query your extracted structured data — files, entities, and connector metadata — with read-only SQL

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](#querying-from-the-agent) |

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](#background-exports).

<Tip>
  Use [Search](/api-reference/endpoint/search/post) or [Deep
  Search](/api-reference/endpoint/deep-search/post) to *find* content ("where
  does anyone mention pricing?"). Use Query to *measure* it ("how many videos
  per source platform?", "which topics dominate our meetings?").
</Tip>

## When to Use Query vs Search

| You want to...                                         | Use                                                                                                     |
| ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------- |
| Find specific moments or answers in videos             | [Search](/api-reference/endpoint/search/post) / [Deep Search](/api-reference/endpoint/deep-search/post) |
| 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](/api-reference/endpoint/collections/files-entities)                                |

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

```bash theme={null}
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:

```json theme={null}
{
  "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}`](/api-reference/endpoint/query/get) and browse history with [`GET /query`](/api-reference/endpoint/query/list).

## The Virtual Schema

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

<Note>
  The `entities` and `segment_entities` tables carry each file's **most recent
  completed extraction only**. This is deliberately different from the
  [collection entities
  endpoint](/api-reference/endpoint/collections/list-collection-entities), which
  merges all extraction jobs — analytics must not double-count re-extractions.
</Note>

### `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](#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`](/api-reference/endpoint/query/schema) to see the virtual tables plus what each collection actually extracts:

```bash theme={null}
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:

```json theme={null}
{
  "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`:

  ```sql theme={null}
  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`:

  ```sql theme={null}
  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:

```sql theme={null}
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](#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:

```sql theme={null}
SELECT source, COUNT(*) AS file_count
FROM files
GROUP BY source
ORDER BY file_count DESC
```

Count videos in each collection:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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):

```sql theme={null}
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):

```sql theme={null}
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):

```sql theme={null}
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:

```sql theme={null}
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):

```sql theme={null}
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.

```bash theme={null}
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`.

<Tip>
  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.
</Tip>

## 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`.

```bash theme={null}
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
}'
```

```json theme={null}
{
  "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`:

```bash theme={null}
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}`](/api-reference/endpoint/query/get) until `status` is `completed` (or `failed`), then download from `download_url`:

```json theme={null}
{
  "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:

```bash theme={null}
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](/deep-dives/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](#background-exports).

<Tip>
  Not sure a query is right? Add [`dry_run: true`](#dry-run) to check it
  compiles and preview its columns before spending credits on a full run.
</Tip>