7

Let's say I have my DynamoDB table like this, with Order ID as the primary key. :

table screenshot

The Order ID increments by one, everytime I add/put a new item.

Now, I have one number, let's say 1000, and my user wants to get all the items which have Order ID > 1000. So the items returned would be 1001, 1002, 1003, and so on till the last one.

My requirement is as simple as it seems - but is this thing possible to do with Query method of AWS DynamoDB?

Any help is appreciated :) Thanks!

Community
  • 1
  • 1
kartik
  • 550
  • 1
  • 6
  • 19

2 Answers2

8

There's currently no way to filter on partition key, but I can suggest a way that you can achieve what you want.

You're heading in the right direction with Query which has a "greater than" operator. However, it only operates on the sort key attribute.

With Query, you essentially choose a single partition key, and provide a filter expression that is applied to the sort key of items within that partition.
Since your partition key is currently "Order ID?", you'll need to add a Global Secondary Index to query the way you want.

Without knowing more about your access patterns, I'd suggest you add a Global Secondary Index using "From" as the partition key, which I assume is the user ID. You can then use "Order ID" as the sort key.

my user wants to get all the items which have Order ID > 1000.

With the GSI in place, you can achieve this by doing a query for items where "User ID" is userId and "Order ID" > orderId.

You can find more on query here, details on adding a GSI here, and more info on choosing a partition key here.

Scott Decker
  • 4,229
  • 7
  • 24
  • 39
Zac Charles
  • 1,208
  • 14
  • 19
  • Thanks @Zac! So, all summed up, I need to re-name the `From` to `User ID`, and make it the Primary Key. Also, make `Order ID` the secondary key - So my `Query` would be like - `If User ID = XYZ & Order ID > 1000`. Am I correct here? Thanks! – kartik May 30 '20 at 02:57
  • Whether the attribute is named From or Order ID doesn't make any difference, so that's up to you. You can change the schema of your base table like you described. But you need to think about your access patterns; how you'll use the table. If you make `From`/`User ID` the partition key, then you wouldn't be able to "get order by Order ID" without also knowing the user ID. Maybe that's okay in your situation. Global Secondary Indexes are there in case you need to group/partition the data in multiple ways to support more access patterns. – Zac Charles May 30 '20 at 19:03
  • But yes, changing your table like that would work. If you can't change it, a GSI would work. – Zac Charles May 30 '20 at 19:04
1

No, because Query expects an exact key, and does not allow an expression for the partition key (it does however for the sort key).

What you could use however is a Scan with a FilterExpressions (see Filter Expressions for Scan and Condition Expressions for the syntax). This reads all records and filters afterwards, so it is not the most effective way.

Christian
  • 558
  • 2
  • 13
  • Thanks! As you said : _**'Query expects an exact key, and does not allow an expression for the partition key'**_ - But what if I provide an exact number to query like '1000'? Wouldn't it work? Thanks! – kartik May 29 '20 at 17:08
  • 1
    @kartik Yes, technically that should work. How reasonable that is depends on your use-case. – Christian May 29 '20 at 17:24
  • Thanks for the reply! Can you suggest me the condition I need to give the Query for this? Thanks! – kartik May 30 '20 at 02:19
  • Instead of Query, it's probably better to use getItem (for one request per item) or batchGetItem (for up too 100 or 16MB. Syntax for getItem: { "TableName": "YourTableName", "Key": { "OrderID": { "N": 1000 } } } Syntax for BatchGetItem: { "RequestItems": { "YourTableName": { "Keys": [ { "OrderID":{"N":1000} }, { "OrderID":{"N":"1001"} }, { "OrderID":{"N":"1002"} } ], } } } – Christian May 30 '20 at 07:00
  • Hi @christian! The `BatchGetItem` only allows upto 100 items - mine could go up to 100,000. So, any way I could get hold of this? Thanks! – kartik May 30 '20 at 08:30
  • Technically, you would need to send 1000 requests, each requesting the next 100 ids (or unprocessed ids from previous requests). But if you are just looking for specific records and don't actually want to retrieve 100,000 records, this seems like a very bad design to me. If it is possible, you really should try to redesign your tables/keys for your query-requirement as suggested by Zac Charles. – Christian May 30 '20 at 08:46
  • Thanks @christian! :) re-designing the Table - that's the only and the best option for me for now. Thanks! – kartik May 30 '20 at 08:53