DynamoDb Query using Between operator on Primary(Partition) Key only

Question

I have a Dynamo table in which I have partition key only - "CreatedAt" and some other attributes.

I want to write a Query in which I will get no. of items where CreatedAt is between LastSunday and Today.

I have stored the date in string format as "2021-03-09T14:33:29Z".

I do not want to use GSI's.

with below query I get an error - "Query key condition not supported"

Here is my code :

 var queryRequest = new QueryRequest
            {
                TableName = myTableName,
                KeyConditionExpression = "CreatedAt BETWEEN :LastMonday AND :TodaysDate",
            };
            Dictionary<string, AttributeValue> expressionAttributeValues = new Dictionary<string, AttributeValue>();
            expressionAttributeValues.Add(":TodaysDate", new AttributeValue { S = "2021-03-09T14:33:29Z" });
            expressionAttributeValues.Add(":LastMonday", new AttributeValue { S = "2021-03-02T14:33:29Z" });
            queryRequest.ExpressionAttributeValues = expressionAttributeValues;
            var response = await awsDynamoClient.QueryAsync(queryRequest);

Seth Geoghegan · Accepted Answer · 2021-03-09T20:11:46.703

DynamoDB does not support conditional operations on the partition key.

From the docs:

Use the KeyConditionExpression parameter to provide a specific value for the partition key. The Query operation will return all of the items from the table or index with that partition key value. You can optionally narrow the scope of the Query operation by specifying a sort key value and a comparison operator in KeyConditionExpression.

The query operation requires that you specify the full partition key. The getItem operation requires that you provide the full primary key. There is no option to search by specifying characteristics of the partition key (e.g. begins_with, between, etc). Sort keys, on the other hand, are built specifically for this purpose.

If you want to do a range query, you should define a composite primary key when your CreatedAt attribute is the sort key. A composite primary key has both a partition key and a sort key. The sort key will allow you to fetch data using the between condition you are describing.

EDIT: For completeness, I'll add that you can perform a scan operation where you apply conditions to the partition key. There are few, if any, instances where this is a good idea. It's an expensive operation that should be avoided unless you really know what you are doing.

DynamoDb Query using Between operator on Primary(Partition) Key only

1 Answers1