DynamoDB - fetching items by multiple keys

Question

started using DynamoDB recently and I am having problems fetching data by multiple keys.

I am trying to get multiple items from a table.

My table schema is defined as follows:

{
  "AttributeDefinitions": [
    {
      "AttributeName": "id",
      "AttributeType": "S"
    },
    {
      "AttributeName": "date",
      "AttributeType": "S"
    }
  ],
  "KeySchema": [
    {
      "AttributeName": "id",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "date",
      "KeyType": "RANGE"
    }
  ],
  ...
}

I have a filter list of ids and a date range for each id:

[
    { "id": "abc", "start_date": "24/03/2020", "end_date": "26/03/2020" },
    { "id": "def", "start_date": "10/04/2020", "end_date": "20/04/2020" },
    { "id": "ghi", "start_date": "11/04/2020", "end_date": "11/04/2020" }
]

I need to fetch all items that match the filter list.

The problem is that I cannot use Query as KeyConditionExpression only accepts a single partition key (and I need to match it to the entire filter list)

The condition must perform an equality test on a single partition key value.

I cannot use BatchGetItem as it requires the exact key (and I need a date range for my sort key Key('date').between(start_date, end_date))

Keys - An array of primary key attribute values that define specific items in the table. For each primary key, you must provide all of the key attributes. For example, with a simple primary key, you only need to provide the partition key value. For a composite key, you must provide both the partition key value and the sort key value.

I am kind of lost... Is there a way to fetch by multiple keys with a range query (by a single request - not multiple requests from a loop)?

Would you suggest any table changes?

Answer 1

You need to make one query per unique id. Each of these queries should include a key condition expression that has equality on the id partition key and range of values on the date sort key, like this:

#id = :id AND #date BETWEEN :startdate AND :enddate

Don't use scan for this. As your table grows, performance will decline.