Dynamo DB BatchGetItem with Partition Key and Different sortKey begins_with

Question

Dynamo DB Java SDK batchGetItemRequest to support sort key starts_with/begins_with format also.

**Use Case**
SIngle Table design patter for a person is designed to store address, name, emails, phones, ids as separate entities which means PersonId is the sortKey and each address of that person will be added with different sort keys.The same way emails, phones and all the other person related entities are stored in table.

In one of the scenario, I need to pull person's all email and phone alone based on person Id. I will just have the person ID (PK) and email, Phone's sort key format alone not the exact sort key. so how to fetch selective entities based on PK and SK starts with?? I could see options to fetch completely based on PK but it will fetch entire data or giving exact sort key and retrieving it.

Answer

Your high level overview seems to suggest that you have denormalized your data too much, putting things across multiple items. In most cases a person entity should have a single item which contains their important information such as name, address, email etc... Splitting these small attributes into their own items only causes complexity, and under normal circumstances provides you no benefit (there are use-cases that would benefit, but rare).

`BatchGetItem` only supports full key equality, just like `GetItem`, as each request in the batch must only return a sinlge item, for that reason you cannot use begins with as multiple items could begin with a given prefix.

To overcome this hurdle you can use `ExecuteStatement` API where you can pass in the many sort key values, which becomes a BatchQuery style request:

```
SELECT * FROM "PersonTable" WHERE "PK"='Lee' AND (begins_with("SK", 'AGE#') OR begins_with("SK", 'EMAIL#'))
```

Answer

Hello,

Thankyou for using AWS DynamoDB !!

From the description I see that you have a use case where your want to retrieve items from your DynamoDB table using the batchGetItem having a single partition key and multiple sort keys and wants DynamoDB to support sort key starts_with/begins_with format.

We are glad and always happy to take such feedbacks. I would request you to reach out to us via AWS Support[1] (DynamoDB) so that we can raise a feature request with our service team on your behalf.

Also, I would like to inform you that you can create Global Secondary Index (GSI) [2] where person ID can be your Partition Key (PK) and Email/Phone can be your sort key (SK) based on your use case. Later, your can perform Query[3] operation to retrieve all the emails/Phone based on a Person ID (PK).

If you experience any difficulty in implementing the above solution, I would recommend you please reach out to AWS Support[1] (DynamoDB), along with your issue/use case in detail and share relevant AWS resource names. We will be more than happy to assist you.

Hope this helps!

---Reference---
[1] https://support.console.aws.amazon.com/
[2] Using Global Secondary Indexes in DynamoDB - https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html 
[3] Query - https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html

Dynamo DB BatchGetItem with Partition Key and Different sortKey begins_with

Relevant content