CODEWITHSUNDEEP
node.jsamazon-dynamodb
Joseph Paterson
Joseph Paterson

Reputation: 1493

DynamoDB FilterExpression with NOT IN

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!

Upvotes: 9

Views: 14111

Answers (3)

Mohan Dere
Mohan Dere

Reputation: 4597

Here is working example code to exclude some of the items by id. NOT IN scan query - My table have date_time, id, message columns.

async fetchAllItemsByTimeRange(
  startTime: string,
  endTime: string,
  itemsExclude: string[] = [],
) {
  const expressionAttributeNames = {
    '#date_time': 'date_time',
  };
  const expressionAttributeValues = {
    ':startTime': {
      S: startTime,
    },
    ':endDateTime': {
      S: endTime,
    },
  };
  let filterExpression = '#date_time BETWEEN :startTime AND :endTime';
  if (itemsExclude.length) {
    expressionAttributeNames['#id'] = 'id';

    const tnxIdStrArr: string[] = [];

    itemsExclude.forEach((tnxId) => {
      expressionAttributeValues[`item${tnxId}`] = {
        N: tnxId.toString(),
      };
      tnxIdStrArr.push(`:item${tnxId}`);
    });
    filterExpression += ` AND NOT(#id IN (${tnxIdStrArr.join(',')}))`;
  }

  const failedTransactionsParams = {
    ExpressionAttributeNames: expressionAttributeNames,
    ExpressionAttributeValues: expressionAttributeValues,
    FilterExpression: filterExpression,
    TableName: PaybackBasePointFailedTransactionsTuple.TABLE_NAME,
    ProjectionExpression: 'id, date_time, message',
  };

  return await this.dynamoDb
    .scan(failedTransactionsParams)
    .promise();
}

Upvotes: 0

Xeltor
Xeltor

Reputation: 4654

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

Upvotes: 3

notionquest
notionquest

Reputation: 39186

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'

Upvotes: 6

Related Questions