DynamoDB: How to query "Composite sort keys" properly

Question

Hi,

I was reading the following documentation: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-sort-keys.html when I found this very interesting idea:

> Composite sort keys let you define hierarchical (one-to-many) relationships in your data that you can query at any level of the hierarchy.

With an example of sort key being composed like this:

> [country]#[region]#[state]#[county]#[city]#[neighborhood]

The only thing which is missing is an example of how to query such thing.

Or maybe I am not getting it correctly. Can someone please explain this to me with an example?

My assumption is that somehow I will be able to query by state for instance or city. Is this just as simple as using `contains(#sk, :city)` with `:city = #New York#` for instance? Or is there more to it?

re:Post Agent · Accepted Answer

When working with composite sort keys in DynamoDB, you can use the `begins_with` function in your key condition expressions to query at different levels of the hierarchy, not the `contains` function.

The hierarchical design with composite sort keys allows you to query at any level of the hierarchy by using the `begins_with` operator on the sort key. This works because the values are stored in a predictable format with delimiters (like "#").

For example, if your sort key follows the pattern `[country]#[region]#[state]#[county]#[city]#[neighborhood]`, you can query:

1. For all items in a specific country:
```
KeyConditionExpression: "partitionKey = :pk AND begins_with(sortKey, 'USA#')"
```

2. For all items in a specific region within a country:
```
KeyConditionExpression: "partitionKey = :pk AND begins_with(sortKey, 'USA#Northeast#')"
```

3. For all items in a specific state:
```
KeyConditionExpression: "partitionKey = :pk AND begins_with(sortKey, 'USA#Northeast#NewYork#')"
```

And so on for more specific levels.

In Python using Boto3, this would look like:
```python
key_condition = Key('partitionKey').eq(partition_key_value) & Key('sortKey').begins_with('USA#Northeast#NewYork#')
response = table.query(KeyConditionExpression=key_condition)
```

The important points to remember:
1. You must always specify the partition key in your query
2. Use `begins_with` (not `contains`) for hierarchical queries
3. Include all higher levels of the hierarchy and the delimiter when querying a specific level
4. The query is efficient because it uses the sort key index

This approach allows you to model one-to-many relationships and query at any level of the hierarchy efficiently.  
**Sources**  
[Community | 20 Days of DynamoDB](https://community.aws/content/2adE2Du73d6GSemMIKvXcbbN0tI/20-days-of-dynamodb)  
[Query DynamoDB tables using date and time patterns with an AWS SDK - AWS SDK Code Examples](https://docs.aws.amazon.com/code-library/latest/ug/dynamodb_example_dynamodb_Scenario_DateTimeQueries_section.html)  
[Query a DynamoDB table using a date range in the sort key with an AWS SDK - Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/example_dynamodb_Scenarios_QueryWithDateRange_section.html)  
[Query DynamoDB tables using date and time patterns with an AWS SDK - Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/example_dynamodb_Scenario_DateTimeQueries_section.html)  
[DynamoDB PartiQL vs. Scan | AWS re:Post](https://repost.aws/questions/QUAl9OXRGIRLC0RicZJr4uiA/dynamodb-partiql-vs-scan)

lyradaven · Answer

Great question — this is a common point of confusion when starting with composite sort keys. The key thing to remember is that DynamoDB doesn’t support contains() on sort keys for efficient lookups — instead, you leverage begins_with() or key equality.

For your example:

PK = "USA"
SK = "USA#CA#OrangeCounty#Irvine"

You can query by state like this:

KeyConditionExpression = "PK = :country AND begins_with(SK, :state)"
ExpressionAttributeValues = {
  ":country": "USA",
  ":state": "USA#CA"
}

This will return all items where the sort key starts with USA#CA, effectively giving you all counties/cities in California.

The main advantage of composite sort keys is that you can “slice” the hierarchy at any level — country, region, state, etc. — as long as you query with a proper prefix using begins_with().

DynamoDB: How to query "Composite sort keys" properly

回答を追加する

関連するコンテンツ