Query Multiple Partition Keys At Same Time DynamoDB - Node

There are a few options, each with some pros/cons:

1. BatchGetItem as @notionquest pointed out. This can fetch up to 100 items or 16MB of data in a single call, but you need to provide all of the key values (both partition and sort for each item, if your table uses a composite key schema).

2. TransactGetItems - this can retrieve up to 25 items in a single call and as the name implies, is transactional, so the entire operation will fail if there is another pending operation on any of the items being queries. This can be good or bad depending on your use case. Similar to BatchGetItem, you need to provide all key attributes.

3. Query, which you are already using. Query has high performance but only supports 1 key per request (partition key required, sort key optional). Will return up to 1MB of data at a time, and supports paginated results.

If your jobs table has a composite key (partition + sort), Query is the best option in terms of performance and no constraint on specifying the sort key values. If the table only has a partition key, then BatchGetItems is probably the best bet (assuming the each job item is relatively small in size, and you expect less than 100 total jobs to be returned). If either of those assumptions is incorrect, multiple Querys would be the best option.

You can use partiQL for this use case:

SELECT * 
FROM <TABLE_NAME> 
WHERE "Id" IN [ARRAY]

But do note that partiQL has length constraints: Minimum length of 1. Maximum length of 8192.

let statement = {
    "Statement": "SELECT * \nFROM <TABLE_NAME> \nWHERE \"Id\" IN [ARRAY]"
}

let result = await dynamoDbClient.executeStatement(statement).promise();