How to make use of Conditional expression of Pynamodb for graphQl?

Question

I am using PynamoDB for querying DynamoDB tables. Its a basic table with user_id as PK and name as SK. Also I have created a LSI with user_id as PK and unit as SK.

To make a query based on user_id and name we can write the query in following way.

result = userModel.query(user_id, name)
print(result)

The above code is working fine. Now lets say I need to query the table based on user_id and unit(multiple value), we have conditional expressions in pynamodb. I am trying to use "is_in" method. Below is the query. Here "unit_index" is the LSI and "unit" is the attribute name.

result = userModel.unit_index.query(
    user_id,
    userModel.unit.is_in(1,2)
)

The above query is not working. I am getting a following error.

Failed to query items: An error occurred (ValidationException) on request (525267c5-b4fb-46a3-a8a9-008af5b234a6) on table (user_table) when calling the Query operation: Invalid operator used in KeyConditionExpression: IN

Could you let me know what's the best possible to write a query without using for loop?

Answer 1

It's not possible to use IN operator on the sort key using a Query.

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-KeyConditionExpression

You would either need to do a single query for each value in the list, or use ExecuteStatement which allows you to provide up to 50 values using IN operator.

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_ExecuteStatement.html