Multiple Comparison Conditions on Sort Key for DynamoDB?

Question

I am using AWS SDK2 DynamoDB for an application with the following schema:

personId    startDate    endDate     name    age  job
1           10/2/2013    10/3/2020   Bob     12   SWE
1           8/2/2013     10/3/2021   Bob     12   EE
2           10/2/2013    10/3/2021   Joe     17   Student
3           11/2/2013    10/3/2022   Kim     16   Boss

My goal is to be able to query the table by a personId and a date in order to retrieve a person object. Currently, I am thinking about having a partition key on personId; however, I am not sure how to design my sortKey.

For example, say I want a person with personId = 1 and date = 10/5/2019, I would expect the first and second entry of the example table to be returned because the date is between the startDate and endDate. How can I design the sortKey so that I can use an appropriate key condition expression to say something like date between startDate and endDate? I know that filter expression can be used, but I was wondering if there was a way to design the sort key so that filter is not needed as it is more costly.

user_2933177 · Answer

No you can't.

Why do you need a sort key anyway? If personId is unique, you don't need a composite primary key (hash + sort).

And since you know the personId when reading, you can just use GetItem(table, HK=:personId)

Edit

Given the additional information that personId is not unique...

Have startDate be your sort key...

You'll be able to Query(table, HK=:personId, SK <= :date, ScanIndexForward=false, limit=1)

This will return the single record with the most recent start date less than or equal to the date you passed in.

The SQL equivalent would look like

select *
from table
where personId = :personId 
      and startDate <= :date
order by startDate desc
fetch first row only

EDIT2
Didn't see the updated info in the question. If a person can have overlapping jobs, then you'll need to leave off the limit and do the filtering (either in DDB or in your client app)

DDB isn't the best for everything, with data as you've laid out, you've basically got two choices, pay more for DDB RCUs or pay more for an RDB.

Having said that, I'll point out that a DDB RCU covers up to 4KB of data.

If your DDB rows are smaller, you can return multiple rows with a single Query() call costing only 1 RCU.

If you're row size is 512b, you could return 8 rows for a single RCU.

Multiple Comparison Conditions on Sort Key for DynamoDB?

1 Answers