How can I query Dynamodb for different hash keys and secondaryindex?

Question

I am trying to do log table in dynamodb and my table looks like

Pid[HashKey] || TableName[SecondaryIndex] || CreateDate[RangeKey] || OldValue || NewValue

10 || Product || 10.10.2013 00:00:01 || Shoe || Skirt

10 || Product || 10.10.2013 00:00:02 || Skirt || Pant

11 || ProductCategory || 10.10.2013 00:00:01 || Shoes || Skirts

19 || ProductCategory || 10.10.2013 00:00:01 || Tables || Armchairs

Pid = My main db tables primary key

TableName = My main db table names

CreateDate = Row created date

now I want to get list of

where (Pid = 10 AND TableName = "Product") OR (Pid = 11 AND TableName="ProductCategory")

in a single request (it wouldn't be so short like this. It could include too many tables and pids)

I tried batchget but I didn't use it because I couldn't query with secondary index. It needs rangekey with equal operator.

I tried query but this time I couldn't send multiple hash key in a same query.

Any ideas or successions?

Thank you.

aaaristo aaaristo · Accepted Answer · 2014-01-24T14:12:26

The problem here is the OR .... Generally you cannot get this where condition with a single query operation without modifying your row,

Solution 1: You have to issue 2 query operations, and append them to the same resultset.

where (Pid = 10 AND TableName = "Product")

union

where (Pid = 11 AND TableName = "ProductCategory")

Those operations should run in parallel to optimize performance.

Solution2: create a field xxx that describe your condition and maintain it on writes, than you could create a global secondary index on it and perform a single query.

How can I query Dynamodb for different hash keys and secondaryindex?

1 Answers