PartiQL: first queries

View .md

The front door of the dialect series: read items out of one table and choose what comes back. Two statements in, you'll also know how to read the "Executes as" strip — the habit that makes the whole dialect legible.

You'll learn
  • Run your first SELECT against a base table and a GSI
  • Project and alias columns, and de-duplicate results with DISTINCT
  • Build computed columns from expressions, scalar functions, and SELECT VALUE
  • Read the "Executes as" strip to see the exact native request before you run

Your first SELECT

sql 
SELECT * FROM "stage.Users"                          -- base tableSELECT * FROM "stage.Users"."ByOrg"                  -- a GSI
  • FROM "table" reads the base table; FROM "table"."index" reads a GSI. Quote names — especially namespaced ones containing dots ("stage.Users").
  • Keywords read case-blind everywhere; attribute names are case-sensitive on the wire (native re-emissions use your schema's canonical casing).

That's already a real query. Without a WHERE clause it reads the whole table — fine for a small dev stage, and exactly the thing Targeted reads teaches you to avoid on a big one.

Projection: choose your columns

sql 
SELECT Name AS n, Age FROM "stage.Users"             -- projection + aliasSELECT DISTINCT Country FROM "stage.Users"           -- de-duplicated
  • SELECT * or an attribute list. AS aliases are lowered — DynamoDB has no AS, so sources project natively and rename client-side per fetched item. Top-level attributes only; ORDER BY may reference the alias.
  • FROM aliases (FROM "stage.Users" AS x WHERE x.pk = …) are likewise lowered: the alias strips and x.-qualified paths re-spell bare before the wire. Explicit AS only; paths inside string literals are never rewritten.
  • DISTINCT de-duplicates each fetched page client-side with deep value equality — numbers by value, sets order-insensitive. Duplicates across page boundaries can reappear.
How aliases are lowered
DynamoDB has no AS. The studio projects the native attribute names and renames each fetched item client-side — so SELECT Name AS n ships as a projection of Name, with the rename applied after the wire. FROM aliases behave the same way: x.pk re-spells to a bare pk before the request leaves. Every client-side step is disclosed in the "Executes as" strip. *Because it all happens after the read, client-side shaping — aliases, computed columns, DISTINCT — changes what you see, never what DynamoDB reads or bills.* What controls the bill is the WHERE clause — covered in Targeted reads.

Computed columns: expressions & functions

sql 
-- expressions in the SELECT listSELECT Name, balance * 1.1 AS adjusted, address.city,       Name || ' <' || email || '>' AS contactFROM "stage.Users" WHERE pk = 'User#9'​-- SELECT VALUE: each row IS the valueSELECT VALUE {'who': Name, 'next': balance + 1} FROM "stage.Users"

A projected column can be an expression, not just an attribute: document paths (address.city, tags[0]), arithmetic, || concatenation, comparisons, and tuple/array constructors. The native read projects just the root attributes the expressions touch; each row is then shaped client-side, so a column whose value comes out MISSING is simply omitted from that row.

A library of scalar functions rides the same grammar — string (upper, substring), numeric (abs, mod), null-handling (coalesce), date/time (year, to_epoch / from_epoch, date_add), and regex (regex_like) — each evaluated per fetched row. SELECT VALUE projects each row as the evaluated value rather than a named-column tuple. The keyword reference lists every function with a one-line example; these are all lowered — computed client-side and disclosed in the strip.

Relabel with CASE
A CASE expression maps a stored value to a label without leaving the query — CASE Status WHEN 1 THEN 'Pending' WHEN 2 THEN 'Shipped' ELSE Status END AS status. Both forms work: simple (CASE attr WHEN value …) and searched (CASE WHEN condition …). It's a SELECT-list expression like any other, evaluated per fetched row; an unmatched row with no ELSE makes that column MISSING and omitted. One rule worth knowing early: CASE in a normal read WHERE is refused — WHERE decides the Query/Scan plan, so it can't be a client-side fold. Full entry in the keyword reference.

Read the "Executes as" strip

Every statement you type is analysed at the keystroke. The "Executes as" strip shows the exact native DynamoDB request the statement translates to, live, with one-click copy — and the editor colours intent before you run: key conditions tint green, a predicate that degrades to a full-table Scan flags amber with the table's live item count, and anything that won't run is squiggled with the reason.

Make checking the strip a reflex now, while the statements are one-liners. Once joins, subqueries, and aggregates enter the picture, single statements compile to multiple native requests — the strip is how you always know what's about to ship.

Try it · PartiQLSample
Run it live in DynoStudio →
Executes asQuery · stage.Users
Query KeyConditionExpression: pk = 'User#9' · Projection: Name, balance, address
One-partition key read — a Query, not a Scan.
Native: the statement compiles directly to this request.
#Namebalanceaddress.city
1Ada Okafor1,420.50Lagos
Fetched 1 item in 11ms · 100% efficient · 1 RCU · 1 partition

Flip between the two variants above: the same projection, but dropping the WHERE turns a 1-RCU Query into a 12,480-item Scan — the strip and the footer tell the story before you ever pay for it. Both samples are editable and copyable; live execution against your own tables happens in DynoStudio.

AWS background
Recap
  • FROM "table" reads a base table; FROM "table"."index" reads a GSI — always quote namespaced names.
  • Projection picks columns; AS and FROM aliases are lowered (renamed client-side); DISTINCT de-duplicates per page.
  • Projected columns can be expressions and scalar functions; SELECT VALUE projects each row as its evaluated value.
  • The "Executes as" strip is your ground truth — check it on every statement.
Check yourself2
Where does SELECT Name AS n do its renaming?
A SELECT with no WHERE clause compiles to…
Try it yourself 3
1 Alias a column
Return each user's Name (renamed to who) alongside their balance from stage.Users.
PartiQL has no AS natively — the studio lowers it. You still write it the SQL way.
Show solution
The AS alias is lowered: Name is projected natively and renamed client-side per item.
sql 
SELECT Name AS who, balance FROM "stage.Users"
2 De-duplicate a column
List the distinct Country values across the table.
Remember DISTINCT de-duplicates each fetched page client-side — duplicates can reappear across page boundaries.
Show solution
DISTINCT runs client-side with deep value equality.
sql 
SELECT DISTINCT Country FROM "stage.Users"
3 Build a computed column
Project a single contact column shaped like Ada <ada@x.com>, combining Name and email.
Show solution
Concatenate with ||. The native read projects just the root attributes the expression touches (Name, email); the string is assembled client-side.
sql 
SELECT Name || ' <' || email || '>' AS contactFROM "stage.Users" WHERE pk = 'User#9'
Was this page helpful?