DynamoDB FilterExpression with NOT IN

Question

I'm trying to run a filter expression on a DynamoDB query (in a Node.JS environment), for one of the attributes not to be in a list of those attributes. I'm doing this:

documentClient.query( {
    TableName: 'event',
    IndexName: 'OrganisationTimestamp',
    KeyConditionExpression: '#ts BETWEEN :from AND :to',
    ExpressionAttributeNames: {
      '#ts': 'Timestamp'
    },
    ExpressionAttributeValues: {
        ':to': to,
        ':from': from,
        ':ignoredUserIds': "1, 2, 3"
    },
    FilterExpression: 'not (userId in (:ignoredUserIds))'
  })

However I'm not having any luck here, and getting items back with the userId attribute within that range.

Any help much appreciated, thanks!

Answer 1

IN - can be used only if userId attribute is defined as DynamoDB LIST data type

IN : Checks for matching elements in a list.

AttributeValueList can contain one or more AttributeValue elements of type String, Number, or Binary. These attributes are compared against an existing attribute of an item. If any elements of the input are equal to the item attribute, the expression evaluates to true.

Solution:

Change the FilterExpression and ExpressionAttributeValues as mentioned below. It should work.

ExpressionAttributeValues: {
        ':to': to,
        ':from': from,
        ':userid1': "1",
        ':userid2': "2",
        ':userid3': "3"        
    },
FilterExpression: 'userId <> :userid1 and userId <> :userid2 and userId <> :userid3'

Answer 2

You're using ignoredUserIds as if it was a Macro... you should do:

ExpressionAttributeValues: {
    ':to': to,
    ':from': from,
    ':id1': 1,
    ':id2': 2,
    ':id3': 3,
},
FilterExpression: 'not (userId in (:id1, :id2, id3))'

if you're only mapping numbers you should be able to do:

FilterExpression: 'not (userId in (1, 2, 3))'