The IN operator is provided with too many operands; number of operands: 119 dynamodb

Question

Try to use IN operation in dynamodb but get following error. Could anyone help me with alternative solution ?

var params = {

TableName : "table_name",
FilterExpression : "id IN ("+Object.keys(profileIdObject).toString()+ ")",
ExpressionAttributeValues : profileIdObject

};

ERROR :: {

  "message": "Invalid FilterExpression: The IN operator is provided with too many operands; number of operands: 119",
  "code": "ValidationException",
  "time": "2018-02-13T08:48:02.597Z",
  "statusCode": 400,
  "retryable": false,
  "retryDelay": 25.08276239472692

}

Stimulate

Answer 1

According to docs:

The maximum number of operands for the IN comparator is 100

Found here: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Limits.html#limits-expression-parameters

You will need to perform the query/scan in multiple batches, in your case with 100 of Object.keys(profileIdObject).toString() in the first batch and 19 in the second batch. Then coalesce the results.

Brook

if you're searching specific items its okay but how about to avoid items ? like not Id IN(.....) how can you handle it for the next batch query ? because if I do the same for the next one, it will return the items that I wanted to avoid in the previous query, do you got it ? – Tracy

Answer 2

According to dynamodb documentation, the maximum number of operands for the IN comparator is 100

So you can split into many operations like :

FilterExpression : "id IN (1,2,3, ....) OR id IN (101,102,103,...) ..."

Using this function :

let getFilterExp = function (x) {
    let arr = []
    let currentIndex = 0
    let counter = 0
    let max = 99

    arr[currentIndex] = {}

    for (let y in x) {
        if (counter < max) {
            arr[currentIndex][y] = x[y]
            counter++
        }
        else {
            currentIndex++
            arr[currentIndex] = {}
            arr[currentIndex][y] = x[y]
            counter = 0
        }
    }

    let exp = ''
    for (let i = 0; i < arr.length; i++) {
        if (i == 0) {
            exp += "id IN (" + Object.keys(arr[i]).toString() + ")"
        }
        else {
            exp += " OR id IN (" + Object.keys(arr[i]).toString() + ") "
        }
    }

    return exp
}

Where x is the profileIdObject in your case

let filterExp = getFilterExp(profileIdObject )

Durrace