How to query DynamoDB with multiple Indexes?

Question

I have a table in DynamoDB and I have a primary key and two global shared indexes. For example, the table structure is as follows.

// Primary Keys
id -> PK
name -> SK

// Global Shared Index 1
status_one -> S1PK
status_one_time -> S1SK

// Global Shared Index 2
status_two -> S2PK
status_two_time -> S2SK

So what I need is I need to know how to use multiple keys in withKeyConditionExpression.

I will need to filter data by the following scenarios,

1. S1PK = :v1 and SK = :v4 and S2PK = :v2 and S2SK <= :v3
2. S2PK = :v1 and S2SK >= :v2 and S1SK <= :v2

So how can I do that? If I put the above queries into withKeyConditionExpression it will throw errors. So is there a way to query the table with Primary Keys and Secondary Indexes all at once? What I am doing wrong here? I really appreciate it if anybody can help me. Thanks in advance.

Answer 1

You can't. DynamoDB doesn't work like that.

A query can only access the table or a single index.

You could use Scan(), but realize that's going to read every record in the table (and you'll be charged for that) and simply throw away the ones that don't match. Great way to use up your provisioned capacity.

Also, DDB will only read 1MB at a time, so you'll likely need to call it in a loop.

If this is a common access pattern, you'll need to rethink your keys. Or rethink the use of DDB (by itself). A common pattern is to have the data duplicated to Elastic Search for better search functionality.