Query with sort key conditions in DynamoDB

Introduction

You use sort key conditions to find specific items in a group, making your search faster and more focused.

When you want to find all orders made by a customer after a certain date.

When you need to get messages in a chat sorted by time.

When you want to list products in a category within a price range.

When you want to retrieve events that happened before a specific time.

When you want to get all versions of a document sorted by version number.

Syntax

DynamoDB

Query {
  KeyConditionExpression: "PartitionKeyName = :pkval AND SortKeyName operator :skval",
  ExpressionAttributeValues: {
    ":pkval": {S: "partition_key_value"},
    ":skval": {S: "sort_key_value"} // or {N: "sort_key_value"}
  }
}

The partition key must always be specified with '=' in the KeyConditionExpression.

The sort key condition can use operators like =, <, <=, >, >=, BETWEEN, or BEGINS_WITH.

Examples

Finds all items for user 'user123' created after January 1, 2023.

DynamoDB

KeyConditionExpression: "UserId = :uid AND CreatedAt > :date"
ExpressionAttributeValues: {
  ":uid": {S: "user123"},
  ":date": {S: "2023-01-01T00:00:00Z"}
}

Finds all books priced between 10 and 50.

DynamoDB

KeyConditionExpression: "Category = :cat AND Price BETWEEN :min AND :max"
ExpressionAttributeValues: {
  ":cat": {S: "Books"},
  ":min": {N: "10"},
  ":max": {N: "50"}
}

Finds all messages in chat 'chat789' where message IDs start with 'msg2023'.

DynamoDB

KeyConditionExpression: "ChatId = :cid AND begins_with(MessageId, :prefix)"
ExpressionAttributeValues: {
  ":cid": {S: "chat789"},
  ":prefix": {S: "msg2023"}
}

Sample Program

This query finds all orders for customer 'cust123' made on or after January 1, 2023.

DynamoDB

const AWS = require('aws-sdk');
const dynamodb = new AWS.DynamoDB.DocumentClient();

const params = {
  TableName: 'Orders',
  KeyConditionExpression: 'CustomerId = :cid AND OrderDate >= :startDate',
  ExpressionAttributeValues: {
    ':cid': 'cust123',
    ':startDate': '2023-01-01'
  }
};

dynamodb.query(params, (err, data) => {
  if (err) {
    console.error('Error querying:', err);
  } else {
    console.log('Orders:', data.Items);
  }
});

Important Notes

You must always specify the partition key with '=' in the KeyConditionExpression.

Sort key conditions help narrow down results within the partition key.

Using BETWEEN or begins_with can help find ranges or prefixes in sort keys.

Summary

Query with sort key conditions lets you find items within a partition using filters on the sort key.

You always specify the partition key with '=' and can use operators on the sort key.

This makes your database queries faster and more precise.

Practice

1. In DynamoDB, when you use a Query operation with a sort key condition, which of the following is always required?

easy

A. Specify only the sort key condition without the partition key

B. Use a scan operation instead of query

C. Specify the partition key with '=' and a condition on the sort key

D. Specify only the partition key without any sort key condition

Solution

1. Step 1: Understand Query requirements

   A DynamoDB Query must always specify the partition key with '=' to identify the partition.

2. Step 2: Add sort key condition

   You can add conditions on the sort key to filter items within that partition.

3. Final Answer:

   Specify the partition key with '=' and a condition on the sort key -> Option C

4. Quick Check:

   Partition key '=' + sort key condition = required [OK]

Hint: Partition key '=' is mandatory in Query with sort key condition [OK]

Common Mistakes:

• Trying to query without specifying partition key
• Using scan instead of query for sort key filtering
• Specifying only sort key condition without partition key

2. Which of the following is the correct syntax for a DynamoDB Query with a sort key condition to find items where the sort key Timestamp is greater than 1000?

easy

A. KeyConditionExpression: 'PartitionKey > :pk AND Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

B. KeyConditionExpression: 'PartitionKey = :pk OR Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

C. KeyConditionExpression: 'PartitionKey = :pk AND Timestamp == :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

D. KeyConditionExpression: 'PartitionKey = :pk AND Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

Solution

1. Step 1: Use '=' for partition key

   The partition key must be compared with '=' in the KeyConditionExpression.

2. Step 2: Use '>' for sort key condition

   The sort key condition can use operators like '>' to filter items.

3. Final Answer:

   KeyConditionExpression: 'PartitionKey = :pk AND Timestamp > :ts' -> Option D

4. Quick Check:

   PartitionKey '=' and sort key '>' correct syntax [OK]

Hint: Partition key '=' and sort key condition combined with AND [OK]

Common Mistakes:

• Using OR instead of AND in KeyConditionExpression
• Using '>' for partition key instead of '='
• Using '==' instead of '=' for partition key

3. Given a DynamoDB table with partition key UserID and sort key OrderDate, what will the following query return?

KeyConditionExpression: 'UserID = :uid AND OrderDate BETWEEN :start AND :end'
ExpressionAttributeValues: { ':uid': 'user123', ':start': '2023-01-01', ':end': '2023-01-31' }

medium

A. All orders for 'user123' placed between January 1 and January 31, 2023 inclusive

B. All orders for all users placed between January 1 and January 31, 2023

C. All orders for 'user123' placed before January 1, 2023

D. Syntax error due to BETWEEN usage in KeyConditionExpression

Solution

1. Step 1: Partition key '=' filters user

   The query filters items where UserID equals 'user123'.

2. Step 2: Sort key BETWEEN filters date range

   The BETWEEN operator selects OrderDate values from '2023-01-01' to '2023-01-31' inclusive.

3. Final Answer:

   All orders for 'user123' placed between January 1 and January 31, 2023 inclusive -> Option A

4. Quick Check:

   Partition key '=' + sort key BETWEEN returns filtered range [OK]

Hint: BETWEEN filters sort key range within one partition [OK]

Common Mistakes:

• Assuming query returns all users' orders
• Thinking BETWEEN excludes boundary dates
• Believing BETWEEN is invalid in KeyConditionExpression

4. You wrote this DynamoDB query but it returns no results:

KeyConditionExpression: 'UserID = :uid AND OrderDate > :date'
ExpressionAttributeValues: { ':uid': 'user123', ':date': '2023-12-31' }

What is the most likely reason?

medium

A. No items exist with OrderDate after 2023-12-31 for user123

B. Using '>' operator is not allowed in KeyConditionExpression

C. Partition key UserID should use '>' instead of '='

D. ExpressionAttributeValues keys must not start with ':'

Solution

1. Step 1: Check operator validity

   The '>' operator is valid for sort key conditions in DynamoDB queries.

2. Step 2: Consider data existence

   If no items have OrderDate after '2023-12-31' for 'user123', query returns empty.

3. Final Answer:

   No items exist with OrderDate after 2023-12-31 for user123 -> Option A

4. Quick Check:

   Valid syntax but no matching data = empty result [OK]

Hint: Empty results often mean no matching data, not syntax error [OK]

Common Mistakes:

• Thinking '>' is invalid in KeyConditionExpression
• Using '>' for partition key instead of '='
• Misunderstanding ExpressionAttributeValues syntax

5. You want to query a DynamoDB table with partition key CustomerID and sort key InvoiceDate. You need to find all invoices for CustomerID = 'C123' where InvoiceDate is either before '2023-01-01' or after '2023-12-31'. Which approach correctly achieves this?

hard

A. Use KeyConditionExpression with BETWEEN operator for the two date ranges

B. Run two separate queries: one with InvoiceDate < '2023-01-01' and another with InvoiceDate > '2023-12-31', then combine results

C. Use a scan operation with filter expression for the date conditions

D. Use a single query with KeyConditionExpression: CustomerID = :cid AND InvoiceDate < '2023-01-01' OR InvoiceDate > '2023-12-31'

Solution

1. Step 1: Understand KeyConditionExpression limits

   DynamoDB KeyConditionExpression supports only AND between partition key and sort key conditions, no OR.

2. Step 2: Query with OR on sort key requires multiple queries

   To get items before '2023-01-01' OR after '2023-12-31', run two queries and merge results.

3. Final Answer:

   Run two separate queries: one with InvoiceDate < '2023-01-01' and another with InvoiceDate > '2023-12-31', then combine results -> Option B

4. Quick Check:

   OR on sort key = multiple queries combined [OK]

Hint: DynamoDB Query KeyConditionExpression uses AND only; split OR into queries [OK]

Common Mistakes:

• Trying to use OR in KeyConditionExpression
• Using BETWEEN for non-continuous ranges
• Using scan instead of efficient queries