Does `KeyConditionExpression` in DynamoDB support `IN` operator?

Question

Reading the concept of "KeyConditionExpression" https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-KeyConditionExpression

I'm curious about if it's possible to do a IN comparison on the "KeyConditionExpression".

The SQL counterpart would be:

SELECT * FROM table WHERE primary_key IN ("a","b","c")

or should I use the BatchGetItem?

If both are possible, what's the difference? and which one should I use?

Answer 1

My response to this is: mostly no, but sometimes yes - I'll elaborate.

A primary key in DynamoDB can actually be two things, because there's two kinds of keys:

• The partition key, which is mandatory and identifies which data partition the item is stored on and
• the optional sort key, which you can enable on a table when you create it and it's used to sort the items within a given partition key.

If there is only a partition key, that is the primary key of your item. On tables that have a sort key, the (composite) primary key is the combination of the partition and sort key.

For tables where primary key = partition key, you can't use the Query API to build something like that, because you need to specify the partition key exactly and can do optional conditions on the sort key (which is not present). In this case the BatchGetItem API can be used to perform a selection equivalent to your SQL code.

Tables with a composite primary key may lend themselves to more to something like this if you design them appropriately. Query allows you to do some filtering based on the sort key, but a real IN query isn't supported. Through cleverly designing the key schema you may be able to do something with the begins_with or between clause or a regular comparison with a limit, but that's very use case specific. The BatchGetItem will work in this case as well though, so that's the better choice.

Oh and this question has some useful answers outlining some more of the differences and use cases.