Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 37 ยท Queries, Scans and Indexes

Filters, Projections and Pagination

Understand DynamoDB filter expressions (applied after reading), projection expressions, ScannedCount vs Count, the 1 MB page limit and LastEvaluatedKey pagination.

A Query returns items from a key range. Often you want fewer of them, fewer attributes of each, or you want to walk through a long result page by page. Three features handle this: filter expressions, projection expressions and pagination. They are easy to use and easy to misunderstand, mostly because of one fact: filters and projections do not make a read cheaper. This lesson explains what they do and do not do, and shows the standard loop for pagination.

Filter expressions

A FilterExpression is applied to the items a query (or scan) has already read, just before results are returned. Items that do not match are dropped from the response.

Terminal
aws dynamodb query \
  --table-name AppData \
  --key-condition-expression "PK = :pk AND begins_with(SK, :p)" \
  --filter-expression "#s = :open AND Total > :min" \
  --expression-attribute-names '{"#s": "Status"}' \
  --expression-attribute-values '{
     ":pk": {"S": "CUSTOMER#amina"}, ":p": {"S": "ORDER#"},
     ":open": {"S": "open"}, ":min": {"N": "10"}
  }'

The filter can use any non-key attribute (and the same operators and functions as a condition expression: comparisons, BETWEEN, IN, contains, begins_with, size, attribute_exists). It cannot reference the partition key, and for a Query it cannot reference the sort key either: use the key condition for those.

What the response tells you

Field Meaning
Count Items returned after the filter
ScannedCount Items evaluated before the filter
LastEvaluatedKey Present when more data may follow

If ScannedCount is much larger than Count, you are reading far more than you use, and paying for all of it. A filter is a convenience for trimming results, not an index. When most items fail the filter, move the attribute into a sort key or an index instead.

Projection expressions

A ProjectionExpression lists which attributes to return, including nested paths.

TEXT
ProjectionExpression: "SK, Total, Address.City, Tags[0]"

It reduces the bytes sent over the network and your processing, not the capacity units: DynamoDB still reads whole items. With a global secondary index, however, the index's projection (which attributes the index stores) does change what is read and stored, a topic of the index lessons.

The 1 MB page limit

A Query or Scan evaluates at most 1 MB of data per call. If the key range holds more, it stops and returns a LastEvaluatedKey, the key of the last item evaluated. To continue, pass it back as ExclusiveStartKey. When the response has no LastEvaluatedKey, you have reached the end.

Python
def query_all(table, **kwargs):
    items, start = [], None
    while True:
        if start:
            kwargs["ExclusiveStartKey"] = start
        response = table.query(**kwargs)
        items.extend(response["Items"])
        start = response.get("LastEvaluatedKey")
        if not start:
            return items

Note that a page can be empty, or contain few items, and still have a LastEvaluatedKey, because the filter removed everything on that page. Always loop until the key is absent. Boto3 also offers paginators (client.get_paginator("query")) that run this loop for you.

The Limit parameter

Limit caps the number of items evaluated in one call, before the filter. It is useful for paging by a fixed number, but it behaves differently from SQL's LIMIT when combined with a filter.

If you ask for Limit=10 with a filter that matches 20% of items, you will typically get around two items back, plus a LastEvaluatedKey. To collect 10 matching items you must loop and count them yourself.

A paginated query with a filter

The stand-in below stores 60 items of about 100 KB each, so 1 MB fits only about ten items per page. It models FilterExpression, Count, ScannedCount, the page size limit and LastEvaluatedKey.

Python Stand-in: paginate with a 1 MB limit and a filter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every call scanned ten items (1 MB) but returned only two. You are billed for the 60 items read, not the 12 returned. Putting Status into the key design, or an index, would have read only the 12.

Limit plus filter surprises

Python Stand-in: Limit counts evaluated items

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Counting items

Select=COUNT returns only the count, which saves bandwidth but still reads (and charges for) the items. A count over a big range is a slow and expensive query. If you need totals regularly, keep a counter item updated with UpdateItem ... ADD, or maintain it from a stream. Also remember that Count is per page: loop and add them up.

Pagination in a web or mobile API

Do not expose LastEvaluatedKey unprotected: it reveals key values. Encode it into an opaque token (base64 of JSON, ideally signed) that the client sends back to get the next page. Because DynamoDB pagination is cursor-based, there is no "go to page 7"; offer "next" and "load more" instead.

Python Stand-in: opaque pagination tokens

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Using filters for selectivity, then wondering why costs grow. Fix the key or add an index.
  • Reading only the first page. If LastEvaluatedKey exists, there is more.
  • Stopping at an empty page. It can still have a next page.
  • Expecting projections to save capacity.
  • Expressing key conditions as filters. They are evaluated after reading and cannot narrow the read.
  • Filtering on the sort key in a Query: not allowed, use the key condition.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When is a FilterExpression applied?

    Filters run after the read. You are charged for everything read, including items the filter removes.
  2. 2. ScannedCount is 500 and Count is 12. What does that tell you?

    ScannedCount is the number evaluated before filtering, and Count is the number returned afterwards.
  3. 3. How do you know there are more results to fetch?

    A LastEvaluatedKey means the query stopped early. Keep requesting with ExclusiveStartKey until it is absent.
  4. 4. Does ProjectionExpression reduce consumed read capacity?

    Whole items are read regardless, so capacity is unchanged. The projection trims the response.

Summary

  • Filter expressions run after the read and do not reduce its cost; use keys or indexes for selectivity.
  • Count is returned items, ScannedCount is evaluated items.
  • Projection expressions trim the response, not the capacity used.
  • Each call reads at most 1 MB; loop on LastEvaluatedKey until it is absent, even across empty pages.
  • Limit counts evaluated items, so combined with a filter it may return fewer.

Next: The Scan Operation