Query with sort key conditions in DynamoDB - Step-by-Step Execution

Concept Flow - Query with sort key conditions

Start Query

↓

Specify Partition Key

↓

Add Sort Key Condition

↓

DynamoDB Filters Items

↓

Return Matching Items

↓

End Query

The query starts by specifying a partition key, then adds a condition on the sort key. DynamoDB filters items matching both keys and returns them.

Execution Sample

DynamoDB

Query {
  KeyConditionExpression: "UserId = :uid AND OrderDate > :date",
  ExpressionAttributeValues: {
    ":uid": "user123",
    ":date": "2023-01-01"
  }
}

This query fetches items for user 'user123' where the order date is after January 1, 2023.

Execution Table

Step	Action	Partition Key Check	Sort Key Condition Check	Result
1	Start query with UserId = 'user123' and OrderDate > '2023-01-01'	UserId matches 'user123'	Sort key condition not checked yet	Continue
2	Check item with UserId='user123', OrderDate='2022-12-31'	Matches	2022-12-31 > 2023-01-01? No	Exclude item
3	Check item with UserId='user123', OrderDate='2023-01-02'	Matches	2023-01-02 > 2023-01-01? Yes	Include item
4	Check item with UserId='user456', OrderDate='2023-02-01'	UserId matches 'user123'? No	Skip sort key check	Exclude item
5	End query	-	-	Return included items

💡 Query ends after checking all items with partition key 'user123' and applying sort key condition.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Current Item	None	{UserId:'user123', OrderDate:'2022-12-31'}	{UserId:'user123', OrderDate:'2023-01-02'}	{UserId:'user456', OrderDate:'2023-02-01'}	None
Included Items	[]	[]	[{UserId:'user123', OrderDate:'2023-01-02'}]	[{UserId:'user123', OrderDate:'2023-01-02'}]	[{UserId:'user123', OrderDate:'2023-01-02'}]

Key Moments - 2 Insights

Why are items with a different UserId excluded immediately?

Because the partition key must match exactly. The query only looks at items where UserId = 'user123', so items with UserId 'user456' are skipped without checking the sort key condition (see Step 4 in execution_table).

What happens if the sort key condition is false for an item?

The item is excluded from the results even if the partition key matches. For example, in Step 2, the OrderDate '2022-12-31' is not greater than '2023-01-01', so the item is excluded.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of checking the item with OrderDate '2023-01-02'?

A Item is excluded

B Item is skipped

C Item is included

D Item causes query to stop

💡 Hint

See Step 3 in execution_table where the sort key condition is true and the item is included.

At which step does the query exclude an item because the partition key does not match?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Check Step 4 in execution_table where UserId is 'user456' and does not match 'user123'.

If the sort key condition was changed to OrderDate >= '2023-01-01', which item would be included additionally?

A Item with OrderDate '2022-12-31'

B Item with OrderDate '2023-01-01'

C Item with UserId 'user456'

D No additional items

💡 Hint

Think about the boundary condition and which dates satisfy OrderDate >= '2023-01-01'.

Concept Snapshot

Query with sort key conditions in DynamoDB:
- Specify partition key exactly (e.g., UserId = :uid)
- Add sort key condition (e.g., OrderDate > :date)
- DynamoDB returns items matching both
- Items with wrong partition key are skipped
- Sort key condition filters items within partition

Full Transcript

This visual execution shows how a DynamoDB query works when you add a condition on the sort key. First, the query looks for items with the exact partition key, here UserId 'user123'. Then it checks each item's sort key, OrderDate, to see if it meets the condition (greater than '2023-01-01'). Items that don't match the partition key are ignored immediately. Items that match the partition key but fail the sort key condition are excluded. Only items passing both checks are returned. This step-by-step helps understand how DynamoDB efficiently filters data using keys.

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