Boto3: querying DynamoDB with multiple sort key values

Question

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.

Answer 1

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"}].