Working with Queries and Scans

A Query operation finds items in a table or a secondary index using only primary key attribute values. You must provide a partition key name and a value to search for. You can optionally provide a sort key name and value, and use a comparison operator to refine the search results. By default, a Query operation returns all of the data attributes for items with the specified primary key(s); however, you can use the ProjectionExpression parameter so that the Query operation only returns some of the attributes, rather than all of them.

In a Query operation, you use the KeyConditionExpression parameter to determine the items to be read from the table or index. You must specify the partition key name and value as an equality condition. You can optionally provide a second condition for the sort key (if present). The sort key condition must use one of the following comparison operators:

The following function is also supported:

The following are some examples of key condition expressions. Note that these expressions use placeholders (such as :name and :subj) instead of actual values. For more information, see Expression Attribute Names and Expression Attribute Values.

You can use any attribute name in a key condition expression, provided that the first character is a-z or A-Z and the second character (if present) is a-z, A-Z, or 0-9. In addition, the attribute name must not be a DynamoDB reserved word. (For a complete list of these, see Reserved Words in DynamoDB.) If an attribute name does not meet these requirements, you will need to define an expression attribute name as a placeholder. For more information, see Expression Attribute Names.

For items with a given partition key value, DynamoDB stores these items close together, in sorted order by sort key value. In a Query operation, DynamoDB retrieves the items in sorted order, and then processes the items using KeyConditionExpression and any FilterExpression that might be present. Only then are the Query results sent back to the client.

A Query operation always returns a result set. If no matching items are found, the result set will be empty.

Query results are always sorted by the sort key value. If the data type of the sort key is Number, the results are returned in numeric order; otherwise, the results are returned in order of UTF-8 bytes. By default, the sort order is ascending. To reverse the order, set the ScanIndexForward parameter to false.

A single Query or Scan operation can retrieve a maximum of 1 MB of data. This limit applies before any FilterExpression is applied to the results. If LastEvaluatedKey is present in the response and is non-null, you will need to paginate the result set (see Paginating the Results).

A Scan operation reads every item in a table or a secondary index. By default, a Scan operation returns all of the data attributes for every item in the table or index. You can use the ProjectionExpression parameter so that Scan only returns some of the attributes, rather than all of them.

Scan always returns a result set. If no matching items are found, the result set will be empty.

A single Scan request can retrieve a maximum of 1 MB of data; DynamoDB can optionally apply a filter expression to this data, narrowing the results before they are returned to the user. (For more information on filters, see Filtering the Results from a Query or a Scan.)

With a Query or a Scan operation, you can provide an optional filter expression to refine the results returned to you. A filter expression lets you apply conditions to the data after it is queried or scanned, but before it is returned to you. Only the items that meet your conditions are returned.

The following are some examples of filter expressions. Note that these expressions use placeholders (such as :num and :name) instead of actual values. For more information, see Expression Attribute Names and Expression Attribute Values.

A single Query or Scan operation can retrieve a maximum of 1 MB of data. This limit applies before any filter expression is applied to the results.

When you create a table, you specify your read and write capacity unit requirements. If you add a global secondary index to the table, you must also provide the throughput requirements for that index.

You can use Query and Scan operations on secondary indexes in the same way that you use these operations on a table. If you use Query or Scan on a local secondary index, then capacity units are consumed from the table's provisioned throughput. However, if you perform these operations on a global secondary index, capacity units are consumed from the provisioned throughput of the index. This is because a global secondary index has its own provisioned throughput settings, separate from those of its table.

For more information about how DynamoDB computes the capacity units consumed by your operation, see Capacity Unit Calculations.

DynamoDB paginates the results from Query and Scan operations. With pagination, Query and Scan results are divided into distinct pieces; an application can process the first page of results, then the second page, and so on. The data returned from a Query or Scan operation is limited to 1 MB; this means that if the result set exceeds 1 MB of data, you'll need to perform another Query or Scan operation to retrieve the next 1 MB of data.

If you query or scan for specific attributes that match values that amount to more than 1 MB of data, you'll need to perform another Query or Scan request for the next 1 MB of data. To do this, take the LastEvaluatedKey value from the previous request, and use that value as the ExclusiveStartKey in the next request. This approach will let you progressively query or scan for new data in 1 MB increments.

When the entire result set from a Query or Scan has been processed, LastEvaluatedKey is null. This indicates that the result set is complete (that is, the operation processed the “last page” of data).

If LastEvaluatedKey is anything other than null, this does not necessarily mean that there is more data in the result set. The only way to know when you have reached the end of the result set is when LastEvaluatedKey is null.

The DynamoDB Query and Scan APIs allow a Limit value to restrict the size of the results.

In a request, set the Limit parameter to the number of items that you want DynamoDB to process before returning results.

In a response, DynamoDB returns all the matching results within the scope of the Limit value. For example, if you issue a Query or a Scan request with a Limit value of 6 and without a filter expression, DynamoDB returns the first six items in the table that match the specified key conditions in the request (or just the first six items in the case of a Scan with no filter). If you also supply a FilterExpression value, DynamoDB will return the items in the first six that also match the filter requirements (the number of results returned will be less than or equal to 6).

For either a Query or Scan operation, DynamoDB might return a LastEvaluatedKey value if the operation did not return all matching items in the table. To get the full count of items that match, take the LastEvaluatedKey value from the previous request and use it as the ExclusiveStartKey value in the next request. Repeat this until DynamoDB no longer returns a LastEvaluatedKey value.

In addition to the items that match your criteria, the response from a Query or Scan operation contains the following elements:

If you do not use a FilterExpression, then ScannedCount and Count will have the same value.

Suppose that you have a table named TestTable with 1000 items, and the size of each item is exactly 250 bytes. If you were to Scan this table (with no filter), then ScannedCount and Count would have the same value (1000). Now suppose that you added a FilterExpression so that only 300 items would be returned. DynamoDB would still read all 1000 items from TestTable, but would discard all of these results except for the 300 items that matched your criteria. In this case, ScannedCount would be 1000, but Count would be 300.

If the size of the result set exceeds 1 MB, then ScannedCount and Count will represent only a partial count of the total items. Suppose that TestTable now has one million items, and the size of each item is 250 kilobytes. In this case, you would need to perform multiple Scan requests in order to scan the entire table. (For more information, see Paginating the Results.) Each response would contain the ScannedCount and Count for the items processed by that particular Scan request. To obtain grand totals for all of the Scan requests, you could to keep a running tally of both ScannedCount and Count.

Generally, a Query operation is more efficient than a Scan operation.

A Scan operation always scans the entire table or secondary index, then filters out values to provide the desired result, essentially adding the extra step of removing data from the result set. Avoid using a Scan operation on a large table or index with a filter that removes many results, if possible. Also, as a table or index grows, the Scan operation slows. The Scan operation examines every item for the requested values, and can use up the provisioned throughput for a large table or index in a single operation. For faster response times, design your tables and indexes so that your applications can use Query instead of Scan. (For tables, you can also consider using the GetItem and BatchGetItem APIs.).

Alternatively, design your application to use Scan operations in a way that minimizes the impact on your request rate. For more information, see Guidelines for Query and Scan.

A Query operation searches for a specific range of keys that satisfy a given set of key conditions. If you specify a filter expression, then DynamoDB must perform the extra step of removing data from the result set. A Query operation seeks the specified composite primary key, or range of keys, until one of the following events occurs:

Query performance depends on the amount of data retrieved, rather than the overall number of primary keys in a table or secondary index. The parameters for a Query operation (and consequently the number of matching keys) determine the performance of the query. For example, a query on a table that contains a large set of sort key values for a single partition key value can be more efficient than a query on another table that has fewer sort key values per partition key value, if the number of matching keys in the first table is fewer than in the second. The total number of primary keys, in either table, does not determine the efficiency of a Query operation. A filter expression can also have an impact on the efficiency of a Query operation, because the items that don't match the filter must be removed from the result set. Avoid using a Query operation on a large table or secondary index with a filter that removes many results, if possible.

If a specific partition key value has a large set of sort key values, and the results cannot be retrieved in a single Query request, the ExclusiveStartKey continuation parameter allows you to submit a new query request from the last retrieved item without reprocessing the data already retrieved.

By default, the Scan operation processes data sequentially. DynamoDB returns data to the application in 1 MB increments, and an application performs additional Scan operations to retrieve the next 1 MB of data.

The larger the table or index being scanned, the more time the Scan will take to complete. In addition, a sequential Scan might not always be able to fully utilize the provisioned read throughput capacity: Even though DynamoDB distributes a large table's data across multiple physical partitions, a Scan operation can only read one partition at a time. For this reason, the throughput of a Scan is constrained by the maximum throughput of a single partition.

To address these issues, the Scan operation can logically divide a table or secondary index into multiple segments, with multiple application workers scanning the segments in parallel. Each worker can be a thread (in programming languages that support multithreading) or an operating system process. To perform a parallel scan, each worker issues its own Scan request with the following parameters:

The following diagram shows how a multithreaded application performs a parallel Scan with three degrees of parallelism:

In this diagram, the application spawns three threads and assigns each thread a number. (Segments are zero-based, so the first number is always 0.) Each thread issues a Scan request, setting Segment to its designated number and setting TotalSegments to 3. Each thread scans its designated segment, retrieving data 1 MB at a time, and returns the data to the application's main thread.

The values for Segment and TotalSegments apply to individual Scan requests, and you can use different values at any time. You might need to experiment with these values, and the number of workers you use, until your application achieves its best performance.