Query in DynamoDB on the Basis of Partition Key and Sort Key

1. Introduction

Amazon DynamoDB is one of the core services offered by AWS. It is widely used for building fast, scalable, and serverless applications. It provides a fully managed NoSQL database solution with single-digit millisecond performance at any scale. Unlike traditional relational databases, DynamoDB uses a key-value and document data model that encourages designing data access patterns up front.

In this tutorial, we’ll focus on one of DynamoDB’s most powerful features: querying data using a composite primary key, which combines a Partition Key and a Sort Key. We’ll walk through how this composite key model works and demonstrate how to efficiently query data using it with the AWS SDK for Java.

2. Understanding the Composite Key Model

DynamoDB supports two types of primary keys: a simple key (just a Partition Key) and a composite key, which combines a Partition Key and a Sort Key.

The Partition Key determines where the data is stored, while the Sort Key allows sorting and filtering within that partition. This model fits related data, like user orders, under a single key. For example, in a UserOrders table:

• userId can be the Partition Key
• orderDate can be the Sort Key

This setup makes it easy to retrieve all a user’s orders, sort them by date, or filter by time range, all with a single query.

3. Maven Dependency

To interact with DynamoDB from a Java application, we’ll use the AWS SDK for Java v2, which provides a modern, non-blocking API:

<dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>dynamodb</artifactId>
    <version>2.31.26</version>
</dependency>

This gives us access to the DynamoDbClient and other necessary classes to run queries against our table.

4. Querying by Partition Key

The simplest and most common way to query data in DynamoDB is by using the Partition Key. When we query by Partition Key, DynamoDB returns all items sharing the same partition key value.

Let’s say our UserOrders table stores orders for multiple users, and we want to retrieve all orders placed by a single user. Since userId is our Partition Key, we can do this with a basic query:

QueryRequest queryRequest = QueryRequest.builder()
  .tableName("UserOrders")
  .keyConditionExpression("userId = :uid")
  .expressionAttributeValues(Map.of(
    ":uid", AttributeValue.builder().s("user1").build()
  )).build();

QueryResponse response = dynamoDbClient.query(queryRequest);

In the example above, we use the DynamoDbClient to execute the query. We built the request using the builder pattern, making it easy to construct complex requests. After executing the query, the results are returned in the QueryResponse object. To access the actual data, we use the items() method:

List<Map<String, AttributeValue>> items = response.items();

for (Map<String, AttributeValue> item : items) {
    System.out.println("Order item: " + item.get("item").s());
}

This list contains each item as a map of attribute names to values, which you can further process or convert into application-specific objects.

5. Querying with Partition Key and Sort Key

While querying by Partition Key alone might be useful, sometimes we need more precise filtering. We can achieve this by combining the Partition Key with conditions on the Sort Key. This allows us to filter results within a partition, for example, by a date range or a specific prefix.

Let’s say we want to retrieve all orders placed by user1 after January 1st, 2025. Since orderDate is our Sort Key, we can include a comparison in the keyConditionExpression:

QueryRequest queryRequest = QueryRequest.builder()
  .tableName("UserOrders")
  .keyConditionExpression("userId = :uid AND orderDate > :startDate")
  .expressionAttributeValues(Map.of(
    ":uid", AttributeValue.builder().s("user1").build(),
    ":startDate", AttributeValue.builder().s("2025-01-01").build()
  )).build();

QueryResponse response = dynamoDbClient.query(queryRequest);

In this query, we use the Partition Key and a condition on the Sort Key to narrow down the results. DynamoDB will only scan the partition for user1 and return items where the orderDate is after 2025-01-01. This approach is efficient because it avoids scanning unrelated data.

6. Common Range Key Conditions

DynamoDB supports several useful operators for filtering by the Sort Key. These allow us to fine-tune our queries within a partition by using standard comparison logic.

6.1. BETWEEN

We can use BETWEEN to retrieve items within a specific range. This is especially helpful when working with timestamps or dates:

QueryRequest queryRequest = QueryRequest.builder()
  .tableName("UserOrders")
  .keyConditionExpression("userId = :uid AND orderDate BETWEEN :from AND :to")
  .expressionAttributeValues(Map.of(
    ":uid", AttributeValue.builder().s("user1").build(),
    ":from", AttributeValue.builder().s("2024-12-01").build(),
    ":to", AttributeValue.builder().s("2024-12-31").build()
  )).build();

This will return all orders placed by user1 in December 2024.

6.2. BEGINS_WITH

If our Sort Key is a string (like a formatted date), we can query all items that start with a specific prefix. This is helpful for grouping by year, month, or any string-based prefix.

QueryRequest queryRequest = QueryRequest.builder()
  .tableName("UserOrders")
  .keyConditionExpression("userId = :uid AND begins_with(orderDate, :prefix)")
  .expressionAttributeValues(Map.of(
    ":uid", AttributeValue.builder().s("user1").build(),
    ":prefix", AttributeValue.builder().s("2025-01").build()
  )).build();

This will return all orders placed in January 2025.

7. Handling Pagination in Queries

DynamoDB limits the size of each query response to 1 MB of data. If our query matches more than that, DynamoDB returns a LastEvaluatedKey in the response, which we can use to continue fetching the next page of results.

To handle this, we should paginate through the results in a loop:

List<Map<String, AttributeValue>> allItems = new ArrayList<>();
Map<String, AttributeValue> lastKey = null;

do {
    QueryRequest.Builder requestBuilder = QueryRequest.builder()
      .tableName("UserOrders")
      .keyConditionExpression("userId = :uid")
      .expressionAttributeValues(Map.of(
        ":uid", AttributeValue.fromS(userId)
      ));

    if (lastKey != null) {
        requestBuilder.exclusiveStartKey(lastKey);
    }

    QueryResponse response = dynamoDb.query(requestBuilder.build());
    allItems.addAll(response.items());
    lastKey = response.lastEvaluatedKey();
} while (lastKey != null && !lastKey.isEmpty());

return allItems;

<This pattern ensures we retrieve all matching items, regardless of how many are returned per page. It’s useful when querying large partitions or doing report-style exports.

8. Conclusion

DynamoDB’s composite key model gives us a powerful way to organize and retrieve data efficiently. Using a Partition Key together with a Range Key allows us to organize related data and create efficient, scalable access patterns for high-performance applications.

In this article, we explored how to query a table using just the Partition Key and how to enhance those queries by including the Range Key to filter results within a partition.