How do I query DynamoDB by an attribute that isn't the key?

Your DynamoDB table is keyed by userId. Now product wants "all orders with status PENDING", or "find a user by email". You try:

await ddb.query({ TableName: "Orders", KeyConditionExpression: "status = :s", ... })
// ValidationException: Query condition missed key schema element: userId

So you switch to Scan with a FilterExpression. It works in development, then takes 40 seconds and costs a fortune in production. Here's how querying on non-key attributes is supposed to work in DynamoDB.

Query vs. Scan

• Query needs an equality condition on the partition key and can optionally narrow the sort key (=, <, >, BETWEEN, begins_with). It reads only the matching item collection, so it's fast and cheap at any table size.
• Scan reads every item in the table, 1 MB per page, and charges for everything it reads.

A FilterExpression doesn't change that. Filters are applied after items are read, so you pay read capacity for every scanned item, including the 99.9% the filter throws away. Filters are only for trimming a Query result that's already small.

(PartiQL's SELECT … WHERE status = 'PENDING' without the key is the same full scan, just in SQL syntax.)

The fix: design keys for your access patterns

DynamoDB flips the relational approach. You list your queries first and then shape keys and indexes so that every query is a key lookup.

Global secondary indexes (GSIs)

A GSI is a second copy of the data with a different partition key and sort key:

// GSI "status-createdAt": partition key status, sort key createdAt
await ddb.query({
  TableName: "Orders",
  IndexName: "status-createdAt",
  KeyConditionExpression: "#s = :s AND createdAt > :since",
  ExpressionAttributeNames: { "#s": "status" },
  ExpressionAttributeValues: { ":s": "PENDING", ":since": "2026-09-01" },
});

Things to know:

• GSIs are eventually consistent. A write may take a moment to show up in the index, and strongly consistent reads aren't supported on them.
• They have their own throughput (in provisioned mode) and cost a write for every base-table write that touches them, so project only the attributes you need (KEYS_ONLY, INCLUDE, or ALL).
• GSI key values don't have to be unique. "Find user by email" is a GSI with email as the partition key (enforce uniqueness separately, for example with a conditional put on an EMAIL#… item).
• Watch for hot index partitions. Partitioning a GSI on status puts every PENDING order in one partition. For high write rates, add a suffix to spread them (PENDING#0…PENDING#9) and query each shard.

Local secondary indexes (LSIs)

An LSI keeps the same partition key with a different sort key, for example orders for one user sorted by total instead of createdAt. LSIs support strongly consistent reads, but they must be created with the table and limit each partition key's item collection to 10 GB.

Sparse indexes

Only items that have the GSI's key attribute appear in the index. Set pendingSince only on pending orders and remove it when they ship; the index then contains only pending orders and stays small and cheap.

Composite sort keys

Overload the sort key to support several patterns with begins_with:

PK = USER#42    SK = ORDER#2026-09-26#A17
PK = USER#42    SK = PROFILE
PK = USER#42    SK = ADDRESS#home

Query PK = USER#42 AND begins_with(SK, "ORDER#2026-09") returns one user's orders for September. This is the basis of single-table design: related entities share a partition so one query fetches them together.

When you really need ad-hoc queries

If the questions aren't known in advance (analytics, admin search, reports), DynamoDB is the wrong engine to ask:

• Export to S3 (point-in-time export, no read capacity used) and query with Athena.
• Use the zero-ETL integrations to stream into OpenSearch (search and filtering) or Redshift (analytics).
• For a one-off backfill, a parallel scan (Segment / TotalSegments) finishes sooner, but it still reads the whole table, so rate-limit it.

Checklist

1. Query needs the partition key. Scan + FilterExpression still reads and bills every item.
2. Add a GSI for each new access pattern, and project only the attributes you need.
3. Use sparse indexes and composite sort keys to keep indexes small and flexible.
4. Send ad-hoc analytics to S3/Athena, OpenSearch or Redshift, not to Scan.