# PartiQL: keyword reference

The flat index for the [dialect series](https://dynostudio.dev/docs/dynostudio-partiql/): every keyword, operator, and function the editor understands, one sample line each, tagged with where it executes. The guide pages teach these in context — this page is the cheat-sheet you keep open while you write.

Tags follow the [series vocabulary](https://dynostudio.dev/docs/dynostudio-partiql/): **native** (the selected data plane runs it server-side), **lowered** (the studio translates or composes it; client-side work is disclosed), **Kanject** (a convention extension beyond the PartiQL spec). Where a keyword behaves differently by position — a predicate that narrows a key versus one that only filters — the entry says so.

Each core statement also has a **dedicated page** with syntax, use-cases, and quotable, deep-linkable examples:

- **[SELECT](https://dynostudio.dev/docs/dynostudio-select/)** — Point reads, GSI lookups, vector similarity, explicit data planes, computed columns, counts — and the cost rule.
- **[INSERT & upserts](https://dynostudio.dev/docs/dynostudio-insert/)** — Object-literal writes, ON CONFLICT skip / replace / upsert, TTL stamps.
- **[UPDATE](https://dynostudio.dev/docs/dynostudio-update/)** — Single-item writes, RETURNING, counters, preview-first sweeps.
- **[DELETE](https://dynostudio.dev/docs/dynostudio-delete/)** — Keyed deletes, keep-a-copy RETURNING, stale-row sweeps — and when TTL beats a sweep.
- **[PROFILE TABLE](https://dynostudio.dev/docs/dynostudio-profile-table/)** — Discover an unfamiliar table's shape before you query it.
- **[Transactions](https://dynostudio.dev/docs/dynostudio-transactions/)** — All-or-nothing write blocks, condition guards, snapshot reads.
- **[CREATE & DROP TABLE](https://dynostudio.dev/docs/dynostudio-create-table/)** — Key types, billing modes, and the type-the-name drop confirmation.
- **[CREATE & DROP GSI](https://dynostudio.dev/docs/dynostudio-create-gsi/)** — Single- and multi-key GSIs, managed backfill, and guarded removal without deleting base-table items.
- **[CREATE & DROP VECTOR INDEX](https://dynostudio.dev/docs/dynostudio-vector-index/)** — Dimensions, distance metric, search schema, projection, asynchronous backfill, and safe removal.
- **[BACKUP & RESTORE TABLE](https://dynostudio.dev/docs/dynostudio-backup-restore/)** — Create a managed snapshot, capture its ARN, and restore it into a new table.
- **[ANALYTICS REPLICA](https://dynostudio.dev/docs/dynostudio-analytics-replica/)** — Provision, retune and tear down a DynamoDB → S3 Tables replica.
- **[OpenSearch indexes & queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/)** — Create a searchable projection, keep it current, query text, filters, highlights and facets, then rebuild or remove it safely.
- **[Hybrid & cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/)** — Name a data plane explicitly, combine live and historical rows, recompute aggregates, or run one bounded cross-plane join.
- **[Kanject markers](https://dynostudio.dev/docs/dynostudio-marker-indexes/)** — Model-declared access paths, transactional maintenance, generated finders, and migration boundaries.

## Statements

```sql
SELECT  * FROM "stage.Users" WHERE pk = 'User#9';
INSERT  INTO "stage.Users" VALUE {'pk': 'User#9', 'sk': 'PROFILE'};
INSERT  … VALUE {…} ON CONFLICT DO NOTHING | DO REPLACE | DO UPDATE SET …;
UPDATE  "stage.Users" SET Role = 'admin' WHERE pk = 'User#9' AND sk = 'PROFILE';
DELETE  FROM "stage.Users" WHERE pk = 'User#9' AND sk = 'PROFILE';
PROFILE TABLE "stage.Users";
BEGIN TRANSACTION; … ; COMMIT;
```

- **`SELECT`** — *native / lowered / composed.* Reads DynamoDB by default; provider-qualified `dynamodb`, `s3tables`, `opensearch`, and `hybrid` sources select an explicit plane. Against a live vector index, `ORDER BY VECTOR_DISTANCE(…) LIMIT n` lowers to one native `SearchVectors` request. OpenSearch and hybrid shapes have their own bounded contracts below. Read paths land in [First queries](https://dynostudio.dev/docs/dynostudio-partiql-basics/) and [SELECT](https://dynostudio.dev/docs/dynostudio-select/).
- **`SELECT VALUE expr`** — *lowered.* Each row *is* the evaluated value; a tuple spreads into columns, anything else lands in a `value` column. [First queries](https://dynostudio.dev/docs/dynostudio-partiql-basics/).
- **`INSERT INTO … VALUE {…}`** — *native.* Writes one item from a PartiQL object literal; inserting over an existing key is refused, never an overwrite. See [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).
- **`INSERT … ON CONFLICT DO NOTHING / DO REPLACE / DO UPDATE SET …`** — *lowered.* Resolves a primary-key collision in one native write: skip (`attribute_not_exists` `PutItem`), overwrite (`PutItem`), or upsert (`UpdateItem`). See [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).
- **`UPDATE … SET … WHERE …`** — *native.* Single-item write when the `WHERE` names the complete primary key; a looser `WHERE` runs as a **preview-first set-based write**. [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).
- **`DELETE FROM … WHERE …`** — *native.* Single-item delete under the complete-key rule, or a preview-first set-based delete on a looser `WHERE`. [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).
- **`PROFILE TABLE`** — *lowered.* Samples a table and reports per-attribute coverage and type distribution. [Aggregates & profiling](https://dynostudio.dev/docs/dynostudio-partiql-aggregates/).
- **`BEGIN TRANSACTION` / `COMMIT`** — *native.* Wraps writes into one all-or-nothing transaction, or exact-key SELECTs into one consistent-snapshot read transaction. [Transactions](https://dynostudio.dev/docs/dynostudio-transactions/).

## Scripts & workflow control

```sql
SELECT …;

THEN

BEGIN TRANSACTION;
UPDATE …;
UPDATE …;
COMMIT;

THEN

SELECT …;
```

- **`;` statement separator** — *Kanject script control.* Runs statements in order as independent requests; a failed statement marks its result tab and the ordinary script continues.
- **Standalone `THEN`** — *Kanject workflow control.* Must occupy its own line between semicolon-closed units. Runs standalone `SELECT`s and complete write-transaction blocks in any order, stopping at the first failure. Each COMMIT is still its own atomic boundary; a later failure reports partial completion rather than implying rollback. [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/#success-gated-workflows-with-then).
- **`THEN` in `CASE`** — *lowered expression.* Keeps its ordinary PartiQL meaning (`CASE WHEN condition THEN value END`) and is never parsed as a workflow gate.

A workflow containing writes validates and arms on the first Run, then executes on an unchanged **Execute workflow** press. A read-only workflow runs immediately. Plain writes outside a transaction, read-only transaction blocks, variables, branching, and result piping are not workflow units.

## Schema & DDL

```sql
CREATE TABLE "stage.Ledger" (PARTITION KEY pk STRING, SORT KEY sk STRING) WITH BILLING = ON_DEMAND;
DROP   TABLE "stage.Ledger";                         -- type-the-name confirmation
CREATE GLOBAL INDEX "byRegionStatus" ON "stage.Orders" -- up to 4 partition + 4 sort keys
  PARTITION KEY (region, tenant) SORT KEY (status, createdAt) PROJECT ALL;
DROP GSI "byRegionStatus" ON "stage.Orders";         -- keeps every base-table item
CREATE VECTOR INDEX "ProductEmbeddingIndex" ON "Products" (Embedding)
  DIMENSIONS 1536 DISTANCE COSINE PARTITION BY (Category) FILTER BY (Brand) PROJECT ALL;
DROP VECTOR INDEX "ProductEmbeddingIndex" ON "Products"; -- keeps every base-table item
BACKUP TABLE "stage.Orders" AS 'orders-2026-06-19';
RESTORE TABLE "stage.OrdersRestored" FROM BACKUP 'arn:aws:dynamodb:…:backup/…';
CREATE ANALYTICS REPLICA FOR TABLE "stage.Orders";   -- S3 Tables zero-ETL integration
CREATE OPENSEARCH INDEX "open-orders" ON "stage.Orders"
  PROJECT (orderId, notes, status) MAPPING (notes SEARCH, status FILTER)
  ON INSERT, MODIFY, REMOVE;
DESCRIBE OPENSEARCH INDEX "open-orders";
ALTER OPENSEARCH INDEX "open-orders" SET PROJECT (orderId, notes, status, region);
REINDEX OPENSEARCH INDEX "open-orders";
DROP OPENSEARCH INDEX "open-orders" INCLUDING DOCUMENTS;
```

DynamoDB's own PartiQL has **no DDL** — these are a DynoStudio dialect surface that lowers to the control-plane calls the AWS services actually use, disclosed in the "Executes as" strip before it runs. A read-only stage refuses all mutating control-plane work.

- **`CREATE TABLE … WITH BILLING = ON_DEMAND | PROVISIONED (RCU n, WCU n)`** — *lowered.* Becomes a `CreateTable`; each key attribute's type is **required** — `STRING`, `NUMBER`, or `BINARY` (DynamoDB fixes key types at creation, so the studio won't guess one).
- **`DROP TABLE` / `DELETE TABLE`** — *lowered.* Becomes a `DeleteTable`, and is **irreversible** — like the AWS Console, the run stays disarmed until you type the exact table name.
- **`CREATE GLOBAL INDEX … PARTITION KEY (…) [SORT KEY (…)] PROJECT ALL | KEYS | INCLUDE (…)`** — *lowered.* Becomes an `UpdateTable`; supports DynamoDB **multi-key** GSIs (up to 4 partition + 4 sort attributes) and the service auto-backfills the index from existing attributes. `CREATE GSI`, `CREATE GLOBAL SECONDARY INDEX`, and bare `CREATE INDEX` remain compatibility aliases.
- **`DROP GSI "index" ON "table"`** — *lowered.* Becomes an `UpdateTable` GSI delete. The first Run arms it and the second unchanged Run confirms it; base-table items stay intact, but index queries fail immediately while deletion finishes in the background. `DROP GLOBAL SECONDARY INDEX` and `DROP INDEX` are accepted aliases.
- **`CREATE VECTOR INDEX … DIMENSIONS n DISTANCE metric [PARTITION BY (a)] [FILTER BY (…)] PROJECT …`** — *lowered.* Becomes `UpdateTable.VectorIndexUpdates.Create`. Professional; requires a fresh descriptor, an `ON_DEMAND` table, `1`–`4096` dimensions, and room under the five-vector-index limit. The receipt tracks backfill until ready.
- **`DROP VECTOR INDEX "index" ON "table"`** — *lowered.* Becomes the matching `UpdateTable` delete, uses the same double-run confirmation, and preserves every base-table item. [Vector-index reference](https://dynostudio.dev/docs/dynostudio-vector-index/).
- **`BACKUP TABLE "T" AS 'name'`** — *lowered.* Becomes `CreateBackup`, a DynamoDB-managed snapshot; the run reports the backup ARN.
- **`RESTORE TABLE "new" FROM BACKUP 'arn'`** — *lowered.* Becomes `RestoreTableFromBackup` and creates a new table, tracked until ACTIVE. [Backup/restore reference](https://dynostudio.dev/docs/dynostudio-backup-restore/).
- **`CREATE / ALTER / DROP ANALYTICS REPLICA FOR TABLE "T"`** — *lowered.* Provisions, reconfigures or removes a DynamoDB to Amazon S3 Tables analytics replica. The create path expands to the ordered DynamoDB, S3 Tables, Lake Formation, Glue and IAM setup plan, with native calls, likely permissions and recurring costs previewed before consent. See [Tables, indexes & replicas](https://dynostudio.dev/docs/dynostudio-partiql-ddl/#s3-tables-analytics-replicas).
- **`CREATE OPENSEARCH INDEX … PROJECT … [MAPPING …] [ON …] [WHERE …]`** — *lowered.* Creates an eventually consistent, DynamoDB-derived search projection and the export/stream ingestion path that maintains it. Professional; requires the Search binding, PITR, a compatible Streams view and an ACTIVE OSIS pipeline. [OpenSearch index reference](https://dynostudio.dev/docs/dynostudio-opensearch-index/).
- **`DESCRIBE` / `SHOW OPENSEARCH INDEXES`** — *lowered read-only control plane.* Returns the projection definition and health as ordinary result rows.
- **`ALTER OPENSEARCH INDEX` / `REINDEX OPENSEARCH INDEX`** — *lowered.* `ALTER` accepts one future-facing project, mapping or event action; widening a projection or mapping requires a full, guarded `REINDEX` to cover existing rows.
- **`DROP OPENSEARCH INDEX` [`INCLUDING DOCUMENTS`]** — *lowered.* Plain drop removes the feed but leaves indexed documents; `INCLUDING DOCUMENTS` also deletes them after a type-the-name confirmation. [OpenSearch lifecycle](https://dynostudio.dev/docs/dynostudio-opensearch-index/).

Kanject marker indexes are deliberately absent from this DDL list. They are model-declared reserved rows whose keys can depend on annotation names, templates, several entity properties, and the generator contract — not AWS control-plane resources that a column-only SQL statement can define. See [Kanject marker indexes](https://dynostudio.dev/docs/dynostudio-marker-indexes/) for generated writes, finders, inspection, and the separate migration contract.

## Clauses

```sql
FROM "stage.Users"."ByOrg"          -- base table or "table"."index"
WHERE OrgId = 'ORG#kanject'            -- filter / key condition
GROUP BY Status                     -- fold matches into groups
ORDER BY Age DESC NULLS LAST        -- sort (ASC | DESC, NULLS FIRST | LAST)
LIMIT 50 OFFSET 10                  -- page window
PARALLEL 4                          -- concurrent Scan segments (faster, same RCUs)
RETURNING ALL OLD *                 -- echo the written item
JOIN "stage.Users" AS u ON u.pk = 'User#{CustomerId}'
```

- **`FROM "table"` / `FROM "table"."index"`** — *native.* Reads the base table or a GSI — including **multi-key** GSIs (up to 4 partition + 4 sort attributes), where a Query needs equality on every partition attribute, then the sort attributes bound left-to-right with no gaps and at most one trailing inequality (`begins_with` last); a gap or a second range degrades to a Scan. Quote namespaced names containing dots. [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/).
- **`FROM dynamodb."T"` / `s3tables."T"` / `opensearch."index"` / `hybrid."T"`** — *native or composed.* Selects a data plane explicitly. `FROM OPENSEARCH "index"` is the equivalent OpenSearch shorthand. An unqualified source retains ordinary DynamoDB behavior. [Hybrid & cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/).
- **`WHERE`** — *native.* Predicates either form the key condition (bounding what DynamoDB reads) or filter after the read. The split is the whole of [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/).
- **`GROUP BY`** — *lowered.* One result row per distinct key combination, folded client-side. [Aggregates & profiling](https://dynostudio.dev/docs/dynostudio-partiql-aggregates/).
- **`ORDER BY`** *(`ASC` / `DESC`, `NULLS FIRST` / `NULLS LAST`)* — *native or lowered.* The queried sort key stays server-side; anything else sorts each page client-side. [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/).
- **`LIMIT`** — *native.* Sent as the request page limit; with `GROUP BY` it bounds the groups returned.
- **`OFFSET`** — *lowered.* Skipped rows are fetched, dropped client-side, and still billed. First page only.
- **`PARALLEL n`** — *lowered.* Runs an eligible Scan as `n` concurrent native segments and merges them client-side. Faster wall-clock, same items and total RCUs, up to `n×` concurrent capacity; ignored or serialized with a disclosed reason when the shape is not eligible. Professional, configured in Settings → Querying. [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/#parallel-n-faster-scans-same-bill).
- **`RETURNING ALL OLD *` / `ALL NEW *`** — *native.* Echoes the item before or after a single-statement write (not inside transactions).
- **`JOIN` / `LEFT` / `RIGHT` / `FULL [OUTER] JOIN … ON …`** *(with `AS` aliases)* — *lowered.* INNER/LEFT resolve per left row through the right side's key or a GSI; RIGHT/FULL drain the whole right side (a capped Scan) and join in memory, keeping unmatched rows from the outer side(s). [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **`CROSS JOIN`** *(no `ON`)* — *lowered.* The Cartesian product of both fully-drained sides, capped at 10,000 output rows. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **Two or more `JOIN`s** *(a chain)* — *lowered.* The base table drains, then each table resolves per accumulated row through its key, rows threaded alias-qualified. `INNER` and `LEFT` joins chain; `ON` is attribute-equality or a Kanject key template, binding each step's base key or a GSI key. A `RIGHT` / `FULL` / `CROSS` join inside a chain refuses. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **`SET`** / **`REMOVE`** — *native.* `UPDATE` assignment forms — repeated `SET` to assign, `REMOVE` to drop an attribute.
- **`AS`** — *lowered.* Renames a projected column or aliases a `FROM`/`JOIN` source; the rename happens client-side per item.

## Operators

```sql
WHERE Age >= 18 AND Country <> 'US'          -- comparisons + AND
WHERE Score BETWEEN 10 AND 20                -- range
WHERE pk IN ('User#1', 'User#2')             -- membership
WHERE pk IN (SELECT 'User#{CustomerId}' FROM "stage.Orders")       -- semi-join (subquery)
WHERE sk NOT IN (SELECT sk FROM "stage.Outbox"
                 WHERE pk = 'Outbox#processed')                    -- anti-join (subquery)
WHERE Status = 'open' OR NOT Archived        -- OR / NOT (forces Scan)
WHERE Nickname IS MISSING                    -- attribute absence
SELECT DISTINCT Country FROM "stage.Users";  -- de-duplicate
SELECT 'Outbox#' || Id FROM "stage.Outbox";  -- string concat (||, not +)
```

- **`=  <>  !=  <  <=  >  >=`** — *native.* Partition-key equality forms the key condition; sort-key comparisons on a queried target narrow it; anything else filters.
- **`BETWEEN low AND high`** — *native.* Narrows the key condition on a queried sort key; elsewhere it filters.
- **`IN (…)`** — *native.* On the partition key, one key lookup per value; never on a sort key; elsewhere it filters.
- **`IN (SELECT …)`** — *lowered.* A semi-join: the subquery runs first, then the outer read (inlined as key lookups when small, else a page filter). [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **`NOT IN (SELECT …)`** — *lowered.* The anti-join mirror — keeps rows absent from the subquery set; no key-lookup form, so the outer read Scans unless another predicate keys it. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **`AND`** — *native.* Composes key conditions and filters freely.
- **`OR` / `NOT`** — *native (filter only).* Can never form a key condition; around key predicates they degrade the read to a Scan, and the editor says so.
- **`IS MISSING` / `IS NOT MISSING`** — *native.* Tests attribute absence — *not* the same as an explicit `NULL`.
- **`DISTINCT`** — *lowered.* De-duplicates each fetched page by deep value equality; duplicates across page boundaries can reappear.
- **`||`** — *lowered.* String concatenation in a projection, computed client-side per item (like `AS`). Works the same in a top-level `SELECT` and in a subquery's inner projection — `||`, never `+`. Semi-join use in [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).

## Set operators

```sql
SELECT * FROM "stage.A" UNION     SELECT * FROM "stage.B";
SELECT * FROM "stage.A" UNION ALL SELECT * FROM "stage.B";
SELECT * FROM "stage.A" INTERSECT SELECT * FROM "stage.B";
SELECT * FROM "stage.A" EXCEPT    SELECT * FROM "stage.B";
```

- **`UNION`** — *lowered.* One read per operand, combined client-side by deep equality and de-duplicated. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **`UNION ALL`** — *lowered.* As `UNION`, but keeps duplicates (plain concatenation). Across provider-qualified planes, Professional runs one bounded read per leg and merges compatible columns and types; plain cross-plane `UNION`, `INTERSECT`, and `EXCEPT` refuse. [Hybrid & cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/).
- **`INTERSECT`** — *lowered.* Keeps rows present in every operand.
- **`EXCEPT`** — *lowered.* Keeps the first operand's rows that appear in none of the rest.

A trailing `ORDER BY [LIMIT]` after the last operand sorts and cuts the **combined** set. Operators mix only through parentheses — `(A UNION B) EXCEPT C` runs; un-parenthesized mixing refuses, by design. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).

## Condition functions

```sql
WHERE begins_with(sk, 'EVT#')     -- prefix match (narrows a sort key)
WHERE contains(Tags, 'urgent')    -- substring / set membership
WHERE attribute_type(Meta, 'M')   -- DynamoDB type code
WHERE size(Items) > 3             -- length of string / set / list / map
```

- **`begins_with(path, prefix)`** — *native.* On a queried sort key it narrows the key condition; elsewhere it filters.
- **`contains(path, value)`** — *native (filter).* Substring match on strings, membership in a set or list.
- **`attribute_type(path, type)`** — *native (filter).* Matches DynamoDB type codes (`S`, `N`, `M`, `L`, …).
- **`size(path)`** — *native (filter).* Length of a string, set, list, or map.

## Vector similarity

```sql
SELECT ProductId,
       VECTOR_DISTANCE(Embedding, {queryVector}) AS score
FROM "Products"."ProductEmbeddingIndex"
WHERE Category = {category} AND Brand = 'Acme'
ORDER BY VECTOR_DISTANCE(Embedding, {queryVector}) ASC
LIMIT 10
```

- **`VECTOR_DISTANCE(vector_attr, vector)`** — *lowered to native `SearchVectors`.* Used in `ORDER BY` and optionally selected with an explicit `AS` alias. The returned service score is not recomputed client-side.
- **Ordering** — `COSINE` / `EUCLIDEAN` are nearest-first with `ASC`; `DOT_PRODUCT` uses `DESC`. Omitting the direction uses the descriptor's nearest-first order.
- **Boundaries** — `LIMIT 1..100` is required; the vector must match the descriptor dimensions; filters are equality-only over declared `HASH` / `INLINE_FILTER` attributes; selected fields must be projected; the index must be ACTIVE and no longer backfilling.
- **Meter** — eventually consistent and byte-metered rather than RCU-metered. The receipt reports `VectorSearchRequestBytes`. [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/#vector-similarity-search).

## OpenSearch search clauses

```sql
SELECT orderId, status, total
FROM OPENSEARCH "open-orders"
WHERE notes MATCH 'urgent refund' AND status = 'OPEN'
ORDER BY status ASC LIMIT 50
HIGHLIGHT (notes)
FACET status
```

- **`MATCH`** — *native OpenSearch.* Full-text match against a field declared `SEARCH` in the projection mapping.
- **Exact and range filters** — *native OpenSearch.* `=`, comparisons, `BETWEEN`, and `IN` become search filters. Exact string filtering and sorting require a `FILTER` mapping; numeric and Boolean filters can use their inferred mapping.
- **`HIGHLIGHT (fields)`** — *native OpenSearch.* Returns matching fragments for projected searchable fields.
- **`FACET fields`** — *native OpenSearch.* Returns bucket counts for fields mapped `FILTER`.
- **Boundaries** — `LIMIT` and first-page `OFFSET` are supported, with `OFFSET` capped at 10,000. Direct OpenSearch reads refuse joins, groups, HAVING, set operations and CTEs; results are eventually consistent with DynamoDB. Professional. [OpenSearch queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/).

## Query planes and hybrid composition

```sql
SELECT * FROM dynamodb."Sales" WHERE pk = 'STORE#SEA';
SELECT region, SUM(total_amount) FROM s3tables."Sales" GROUP BY region;
SELECT orderId FROM opensearch."open-orders" WHERE notes MATCH 'refund' LIMIT 25;
SELECT region, COUNT(*) FROM hybrid."Sales" GROUP BY region;
```

- **Explicit planes** — `dynamodb`, `s3tables`, and `opensearch` route one read to that provider; an unqualified table still means DynamoDB.
- **Cross-plane `UNION ALL`** — runs one single-plane leg at a time, reconciles compatible columns and types, and keeps duplicates. Combined `ORDER BY` or `LIMIT` refuses.
- **`hybrid."T"`** — uses the analytics-replica watermark: older rows come from S3 Tables, current rows from DynamoDB, raw rows merge, and `COUNT`, `SUM`, `MIN`, `MAX`, and `AVG` are recomputed over the combined input.
- **One cross-plane join** — Professional supports one bounded `INNER` or `LEFT` equality join between different provider-qualified planes. Projections are alias-qualified, and each `WHERE` conjunct must belong to one side.
- **Boundaries** — read-only, Professional, and at most 5,000 rows per hybrid or join leg. Same-plane, multi-join, non-equality, compound, `RIGHT` / `FULL` / `CROSS`, CTE, and unsupported aggregate shapes refuse before execution. [Hybrid & cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/).

## Scalar functions (computed columns)

```sql
SELECT upper(Name) AS shout,
       coalesce(nickname, Name) AS display,
       year(CreatedAt) AS cohort,
       from_epoch(ExpiresAt) AS expires_iso,
       CASE Status WHEN 1 THEN 'Pending' ELSE 'Other' END AS status
FROM "stage.Users" WHERE pk = 'User#9'
```

These evaluate **per fetched row, after the read** — in `SELECT` lists, `SELECT VALUE`, and `HAVING` (distinct from the *native* WHERE functions above, which DynamoDB runs server-side). A MISSING argument yields MISSING, a NULL yields NULL, a wrongly typed argument yields MISSING — except `coalesce` / `ifnull`, which skip both; an unknown function or wrong argument count refuses *before* the read.

- **String** — *lowered.* `upper` · `lower` · `trim` / `ltrim` / `rtrim` · `length` · `substring(s, start[, len])` (1-based).
- **Numeric** — *lowered.* `abs` · `ceil` · `floor` · `mod(a, b)`.
- **Null-handling** — *lowered.* `coalesce(…)` · `ifnull(a, b)` — return the first present value.
- **Date / time** — *lowered.* `year` · `month` · `day` · `hour` · `minute` · `second` · `to_epoch` / `from_epoch` · `date_add(unit, n, iso)` · `date_diff(unit, a, b)`. ISO-8601 in UTC at second precision, so they round-trip with the clock functions.
- **Regex** — *lowered.* `regex_like(s, pattern)` · `regex_replace(s, pattern, repl)` · `regex_extract(s, pattern[, group])`. .NET syntax, timeout-bounded; a malformed literal pattern refuses before the read.
- **`CASE … END`** — *lowered.* Maps a stored value to a label as a SELECT-list expression — **simple** (`CASE attr WHEN v THEN … ELSE … END`) and **searched** (`CASE WHEN cond THEN … END`). An unmatched row with no `ELSE` is MISSING-and-omitted; `CASE` in a *normal read* `WHERE` is refused. [First queries](https://dynostudio.dev/docs/dynostudio-partiql-basics/).

## Aggregate functions

```sql
SELECT COUNT(*) AS n, AVG(balance) AS avg,
       SUM(balance) AS total, MIN(Age) AS lo, MAX(Age) AS hi
FROM "stage.Users"
WHERE Country = 'Nigeria'
```

- **`COUNT(*)`** — *lowered.* Counts matched items regardless of attribute presence.
- **`COUNT(attr)`** — *lowered.* Counts items where `attr` is present and non-null.
- **`SUM` / `AVG`** — *lowered.* Numeric fold; non-numeric values are skipped with a disclosure, and an empty fold is `NULL`, not `0`.
- **`MIN` / `MAX`** — *lowered.* Type-ordered comparison — booleans before numbers before text.
- **`COUNT` / `SUM` / `AVG(DISTINCT attr)`** — *lowered.* Fold over distinct values (deep equality); `DISTINCT` is refused on `MIN` / `MAX`.
- **`GROUP BY` / `HAVING`** — *lowered.* `GROUP BY` folds one row per distinct key (attribute or document path); `HAVING` filters the folded rows. [Aggregates & profiling](https://dynostudio.dev/docs/dynostudio-partiql-aggregates/).

All aggregates fold the matched read client-side into one row (or one row per group with `GROUP BY`), reading and billing every matched item. The target may be a **document path** (`SUM(order.total)`). Bound the input with `WHERE` and `LIMIT`. Full treatment in [Aggregates & profiling](https://dynostudio.dev/docs/dynostudio-partiql-aggregates/).

## Clock functions

```sql
SET CreatedAt = CURRENT_TIMESTAMP        -- ISO-8601 instant, UTC
WHERE Day     = CURRENT_DATE             -- ISO-8601 date, UTC
SET StampedAt = utcnow()                 -- alias of CURRENT_TIMESTAMP
SET ExpiresAt = unix_now()               -- epoch SECONDS (a bare number, for TTL)
SET StampedMs = unix_now_ms()            -- epoch MILLISECONDS
WHERE Day > CURRENT_DATE - 7             -- arithmetic folds before the wire
```

- **`CURRENT_TIMESTAMP`** / **`utcnow()`** — *lowered.* Substituted as an ISO-8601 instant **in UTC** at run time; work in reads and writes.
- **`CURRENT_DATE`** — *lowered.* An ISO-8601 date in UTC.
- **`unix_now()`** / **`CURRENT_EPOCH`** — *lowered.* Epoch **seconds** as a bare number — the form TTL attributes store (an ISO string never matches them).
- **`unix_now_ms()`** / **`CURRENT_EPOCH_MS`** — *lowered.* Epoch **milliseconds**, for JS-style timestamps.
- **Clock arithmetic** — *lowered.* An integer offset folds into the literal: `CURRENT_DATE - 7` (whole days), `± INTERVAL 'n' DAY|HOUR|MINUTE|SECOND`, or `unix_now() - 3600` (in the rendered base unit). A sub-day unit promotes `CURRENT_DATE` to a timestamp.

UTC is deliberate: DynamoDB compares ISO strings lexicographically, so a session-local literal silently misses rows. Occurrences inside string literals are untouched. Detail in [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).

## Kanject extensions

```sql
-- key template: re-spells a bare id into the stored key shape
WHERE pk IN (SELECT 'Outbox#{Id}' FROM "stage.Outbox" LIMIT 25)
ON u.pk = 'User#{CustomerId}'

-- named parameter: a reusable question (lowers to a positional ?)
WHERE status = {Status} AND created_at >= {Since:DateTimeOffset}
WHERE pk = ?                              -- the lowered positional form
```

- **Key templates** — `'Prefix#{Attr}'` — *Kanject.* Re-spell a bare attribute into the stored key shape inside a subquery projection or a join `ON` condition. Multi-placeholder and transform-aware; the reason bare-id subqueries match nothing. [Joins, subqueries & sets](https://dynostudio.dev/docs/dynostudio-partiql-joins/).
- **Named parameters** — `{Name}` / `{Name:format}` / `{Name|transform}` — *Kanject.* Lift a literal into a reusable, injection-safe placeholder; each lowers to a positional `?` with the value bound alongside, never entering the statement text. Authoring is Professional; running a parameterized saved function is free at every edition. [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).
- **`?` positional parameters** — *Kanject.* The lowered form named parameters compile to. Work in single read statements and saved functions; not yet combined with subqueries, aggregates, scripts, or transactions.

> **Lookup, not lesson:** This page is a lookup, not a lesson. If a keyword's behaviour surprises you, the page link beside it walks through *why*. New here? Start at [First queries](https://dynostudio.dev/docs/dynostudio-partiql-basics/), or pick your entry point from [the series overview](https://dynostudio.dev/docs/dynostudio-partiql/).

---
_Source: https://dynostudio.dev/docs/dynostudio-partiql-keywords/ · DynoStudio Docs_