CODEWITHSUNDEEP
node.jsamazon-dynamodb
pizzarob
pizzarob

Reputation: 12029

Query Multiple Partition Keys At Same Time DynamoDB - Node

I have an array of Job ID's.

[ '01', '02', '03', '04' ]

Currently I am looping through the array and executing a Query for each item in the array to get the job details.

Is there a way to use a single query to get all the jobs whose Partition Key is in the array ?

Upvotes: 22

Views: 27248

Answers (3)

Gurudeepak
Gurudeepak

Reputation: 431

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();

Upvotes: 8

akerra
akerra

Reputation: 1200

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.

Upvotes: 12

notionquest
notionquest

Reputation: 39186

You can use Batch Get Item API to get multiple items from DynamoDB table based on key attributes (I.e. partition key and sort key if available).

Upvotes: 10

Related Questions