How to write a single query to get multiple items from DynamoDb based on PK and SK?

Question

I'd like to know how I can get multiple items back from my dynamoDB if I know the PartitionKey (PK) and have a list of SortKeys (SK).

I've tried various permutations of OR... but it appears the OR command is not allowed in a KeyConditionExpression.

let params = {
    TableName: MY_TABLE_NAME
    KeyConditionExpression: `PK=:usr and (SK=:sid0 or SK=:sid1)`,
    ExpressionAttributeValues: {
        ":usr": "USR",
           ":sid0": '1234',
           ":sid1": '9101'
    }
}

I would then use these params thusly:

try {
    const users = []
    let data
    do {
        data = await dynamoClient.Query(params).promise()
        data.Items.forEach((user) => {
            users.push(user)
        })
        params.ExclusiveStartKey = data.LastEvaluatedKey
    } while (typeof data.LastEvaluatedKey !== "undefined")
    return users
} catch (err) {
    callback(new Error(`[500] ${err.message}`))
}

My DB structure has a lot of non-USR partitions in it, and won't have a ton of USRs (likely never more than 1k). It will be, however, too large to run a 'scan'.

My access patterns that matter are being able to quickly get "all users" as well as quickly get a specific user. What I have effectively looks like this

   PK    |   SK  
_________________
   USR   |  1234
   USR   |  5678
   USR   |  9101
 CAM_ID# |  ImageDate  (billions)
CAM_CONF |  CAM_ID (thousands)
 LOG_ID# |  ISODate (Millions)
 NOTIF#  |  ISODate (Millions)
 NOTIF#  |  Config (<1000)

I know I can make a single query with the PK and SK like is:

let params = {
        TableName: MY_TABLE_NAME,
        KeyConditionExpression: `PK=:usr and SK=:sid0`,
        ExpressionAttributeValues: {
            ":usr": "USR",
            ":sid0": '1234'
        }
    }

I could probably just loop through this and fire off a bunch of separate queries... but that seems inefficient.

And I can get all USR Items with this:

let params = {  
        TableName: MY_TABLE_NAME,
        KeyConditionExpression: `PK=:usr`,
        ExpressionAttributeValues: {
            ":usr": "USR",
        },
    }

But how would I go about getting user 1234 and user 9101 without also grabbing user 5678? Is there a reasonable way to pull more than one in a single query that isn't overly wasteful?

For example, I can duplicate the SK to a separate field (you can't filter on a sort key) and then apply a FilterExpression to that:

let params = {
    TableName: MY_TABLE_NAME
    FilterExpression = `sid=:sid0 or sid=:sid1`
    KeyConditionExpression: `PK=:usr`,
    ExpressionAttributeValues: {
        ":usr": "USR",
        ":sid0": '1234',
        ":sid1": '9101'
    }
}

However, that reads the entire partition key - gobbling RCUs and it's also limited to 1mb return BEFORE the filter happens. So while it might be okay for my 'user' search, it's not going to work when I've got millions or billion of items in a partition.

Answer 1

To get a list of items when you know the keys, you can use BatchGetItem which allows you to obtain up to 100 items in a single request. An example in Node would look like this:

async function batchGetItems() {
  const client = new DynamoDBClient({ region: "us-west-2" });
  const ddbDocClient = DynamoDBDocumentClient.from(client);
  try {
    return await ddbDocClient.send(
        new BatchGetCommand({
          RequestItems: {
            RetailDatabase: {
              Keys: [
                {
                  pk: "vikram.johnson@somewhere.com",
                  sk: "metadata",
                },
                {
                  pk: "jose.schneller@somewhere.com",
                  sk: "metadata",
                },
              ],
            },
          },
 
          ReturnConsumedCapacity: "TOTAL",
        })
    );
  } catch (err) {
    console.error(err);
  }
}

You can also use PartiQL ExecuteStatement API which allows you to pass in up to 50 keys.

SELECT * FROM mytable WHERE PK=123 AND SK IN ['a','b','c']