My DynamoDB table setup has the a Hash Key (PK) and a SortKey (SK1). I currently store a user ID and various sort keys relating to user information. For example,
| PK | SK1 |
|---|---|
| User#1 | DeviceToken#1234 |
| User#1 | CourseEnrolledDate#2022-01-20 |
| User#1 | CourseEnrolledDate#2022-01-19 |
I now need to know which courses a user has enrolled in after 2022-01-19, so I issue a query with PK=User#1 and KeyCondition= SK1 > CourseEnrolledDate#2022-01-19.
The result filters the CourseEnrolled correctly except that it also includes the DeviceToken#1234 item which should not be the case since the DeviceToken item does not have a date field to filter from. What am I doing wrong and how do you suggest I model my use case?
CodePudding user response：
Instead of the > operator, apply the BETWEEN operator to SK1 in the query's key condition expression:
AND SK1 BETWEEN CourseEnrolledDate#2022-01-20 AND CourseEnrolledDate#9
Where a BETWEEN b AND c returns true if a is greater than or equal to b, and less than or equal to c.