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.

CodePudding user response：

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.