CODEWITHSUNDEEP
node.jsamazon-web-servicesdynamodb-queries
ani
ani

Reputation: 516

Best way to retrieve an item from dynamoDB using attribute which is not partition key

I am new to dynamoDB and need some suggestion from experienced people here . There is a table created with below model

orderId - PartitionKey
stockId
orderDetails

and there is a new requirement to fetch all the orderIds which includes particular stockId. The item in the table looks like

{
   "orderId":"ord_12234",
   "stockId":[
      123221,
      234556,
      123231
   ],
   "orderDetails":{
      "createdDate":"",
      "dateOfDel":""
   }
}

provided the scenario that stockId can be an array of id it cant be made as GSI .Performing scan would be heavy as the table has large number of records and keeps growing . what would be the best option here , How the existing table can be modified to achieve this in efficient way

Upvotes: 0

Views: 118

Answers (1)

jarmod
jarmod

Reputation: 78783

You definitely want to avoid scanning the table. One option is to modify your schema to a Single Table Design where you have order items and order/stock items.

For example:

pk sk orderDetails stockId ...
order#ord_12234 order#ord_12234 {createdDate:xxx, dateOfDel:yyy} ...
order#ord_12234 stock#123221 23221 ...
order#ord_12234 stock#234556 234556 ...
order#ord_12234 stock#123231 123231 ...

You can then issue the following queries, as needed:

  1. get the order details with a query on pk=order#ord_12234, sk=order#ord_12234
  2. get the stocks for a given order with a query on pk=order#ord_12234, sk=stock#
  3. get everything associated with the order with a query on pk=order#ord_12234

Upvotes: 1

Related Questions