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.