CODEWITHSUNDEEP
sortingamazon-dynamodb
Yvonne
Yvonne

Reputation: 31

DynamoDB BETWEEN sort key with ranges

Newbie here. Is it possible to have a sort key that stores a numeric range, and if a given number were to then fall between that range, to then choose that sort-key?

As a practical example with the table below, say I know my partition key is "key#1", and then I were to be given a number of 5.25. From that example, I'd then expect to pull out the sort key where that number sat between the two given ranges on the one sort key. In this case, it'd be {"partitionkey": key#1, "sortkey": startrange#00.00#endrange#10.00} because 5.25 sits between the ranges of 00.00 to 10.00:

[
   {
      "partitionkey": key#1,
      "sortkey": startrange#00.00#endrange#10.00,
      "pricing": $25
   },
   {
      "partitionkey": key#1,
      "sortkey": startrange#10.01#endrange#20.00,
      "pricing": $30
   },
   {
      "partitionkey": key#1,
      "sortkey": startrange#20.01#endrange#25.00,
      "pricing": $35
   },
   {
      "partitionkey": key#1,
      "sortkey": startrange#25.01#endrange#30.00,
      "pricing": $40
   },
   {
      "partitionkey": key#2,
      "sortkey": startrange#00.00#endrange#05.00,
      "pricing": $20
   },
   {
      "partitionkey": key#2,
      "sortkey": startrange#05.01#endrange#20.00,
      "pricing": $25
   },
   {
      "partitionkey": key#2,
      "sortkey": startrange#20.01#endrange#30.00,
      "pricing": $30
   },
   ...
]

Upvotes: 1

Views: 584

Answers (1)

Seth Geoghegan
Seth Geoghegan

Reputation: 5747

Unfortunately, the way you have your sort key defined will not make this type of query possible.

In the past, I've handled this type of range query by choosing one of the values in the rage to be the sort key and the other value a regular attribute on the table. For example:

PartitionKey SortKey high
key#1 0 10
key#1 10.01 20
key#1 20.01 25
key#1 25.01 30

I would then execute a query operation, using the sort key to filter on the low end of the range and a filter expression to handle the upper bound.

Ideally, you'll want to arrange your data so you can do most of your filtering by the sort key since you pay to read filtered items.

Upvotes: 1

Related Questions