Query in DynamoDB contains list of values in KeyConditionExpression

Question

I'm trying to do a SINGLE query in an AWS DynamoDB giving for multiple values and the scenario is something like that. I want to query by giving a list of values. If the value exists in db(at a specific key, let's say 'mandatoryField'), then give me that value, this means at the end of filtering, I want get back only the values that were existing from my array input.

So, for these items in my db:

column: mandatoryField
        ---------------
1.      value1
2.      value2
3.      value3
4.      value4

And giving in my query command an array of ["value2", "value3334", "value7", "value1"], I want to receive back just the values that exists: ["value2", "value1"].

Tried this query in aws-cli, both with IN and CONTAINS func

aws dynamodb query \
--table-name testTable \
--key-condition-expression '#mandatoryField IN :values' \
--expression-attribute-names '{ "#mandatoryField": "mandatoryField" }' \
--expression-attribute-values '{
    ":values": { "SS": ["value2", "value3334", "value7", "value1"]}
}'

but received as I expected An error occurred (ValidationException) when calling the Query operation: Invalid operator used in KeyConditionExpression: IN

Hope this is enough descriptive to achieve my goal by giving a QUERY

Answer 1

Solution 1: (RECOMMENDED)

Use statement with WHERE mandatoryField IN using the AWS DynamoDB execute-statement command as follows:

aws dynamodb execute-statement \
--statement 'SELECT * FROM testTable WHERE mandatoryField IN 
    ["value2", "value3334", "value7", "value1"]'

Solution 2:

Use --filter-expression instead of --key-condition-expression using the AWS DynamoDB scan command as follows:

aws dynamodb scan \
--table-name testTable \
--filter-expression 'contains(:values, mandatoryField)' \
--expression-attribute-values '{
    ":values": { "SS": ["value2", "value3334", "value7", "value1"]}
}' \
--projection-expression 'mandatoryField'