Documentation / UQL

UQL Syntax

The full UQL request shape — bridge, collection, select, where, group_by, aggregations, order_by, limit, offset — plus every write operation (insert, update, delete, upsert/merge) and the exact records/set/where shapes each one takes.

This page is the structural reference for a UQL request: every field a read accepts, every write operation and the shape it requires, and the rules that decide what happens where. It is written for report authors, pipeline developers, and anyone driving UQL over the MCP query_data tool. For the operator catalog see Filter Operators Reference; for how the engine decides what runs at the source, see Pushdown & capability semantics.

The read request

A UQL read targets one collection on one bridge and returns a flat array of row objects — one object per record, one key per field — regardless of which system produced them.

{
  "bridge": "MSSQL.production",
  "collection": "dbo.orders",
  "select": ["order_id", "customer", "region", "amount", "order_date"],
  "where": {
    "status": { "$eq": "shipped" },
    "amount": { "$gte": 1000 }
  },
  "order_by": [{ "field": "order_date", "direction": "desc" }],
  "limit": 100,
  "offset": 0
}

Field	Type	Required	Description
`bridge`	string	Yes	The connection to query, as `SYSTEM.name` (e.g. `MSSQL.production`).
`collection`	string	Yes	The collection ID exactly as returned by schema discovery.
`containers`	array of strings	Sometimes	Database / schema / board path. Required where a system has more than one — e.g. a Monday board id, or a SQL server with multiple schemas.
`select`	array of strings	No	Fields to return. Omit to return all fields the collection exposes. Over MCP this may also be a comma-separated string.
`where`	object	No	Filter conditions (see below and the operators reference).
`group_by`	array of strings	No	Fields to group by. Pair with `aggregations`.
`aggregations`	array of objects	No	Aggregate functions over the group (see Grouping).
`having`	object	No	A `where`-shaped filter applied to grouped/aggregated rows.
`order_by`	array of objects	No	Sort spec: `{ "field": "...", "direction": "asc" \| "desc" }`.
`distinct`	boolean	No	Return distinct rows.
`limit`	integer	No	Maximum rows. Over MCP the default is 100 and the ceiling is 100,000.
`offset`	integer	No	Rows to skip, for pagination.
`joins`	array of objects	No	Join another collection on the same bridge (SQL sources). Cross-bridge joins are not supported — stage through the workspace instead.

Selecting and paging

select projects a subset of fields; leaving it out returns every field the collection exposes. On a wide table over MCP, an empty select combined with large rows can trip a response_too_large guard that protects an agent's context — name the fields you need.

limit and offset page through large result sets. The second page of 100 rows:

{ "bridge": "MSSQL.production", "collection": "dbo.orders", "limit": 100, "offset": 100 }

When limit/offset can be pushed safely depends on the adapter's pushdown profile — a source that cannot filter natively is not allowed to pre-limit before Nexus applies the filter, because that would drop the wrong rows. This is handled for you; see the pushdown page.

Filtering with `where`

The where object maps a field name to a value or an operator expression. Multiple fields at the top level combine with AND; you can also compose explicit boolean logic with $and, $or, and $not.

{
  "where": {
    "region": { "$in": ["West", "Southwest"] },
    "amount": { "$gte": 1000, "$lt": 50000 },
    "$or": [
      { "status": { "$eq": "shipped" } },
      { "status": { "$eq": "invoiced" } }
    ]
  }
}

Values are always sent as typed data and bind as parameters — a string in where is never concatenated into a query. The complete operator set, their pushdown eligibility, and per-connector support are in the operators reference.

Grouping and aggregation

To summarize rather than list rows, name the group keys in group_by and the aggregate functions in aggregations. Each aggregation carries a type, an optional field (omit for count), and an alias for the output column.

{
  "bridge": "MSSQL.production",
  "collection": "dbo.orders",
  "group_by": ["region"],
  "aggregations": [
    { "type": "count", "alias": "order_count" },
    { "type": "sum", "field": "amount", "alias": "total_revenue" },
    { "type": "avg", "field": "amount", "alias": "avg_order" }
  ],
  "where": { "status": { "$eq": "shipped" } },
  "having": { "total_revenue": { "$gt": 100000 } },
  "order_by": [{ "field": "total_revenue", "direction": "desc" }]
}

Supported functions are sum, count, avg, min, and max (reports also expose median). This returns one row per region with the named aliases as columns. On a source that can aggregate natively (any SQL bridge, the workspace, NetSuite's SuiteQL surface) the grouping is pushed down and only summarized rows return. On a source whose API has no aggregation grammar (QuickBooks Online, Monday.com, a REST record surface), Nexus performs the identical grouping in-engine — the result is the same, computed from the rows the source returns, bounded by a post-process row budget. Grouped/aggregated reads are typically authored as saved reports; over MCP, query_data accepts group_by + aggregations directly.

Sorting

order_by is a list, so you can sort by several fields. Direction is asc (default) or desc; the direction keyword is validated against an allowlist.

{ "order_by": [
    { "field": "region", "direction": "asc" },
    { "field": "amount", "direction": "desc" }
] }

Writes

Writes use the same bridge + collection addressing, plus an operation and the payload that operation needs. The valid operations are insert (alias create), update, delete, and upsert (alias merge). Which of these a bridge supports is declared by the connector; merge in particular is only offered by SQL bridges, NetSuite, and the workspace. Every write is RBAC-gated (a write-scoped grant) and writability-gated (views and read-only collections are refused before the call).

Insert

Post one or more full records. records is a list of row dicts.

{
  "bridge": "MSSQL.production",
  "collection": "dbo.customers",
  "operation": "insert",
  "records": [
    { "name": "Acme Holdings", "region": "West", "status": "active" }
  ]
}

Update

Apply a change set to matched rows. On SQL bridges, pass set (a {column: value} map that becomes the SET clause) scoped by where. Without set, a SQL update fails clean rather than touching every row.

{
  "bridge": "MSSQL.production",
  "collection": "dbo.invoices",
  "operation": "update",
  "set": { "status": "paid", "paid_at": "2026-06-30" },
  "where": { "invoice_id": { "$eq": 4821 } }
}

On record-based REST bridges (Monday, NetSuite) an update addresses one object by id. Pass record_id, or a where on the id / external-id field which the adapter resolves to a record reference (NetSuite: id → internal id, externalid → the native eid:<value> path). Sparse-update systems like QuickBooks Online change only the fields you supply.

{
  "bridge": "NETSUITE.production",
  "collection": "customer",
  "operation": "update",
  "where": { "externalid": { "$eq": "DTM-ACME" } },
  "set": { "comments": "Reviewed 2026-06-30" }
}

Delete

delete requires a where (SQL bridges) or a record_id / records entry (record bridges).

{
  "bridge": "MSSQL.production",
  "collection": "dbo.staging_rows",
  "operation": "delete",
  "where": { "batch_id": { "$eq": "2026-06-30-A" } }
}

Upsert / merge

upsert (alias merge) inserts-or-updates on a key in one call, avoiding a read-then-write race. Pass records; on SQL and workspace bridges also pass key_columns (the conflict target — INSERT ... ON CONFLICT (<keys>) / a T-SQL MERGE). On NetSuite the record's externalId is the upsert key, so key_columns is not needed.

{
  "bridge": "MSSQL.production",
  "collection": "dbo.dim_customer",
  "operation": "upsert",
  "records": [
    { "customer_id": "C-1001", "name": "Acme Holdings", "region": "West" }
  ],
  "key_columns": ["customer_id"]
}

{
  "bridge": "NETSUITE.production",
  "collection": "customer",
  "operation": "upsert",
  "records": [
    { "externalId": "DTM-ACME", "companyName": "Acme Holdings" }
  ]
}

Write payloads at a glance

Operation	Aliases	SQL bridge needs	Record bridge (Monday / NetSuite) needs
`insert`	`create`	`records`	`records`
`update`	—	`set` + `where`	`record_id` (or a `where` on the id) + `set`
`delete`	—	`where`	`record_id` (or `records`)
`upsert`	`merge`	`records` + `key_columns`	`records` (each carrying its `externalId`)

In a pipeline

Inside a Python pipeline the same shapes are passed to the bridge handle:

async def run(ctx):
    open_ar = await ctx.adapters.netsuite.read({
        "collection": "transaction",
        "where": {"status": {"$eq": "open"}, "amount": {"$gt": 10000}},
        "order_by": [{"field": "trandate", "direction": "desc"}],
    })
    await ctx.workspace.upsert("staged_open_ar", open_ar, key=["tranid"])
    ctx.logger.info("staged %d rows", len(open_ar))

Reads through ctx.adapters.<bridge>.read(...) are shaped by the same pushdown rules as reports and query_data. See the Pipeline SDK for the full ctx surface.

Discover before you query

Collection IDs and field names come from schema discovery, not guesswork. Over MCP the flow is list_bridges → discover_schema → query_data (or a saved report). Running discovery first is what makes the exact-match requirements above safe: you are always querying against IDs the source just confirmed.

Reference summary

Target: bridge + collection (+ containers where a system has several).
Shape the output: select, order_by, distinct, limit, offset.
Filter: where with the operator catalog; compose with $and / $or / $not.
Summarize: group_by + aggregations (+ having).
Write: operation ∈ insert / update / delete / upsert, with records, set, where, record_id, or key_columns as the operation requires.
Always discover first so IDs and field names are exact.