Reputation: 2029
Is there any way of supplying multiple values for a DynamoDB table's Sort Key whilst doing a query in Boto3?
For a single SK value to search on, I'm doing this:
table.query(
IndexName="my_gsi",
KeyConditionExpression=Key('my_gsi_pk').eq({pk value}) & Key('my_gsi_sk').eq({sk value}),
FilterExpression={filter expression}
)
... which works.
However, my scenario involves searching on one of a couple of potential SK values, so I'd like to, in SQL terms, do something like this:
WHERE my_gsi_pk = {pk value}
AND my_gsi_sk IN ({sk value 1}, {sk value 2})
I've looked in the Boto3 documentation in the .query() section and concentrated upon the KeyConditionExpression
syntax but can't identify whether this is possible or not.
Upvotes: 2
Views: 2207
Reputation: 21
Please note that the PartiQL will spend much more RCU than the Query. You can check this by requesting ReturnConsumedCapacity = ReturnConsumedCapacity.TOTAL
Upvotes: 2
Reputation: 25809
The query
API does not support the IN
operator in the KeyConditionExpression
.
Use the execute_statement API instead. This executes a PartiQL statement, which does accept the IN
operator in query operations for the Partition and Sort keys:
sk = ["Foo", "Bar"]
res = client.execute_statement(
Statement=f'SELECT * FROM "my_table"."my_gsi" WHERE my_gsi_pk = ? AND my_gsi_sk IN [{",".join(["?" for k in sk])}]',
Parameters= [{"S": "1"}] + [{"S": k} for k in sk]
)
This creates a PartiQL Statement like SELECT * FROM "my_table"."my_gsi" WHERE my_gsi_pk = ? AND my_gsi_sk IN [?, ?]
and substitution Parameters like [{"S": "1"}, {"S": "Foo"}, {"S": "Bar"}]
.
Upvotes: 3