CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodb
tibbe
tibbe

Reputation: 9009

DynamoDB query with both GT and begins_with for sort key?

I have a single table design where I have chat rooms (PK) with timestamped messages (SK). Since it's a single table design the SK has a MSG# prefix, followed by the message creation timestamp, to keep message entities separate from other entities.

I'd like to retrieve all messages after a certain timestamp. It seems like the key condition should be PK = "<ChatRoomId>" AND begins_with(SK, "MSG#") AND SK GT "MSG#<LastRead>". The first part of the SK condition is to only fetch message entities and the second is to only fetch new messages. Is it possible to have a double conditions on the sort key like this? It seems like it should be possible as it denotes a contiguous range of sort keys.

Upvotes: 1

Views: 1543

Answers (2)

vincent
vincent

Reputation: 6608

I have exactly the same use case and found out this answer, thanks for this suggestion, it works but we decided to research further - "between" is inclusive and we'd have to either waste one read capacity unit or make up a fake value as a workaround.

Turns out, the DynamoDB API provides this feature, it's the exclusive start key: https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-ExclusiveStartKey

Admittedly, the documentation is not very encouraging and seems to suggest that the parameter is some opaque data that you can only obtain by having a previous query:

The primary key of the first item that this operation will evaluate. Use the value that was returned for LastEvaluatedKey in the previous operation.

But the actual content of that key is very simple and transparent: it's a map like {"PK": {"S": "your_pk"}, "SK": {"S": "exclusive_start_sk"}} ( replace PK/SK with your actual key - if you're doing single table design you're probably using those generic names ). GSIPK/GSISK may be provided instead, if you're querying a GSI instead of the main table. You can do some manual query and observe the returned LastEvaluatedKey to verify what it's expecting.

From there you can combine greater_than and begins_with, greater_than being expressed as a pagination parameter

Upvotes: 1

Maurice
Maurice

Reputation: 13117

You can easily achieve that by using between:

PK = "<ChatRoomId>" AND SK BETWEEN "MSG#<YourDate>" AND "MSG#9999-99-99"

This way you will get all messages starting at <YourDate> and no records with other prefixes. This will work unless you're planning very far ahead.

Upvotes: 3

Related Questions