Nexus
Get Nexus
Documentation / UQL

UQL Examples

A copy-paste cookbook of UQL queries — filtered reads, ranges and lists, text and pattern matching, boolean composition, cross-field expressions, group-by aggregation, and every write operation — that you can adapt to any connected ERP, database, or SaaS source.

A cookbook of worked UQL queries you can adapt to your own connected systems. Every example uses the same request shape, so a pattern you learn against one bridge works against the next. The data below is generic — swap in your real collection and field names (run schema discovery first if you do not know them). For the full field reference see UQL Syntax; for the operator catalog see Filter Operators Reference.

The basic read request is an object addressed to a bridge and collection:

{
  "bridge": "QBO.production",
  "collection": "Invoices",
  "select": ["InvoiceId", "Customer", "AmountDue", "DueDate"],
  "where": { "Status": { "$eq": "Open" } },
  "limit": 500
}

From a Python pipeline the same read is await ctx.adapters.<bridge>.read({...}). Filters in where push down to the source when the connector supports it, so the source does the work; when it does not, Nexus applies the filter in-engine and the result is the same (see Pushdown & capability semantics).

1. Filtered read — open AR over a threshold

Return open receivables above 10,000. Multiple keys in one where object are combined with AND.

{
  "bridge": "QBO.production",
  "collection": "Invoices",
  "select": ["InvoiceId", "Customer", "AmountDue", "DueDate"],
  "where": {
    "Status":    { "$eq": "Open" },
    "AmountDue": { "$gt": 10000 }
  },
  "order_by": [{ "field": "DueDate", "direction": "asc" }],
  "limit": 1000
}

2. AR aging — invoices past due

Use a date comparison to find everything due before a cutoff. Pass dates as ISO strings; the connector renders them into the source's native date literal.

{
  "bridge": "QBO.production",
  "collection": "Invoices",
  "select": ["InvoiceId", "Customer", "AmountDue", "DueDate"],
  "where": {
    "Status":  { "$eq": "Open" },
    "DueDate": { "$lt": "2026-06-01" }
  }
}

3. Range and list filters

$between is an inclusive range; $in matches any value in a list; $not_in excludes a list.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Orders",
  "where": {
    "OrderDate": { "$between": ["2026-01-01", "2026-03-31"] },
    "Region":    { "$in": ["West", "Southwest"] },
    "Channel":   { "$not_in": ["Internal"] }
  }
}

4. Text and pattern matching

$contains does a substring match with the value's wildcards escaped; $starts_with/$ends_with cover prefix and suffix; $like is a raw pattern where you supply the wildcards (%/* for any run, _/? for one character). Case sensitivity follows the source's collation.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Customers",
  "select": ["CustomerId", "Name", "Email"],
  "where": {
    "Name":  { "$contains": "acme" },
    "Email": { "$ends_with": "@example.com" },
    "Sku":   { "$like": "INV-2026-%" }
  }
}

5. Boolean composition with $or

Compose alternatives with $or (a list of sub-clauses); combine with top-level AND'd fields and negate with $not.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Orders",
  "where": {
    "Region": { "$eq": "West" },
    "$or": [
      { "Status": { "$eq": "Open" } },
      { "$and": [
        { "Status": { "$eq": "Invoiced" } },
        { "AmountDue": { "$gt": 10000 } }
      ] }
    ]
  }
}

6. Cross-field expression with $expr

$expr compares one field to another, or writes a source expression no operator covers. Because it passes an expression to the source, it is an author-only, trusted surface: it is available in saved reports and pipelines, not in ad-hoc query_data, and only on SQL-backed sources.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Invoices",
  "select": ["InvoiceId", "AmountDue", "AmountPaid"],
  "where": {
    "$expr": "AmountPaid < AmountDue"
  }
}

7. Group-by aggregation — revenue by customer

Grouping uses group_by for the keys and aggregations for the functions, each with an alias for its output column. On SQL sources (and NetSuite's SuiteQL surface) the grouping pushes down; on sources with no aggregation grammar (QuickBooks Online, Monday.com) Nexus computes the identical result in-engine. Aggregations are typically authored as saved reports.

{
  "bridge": "QBO.production",
  "collection": "Invoices",
  "group_by": ["Customer"],
  "aggregations": [
    { "type": "sum",   "field": "AmountDue", "alias": "TotalDue" },
    { "type": "count",                       "alias": "InvoiceCount" }
  ],
  "where":  { "Status": { "$eq": "Open" } },
  "having": { "TotalDue": { "$gt": 5000 } },
  "order_by": [{ "field": "TotalDue", "direction": "desc" }]
}

8. Aggregation with a period bucket

Group by a period field to get a monthly or quarterly roll-up.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Orders",
  "group_by": ["Period"],
  "aggregations": [
    { "type": "sum", "field": "Total", "alias": "Revenue" },
    { "type": "avg", "field": "Total", "alias": "AvgOrder" }
  ],
  "where": { "OrderDate": { "$gte": "2026-01-01" } }
}

9. Insert a record

Writes use an operation plus the payload it needs. An insert posts full records. (Writes are available on connectors that declare write support and require a write-scoped grant.)

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Customers",
  "operation": "insert",
  "records": [
    { "Name": "Acme Holdings", "Region": "West", "Status": "Active" }
  ]
}

10. Update matching rows

On SQL bridges an update applies set to the rows matched by where. On sparse-update systems (like QuickBooks Online) only the fields you supply change.

{
  "bridge": "MSSQL.warehouse",
  "collection": "dbo.Invoices",
  "operation": "update",
  "set":   { "Status": "Paid" },
  "where": { "InvoiceId": { "$eq": 4821 } }
}

On record-based REST bridges, address one object by id:

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

11. Upsert / merge on a key

upsert (alias merge) inserts-or-updates in one call. On SQL and workspace bridges pass key_columns (the conflict target); on NetSuite each record carries its own externalId.

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

12. Stage across systems in a pipeline

One read targets one bridge. To combine data from two systems, read each and stage the results through the internal workspace — a per-tenant Postgres scratch area that needs no credentials and supports full pushdown, insert/update/delete, and upsert.

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

Adapting these examples

  • Run schema discovery to get the real collection and field names for your source before querying.
  • Reuse one filter language everywhere — the same where works whether the bridge is SQL Server, NetSuite, or Monday.com.
  • Save a query you will run again as a report so you get parameters, pushdown planning, and version history.
  • Combine results from multiple bridges inside a pipeline, staging through the workspace rather than attempting a cross-source join.
  • Check each connector's tier before depending on heavy pushdown — see Pushdown & capability semantics.

See also

← UQL SyntaxFilter Operators Reference →