# PartiQL: targeted reads

Part of the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/): the difference between a read that touches three items and one that reads — and bills — the whole table is a single predicate. This page is the WHERE clause: which predicates bound what DynamoDB reads, which merely filter it, and how to sort and page what comes back.

**You'll learn**

- Tell a **key condition** (bounds what DynamoDB reads) from a **filter** (applied after — still billed)
- Keep a read a cheap **Query**, and recognise the predicates that degrade it to a **Scan**
- Read a **GSI** as a target — including **multi-key** GSIs with several partition and sort attributes
- Sort and page a result set, and know which rows you still pay for

## The Query/Scan divide

```sql
SELECT * FROM "stage.Outbox" WHERE pk = 'Outbox#123'        -- Query
SELECT * FROM "stage.Outbox" WHERE pk IN ('O#1', 'O#2')      -- key lookups
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND begins_with(sk, 'EVT#')          -- narrowed Query
SELECT * FROM "stage.Outbox" WHERE Status = 'pending'        -- Scan (warned)
```

A **partition-key equality or IN list** in WHERE avoids the full read — DynamoDB's equality-or-IN rule. Equality executes as a Query; an IN list executes as one key lookup per value (an IN-driven read claims no sort-key narrowing — every other predicate filters after those lookups). Anything else degrades to a **Scan that reads — and bills — every item**. When the attribute you need to filter by *isn't* a key, the fix is a **GSI** whose partition key *is* that attribute: read it with the `FROM "table"."index"` form and the filter becomes a key condition again (multi-attribute GSIs are below).

The editor points at the exact predicate (or `OR`) responsible for a Scan, and with a live connection the warning is sized with the table's item count ("~432,926 items at last describe"). Tables outside your parsed schema still classify: their live key schema stands in, so `pk IN (…)` on an unmapped table reads as key lookups, not "can't classify".

**Try it · PartiQL**

_Filter only — Scan_

```sql
SELECT pk, sk, Status
FROM "stage.Outbox"
WHERE Status = 'pending'
```

_Executes as:_ Scan · stage.Outbox — No key condition — every item is read and billed, then filtered. The matched rows are a sliver of what you pay for. Switch to the keyed variant to see the same question asked for the price of one partition.

_Keyed — Query_

```sql
SELECT pk, sk, Status
FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND Status = 'pending'
```

_Executes as:_ Query · stage.Outbox — The pk equality forms the key condition — one partition read; Status filters within it. Same question, keyed: items read drop from ~432,926 to 12.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

Three rows back, the whole table read to find them — that 0.0007% is the Scan tax. Now flip to the **Keyed — Query** variant: the same question with a partition-key equality in front reads 12 items instead of 432,926, and the strip turns green before anything runs.

And the tax compounds as the table grows. Drag the slider — the Scan's bill tracks the table, the keyed Query's doesn't:

_Interactive on the web page: the Scan-tax slider — drag the table size and compare what a Scan vs a keyed Query bills. https://www.kanject.com/docs/dynostudio-partiql-reads/_

## WHERE: the predicate reference

All predicates are native. What varies is *where they execute* — in the key condition (bounding what DynamoDB reads) or in the filter (applied after the read; matched items are still read and billed):

- **Comparisons** — `=  <>  !=  <  <=  >  >=`. Partition-key equality forms the key condition; sort-key comparisons on a queried target narrow it; anything else filters.
- **`BETWEEN low AND high`** — on the queried sort key it narrows the key condition itself; elsewhere it filters.
- **`IN ('a', 'b')`** — literal list. On the partition key it stays a key condition (one key lookup per value); never on a sort key; elsewhere it filters.
- **`AND`** — composes key conditions and filters freely. **`OR` / `NOT`** — can never form a key condition; their presence around key predicates degrades the read to a Scan, and the editor says so.
- **`IS MISSING` / `IS NOT MISSING`** — attribute absence. Absent is *not* the same as `NULL`: DynamoDB stores explicit nulls, and the two test differently.
- **Functions** — `begins_with(path, prefix)`, `contains(path, value)`, `attribute_type(path, type)`, `size(path)`. On a queried sort key, `begins_with` narrows the key condition; the rest always filter.

## Multi-key GSIs

```sql
-- a multi-key GSI: two partition + two sort attributes
SELECT * FROM "stage.Orders"."byRegionStatus"
WHERE region = 'eu' AND tenant = 'acme'         -- equality on every partition attr
  AND status = 'open' AND createdAt >= '2026'   -- sort attrs, left-to-right
-- a gap, a missing partition equality, or a 2nd range → Scan
```

A GSI isn't limited to one partition key and one sort key. It can carry **up to four partition and four sort attributes**, kept separate and typed — not concatenated into one `region#tenant#status` string you have to parse back out. The studio classifies a read against one as a **Query** only when it honours the multi-key rule:

- **Equality on every partition attribute** — all of them, no exceptions; a missing one degrades to a Scan.
- **Sort attributes bound left-to-right, no gaps** — you may stop early, but you can't skip one and bind a later one.
- **At most one trailing inequality** — `<` `<=` `>` `>=` `BETWEEN`, or `begins_with` (which must come last). A *second* range degrades to a Scan.

A gap, a missing partition equality, or a second range turns the read into a Scan, and the strip names the attribute that broke the rule — so you see *why* it fell off the Query path, not just that it did.

**Try it · Multi-key GSI**

```sql
SELECT orderId, region, status, createdAt
FROM "stage.Orders"."byRegionStatus"
WHERE region = 'eu' AND tenant = 'acme'
  AND status = 'open' AND createdAt >= '2026'
```

_Executes as:_ Query · stage.Orders.byRegionStatus — Equality on both partition attributes, then both sort attributes left-to-right with one trailing range — a single targeted Query on the index. Native: a multi-key GSI binds up to 4 partition + 4 sort attributes, kept separate and typed.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

This is the green counterpart to the amber Scan above: same table, but every key attribute is pinned the way the rule wants, so the whole read is one targeted Query on the index. Drop the `tenant` equality or reorder the sort attributes in [DynoStudio](https://www.kanject.com/dynostudio/) and the strip flips amber, naming what broke.

## Sorting and paging

```sql
-- native: provably the queried sort key, server-side order
SELECT * FROM "stage.Users" WHERE pk = 'User#9' ORDER BY sk DESC
-- lowered: per-page client sort, disclosed
SELECT * FROM "stage.Users" ORDER BY Country ASC, Age DESC NULLS LAST
LIMIT 50 OFFSET 10
```

- **`ORDER BY` (native case)** — a single key that provably is the queried target's sort key stays in the native statement, so DynamoDB returns rows in index order (`ASC`/`DESC`).
- **`ORDER BY` (lowered case)** — multi-key lists, non-key attributes, document paths (`ORDER BY address.city`), or unprovable targets sort **each fetched page** client-side, disclosed in the strip. Type order: booleans before numbers before text. Absent values sort as largest unless `NULLS FIRST` / `NULLS LAST` says otherwise.
- **`LIMIT n`** — core PartiQL that DynamoDB's dialect rejects; the studio sends it as the request's page limit.
- **`OFFSET n`** — skipped rows are fetched on top of the first page and dropped client-side. **Skipped rows are still read and billed.** First page only.
- **`DISTINCT`** — de-duplicates each fetched page client-side by deep value equality (numbers by value, sets order-insensitive). Like a client-side `ORDER BY`, it acts **per page**, so duplicates across page boundaries can reappear.

**AWS background**

- [Querying tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html) — AWS docs: partition-key equality, optional sort-key narrowing, and pagination.
- [Scanning tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Scan.html) — AWS docs: Scan reads table or index data before filter expressions discard results.

**Recap**

- A **partition-key equality or `IN`** is the only thing that turns a read into a cheap Query; everything else Scans.
- On a queried sort key, `begins_with` / `BETWEEN` / comparisons **narrow** the read; every other predicate **filters after** it (matched rows still billed).
- A **GSI** — including a multi-key one (up to 4 partition + 4 sort attributes) — reads with `FROM "table"."index"`, and is Query-or-Scan by the same rule.
- `OFFSET`-skipped rows are still read and billed; client-side `ORDER BY` and `DISTINCT` act **per fetched page**.

**Check yourself**

**1. Which `WHERE` clause keeps a read of `stage.Outbox` (keys `pk`, `sk`) a cheap **Query**?**

- `WHERE pk = 'Outbox#123' AND Status = 'pending'` ✓ — the `pk` equality forms the key condition, and `Status` filters *within* that one partition.
- `WHERE Status = 'pending'` — no key condition — this Scans, reading (and billing) every item before the filter runs.
- `WHERE pk = 'Outbox#123' OR pk = 'Outbox#124'` — `OR` can never form a key condition. Write it as `pk IN ('Outbox#123', 'Outbox#124')` to get key lookups.

**2. A read runs `LIMIT 50 OFFSET 100`. How many rows do you pay for?**

- 150 — the `OFFSET` rows are fetched and dropped ✓ — skipped rows are read on top of the first page and discarded client-side. **Skipped rows are still read and billed.**
- 50 — only the returned page — the 100 `OFFSET` rows don't vanish: they're fetched first, then dropped client-side — and billed.
- 100 — the offset is free, the limit bills — it's the reverse: everything read bills, and `OFFSET` forces 100 extra reads before your 50.

**3. A multi-key GSI has partition attributes (`region`, `tenant`) and sort attributes (`status`, `createdAt`). Which read is a **Query**?**

- `region = 'eu' AND tenant = 'acme' AND status = 'open' AND createdAt >= '2026'` ✓ — equality on **every** partition attribute, sort attributes bound left-to-right, one trailing range — the multi-key rule, honoured.
- `region = 'eu' AND status = 'open'` — the `tenant` partition attribute is missing — equality on *every* partition attribute is required, so this Scans.
- `region = 'eu' AND tenant = 'acme' AND createdAt >= '2026'` — the sort attributes bind left-to-right with no gaps — skipping `status` to bind `createdAt` degrades the read to a Scan.

**Try it yourself**

**1. Turn a Scan into a Query**

This reads the whole table: `SELECT * FROM "stage.Outbox" WHERE Status = 'pending'`. Narrow it to a single partition.

_Hint:_ A Scan becomes a Query the moment a partition-key equality joins the WHERE. `Status` can stay — as a filter.

_Solution:_ Add a `pk` equality: `pk = …` forms the key condition (a Query); `Status = …` then filters *within* that partition.

```sql
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND Status = 'pending'
```

**2. Narrow with a sort-key range**

Within one partition, return only the events whose sort key falls in a range.

_Hint:_ On a queried sort key, `BETWEEN` narrows the key condition itself — it doesn't just filter.

_Solution:_ `BETWEEN` on the sort key narrows the read server-side, so DynamoDB only touches the matching slice — not the whole partition.

```sql
SELECT * FROM "stage.Outbox"
WHERE pk = 'Outbox#123' AND sk BETWEEN 'EVT#0100' AND 'EVT#0200'
```

**3. Query a multi-key GSI**

Read `"stage.Orders"."byRegionStatus"` for open orders in region `eu`, tenant `acme`, created since 2026.

_Hint:_ Equality on every partition attribute (`region`, `tenant`), then the sort attributes left-to-right (`status`, `createdAt`).

_Solution:_ All partition attributes use equality and the sort attributes bind left-to-right with one trailing range — so it stays a Query on the index.

```sql
SELECT * FROM "stage.Orders"."byRegionStatus"
WHERE region = 'eu' AND tenant = 'acme'
  AND status = 'open' AND createdAt >= '2026'
```

> **Where next?:** One table, read precisely. [Joins, subqueries & sets](https://www.kanject.com/docs/dynostudio-partiql-joins/) reads *across* tables — and across the entities of a single-table design — with requests DynamoDB can't compose on its own.

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-reads/ · Kanject Docs_