arraysamazon-dynamodbaws-clidynamodb-queries

Query in DynamoDB contains list of values in KeyConditionExpression


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


Solution

  • 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'