DynamodbHow-ToBeginner · 3 min read

How to Use BETWEEN in DynamoDB Query: Syntax and Example

In DynamoDB, use the BETWEEN operator in the KeyConditionExpression to filter items where an attribute value falls within a range. It works with sort keys and requires specifying the attribute name and two values representing the range limits.

📐

Syntax

The BETWEEN operator is used in the KeyConditionExpression to filter items by a range on the sort key. The syntax is:

PartitionKeyName = :partitionKeyValue: Filters by the partition key (required).
SortKeyName BETWEEN :startValue AND :endValue: Filters sort key values between two limits.
Use ExpressionAttributeValues to define the actual values for placeholders.

json

KeyConditionExpression: "PartitionKeyName = :pkval AND SortKeyName BETWEEN :start AND :end"
ExpressionAttributeValues: {
  ":pkval": {S: "partitionKeyValue"},
  ":start": {S: "startValue"},
  ":end": {S: "endValue"}
}

💻

Example

This example queries a DynamoDB table named Orders to find all orders for customer Cust123 where the order date is between 2023-01-01 and 2023-01-31. It demonstrates using BETWEEN on the sort key OrderDate.

javascript

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

const params = {
  TableName: 'Orders',
  KeyConditionExpression: 'CustomerId = :cid AND OrderDate BETWEEN :start AND :end',
  ExpressionAttributeValues: {
    ':cid': 'Cust123',
    ':start': '2023-01-01',
    ':end': '2023-01-31'
  }
};

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

Output

Query results: [ { CustomerId: 'Cust123', OrderDate: '2023-01-05', OrderId: '001', Amount: 100 }, { CustomerId: 'Cust123', OrderDate: '2023-01-20', OrderId: '002', Amount: 150 } ]

⚠️

Common Pitfalls

Using BETWEEN on attributes that are not the sort key will cause errors.
For KeyConditionExpression, the partition key must be specified with equality (=), and BETWEEN only applies to the sort key.
Incorrectly formatting the values or missing ExpressionAttributeValues placeholders causes query failures.
Trying to use BETWEEN in a FilterExpression instead of KeyConditionExpression will not optimize the query and may return unexpected results.

text

/* Wrong: Using BETWEEN on partition key */
KeyConditionExpression: 'CustomerId BETWEEN :start AND :end',

/* Right: Use = on partition key and BETWEEN on sort key */
KeyConditionExpression: 'CustomerId = :cid AND OrderDate BETWEEN :start AND :end'

📊

Quick Reference

Component	Description	Example
Partition Key	Must be specified with equality (=) in KeyConditionExpression	CustomerId = :cid
Sort Key with BETWEEN	Filters sort key values between two limits	OrderDate BETWEEN :start AND :end
ExpressionAttributeValues	Defines actual values for placeholders	{ ':cid': 'Cust123', ':start': '2023-01-01', ':end': '2023-01-31' }
BETWEEN Operator	Includes both start and end values in range	BETWEEN '2023-01-01' AND '2023-01-31'

✅

Key Takeaways

Use BETWEEN only on the sort key in the KeyConditionExpression of a DynamoDB query.

Always specify the partition key with equality (=) when using BETWEEN on the sort key.

Define all placeholder values in ExpressionAttributeValues to avoid errors.

BETWEEN includes both the start and end values in the range filter.

Avoid using BETWEEN in FilterExpression for key filtering; it should be in KeyConditionExpression.