How to query over LSI in dynamoDb with Java

Question

I have a DynamoTable which has a LSI created as FOOD-OFFER#TIMESTAMP#2021-08-02T00:00:000Z#4567

4567 is offerID

I want to fetch results within a date range.

1. Is it possible to use beginsWith and between together. My Search criteria has only Dates in it. It has a Start Date and End Date and i want to fetch all results in between. is that even possible with this.
2. if i change the TimeStamp to Date in LSI. Will i be still able to query for a date range without offer ID ?

Kindly suggest if anyone has a example.

Answer 1

It is not possible to use multiple query types on a Sort Key - you can use one or the other, but not both.

You're best bet is to pick one (which ever will return the least amount of data usually) and then take the structure that is returned and filter it further in code rather than through a query. Its worth noting as a Sort Key, its automatically sorted - so if you only get index 0 of the returned items, and have ISO8601 as your date, you will always have the most latest (or if returned in descending order, it will be the oldest)

Alternatively, is to change your PK/SK set up, with your PK being something like FOOD_OFFER#4567 and SK of TIMESTAMP#YYYY-mm-ddThh:mm:ssZ. - this may mean a duplication of data in your system, OR you can add an additional attribute to all your items and make a GSI or LSI based on that. For Example:

• offerID: 4567
• SK: FOOD_OFFER#TIMESTAMP#[Date]
• all your attributes...
• food_offer_pk: FOOD_OFFER#4567
• timestamp: [date]

with a new GSI/LSI of food_offer_pk and timestamp as your PK/SK respectively, and query against FOOD_OFFER#4567 as pk and sk = between(date and date)

This often feels from an SQL prespective as an unnecessary duplication of data, but in dynamo and making use of index's this is super common way of doing it. If you find yourself however making a lot of index's, you will want to evaluate either your data schema, see if you can reduce a lot of those (and handle more of it in code) or switch from an dynamo to a more traditional SQL that can handle any number of search types (versus dynamo/nosql which really needs to know exactly what the queries are ahead of time)