DynamoDB equivalent of SQL's key or attribute 'not in("value1", "value2"...)'

Question

I am trying to run some dynamoDB operations with AWS.DynamoDB.DocumentClient from aws-sdk module but I am unable to find an easy solution to select items where an attribute is not equals to an array of values.

e.g attribute <> ["value1", "value2]

This is equivalent to a simple typical SQL operation in the form of:

select * from sometable where attribute not in("value1", "value2"...);

After trying out different ScanFilter and QueryFilter following the documentation here, it seems that the AttributeValueList for NE and NOT_CONTAINS does not accept multiple values.

I wish to arrive at the results as shown below without having to define multiple 'AND' queries

I have since arrived at this solution but it seems clumsy and I would have to write logic to create the filter condition string and ExpressionAttributeValues as the filter condition is dynamic.

FilterExpression: 'answer <> :answer1 AND answer <> :answer2',
        ExpressionAttributeValues : {
            ':answer1' : "test1",
            ':answer2' : "test2"
        }

I have therefore, 2 questions:

Is there a better way of doing this?
Is there a length limit to the string of KeyConditionExpression? I am very sure there is but I cannot seem to find information with regards to this.

Shay Ashkenazi Shay Ashkenazi · Accepted Answer · 2020-10-28T08:46:07

There is no other way to achieve what you need. If all these values have something in common, and you know it in the write time, you can insert them with some kind of prefix and create GSI where they will be a sort key. In this case you'll be able to query them by prefix in the key condition expression. Otherwise, what you've suggested is your only option.
4KB for all of the expressions combined. As described in the Expression Parameters:

Expression parameters include ProjectionExpression, ConditionExpression, UpdateExpression, and FilterExpression.

The maximum length of any expression string is 4 KB. For example, the size of the ConditionExpression a=b is 3 bytes.

DynamoDB equivalent of SQL's key or attribute 'not in("value1", "value2"...)'

1 Answers