How to use “IN” statement in FilterExpression using array - dynamodb

Question

Checked AWS document but did not find any working example.

Here is my attempt

var params = {
            TableName: "User",
            IndexName:"a-b-index",
            KeyConditionExpression: "Country = :country and #s = :status",
            FilterExpression: "Id IN (:e)",
            ExpressionAttributeValues: {
              ":country ": "USA",
              ":status": 1,
              ":e": "1"

            },
            ExpressionAttributeNames: {"#s": "Status"}
          };

          //get users
          dynamodb.query(params, function (err, data) {
            if (err)
              //error
            else {
              //success

            }
          });

Got records but it is fetching record which have id 1 but i want to use array like this

 var params = {
        TableName: "User",
        IndexName:"a-b-index",
        KeyConditionExpression: "Country = :country and #s = :status",
        FilterExpression: "Id IN (:e)",
        ExpressionAttributeValues: {
          ":country ": "USA",
          ":status": 1,
          ":e": ["1","2","3"]

        },
        ExpressionAttributeNames: {"#s": "Status"}
      };

      //get users
      dynamodb.query(params, function (err, data) {
        if (err)
          //error
        else {
          //success

        }
       });

How can make above code as working.want to get records. syntax is correct and query run without error but i am not getting records

@NiroshanRanapathi i am using serverless framework.checking locally — Nirmal Goswami, Oct 27 '16 at 11:46
@NiroshanRanapathi i am not using dynamodb in localhost.i am using live dynamodb — Nirmal Goswami, Oct 27 '16 at 11:55
try to use dynamo db in local host http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DynamoDBLocal.html when dynamodb with serverless try to use https://www.npmjs.com/package/serverless-dynamodb-local it is easiest for offline development — Niroshan Ranapathi, Oct 27 '16 at 12:00
@NiroshanRanapathi ok but i don't think it will solve my issue — Nirmal Goswami, Oct 27 '16 at 12:04
@NiroshanRanapathi syntax is correct but i am not getting records — Nirmal Goswami, Oct 27 '16 at 12:16

score 64 · Accepted Answer · edited May 23 '17 at 11:47

64

Please refer this answer

Summary:-

For fixed number of values in "IN" clause:-

var params = {
    TableName : "Users",
    FilterExpression : "username IN (:user1, :user2)",
    ExpressionAttributeValues : {
        ":user1" : "john",
        ":user2" : "mike"
    }
};

For more elements in array and forming the FilterExpression dynamically:-

var titleValues = ["The Big New Movie 2012", "The Big New Movie"];
var titleObject = {};
var index = 0;
titleValues.forEach(function(value) {
    index++;
    var titleKey = ":titlevalue"+index;
    titleObject[titleKey.toString()] = value;
});

var params = {
    TableName : "Movies",
    FilterExpression : "title IN ("+Object.keys(titleObject).toString()+ ")",
    ExpressionAttributeValues : titleObject
};

edited May 23 '17 at 11:47

Community

1
1

answered Oct 27 '16 at 21:43

notionquest

37,595
6
111
105

thanks for answer,will try it,dynamodb does not support join and i want to fetch records based on other table records so i want to use in operator because there is no any other option but you are telling that it is not good for 1000 user in terms of performance than which is best approach ? – Nirmal Goswami Oct 28 '16 at 05:00
In that case, you need to export or stream it to EMR or something and retrieve the required information. Most of the NOSQL databases doesn't support join. Other option is to review the data model to store all required data in one table if the size is less than 4KB. Please note that most of the answers in SO are not written specifically for the requirement. Sometimes they are just pointers to resolve the problem. – notionquest Oct 28 '16 at 08:39
8

This is not correct:"KeyConditions" or "KeyConditionExpression" is required field – Jay May 11 '18 at 22:56
1

Thank you its working. Only missing -> titleObject[titleKey.toString()] = {S: value} – Tigin Dec 20 '18 at 12:46
5

No need to keep an index variable, the second param in the forEach callback is the index of the current value: `titleValues.foreach(function (value, index) { ...` – Purefan Jul 15 '19 at 08:12
`KeyConditions` or `KeyConditionExpression` is required for `query` but not for `scan`. – jarmod Jan 13 '20 at 19:26
"ValidationException: Invalid FilterExpression: Expression size has exceeded the maximum allowed size; expression size: 6324" :-( – kotlinski Jun 15 '20 at 14:33

score 8 · Answer 2 · edited May 23 '17 at 12:34

notionquest's answer is correct but i cant use my other values in ExpressionAttributeValues like :country and :status so here is modified answer to make it working as per my requirements

var AttributeValuesObject = {};

  AttributeValuesObject[':country '] = "USA";
  AttributeValuesObject[':status'] = 1;

  var titleValues = ["1", "2"];
  var titleObject = {}; 

  var index = 0; 

  titleValues.forEach(function(value) {
    index++;
    var titleKey = ":titleValue"+index;
    AttributeValuesObject[titleKey.toString()] = value;
    titleObject[titleKey.toString()] = value;
  });

  var params = {
    TableName: "User",
    IndexName:"a-b-index",
    KeyConditionExpression: "Country = :country and #s = :status",
    FilterExpression: "Id IN ("+Object.keys(titleObject).toString()+ ")",
    ExpressionAttributeValues: AttributeValuesObject,
    ExpressionAttributeNames: {"#s": "Status"}
  };

  //get users
  dynamodb.query(params, function (err, data) {
    if (err)
      //error
    else {
      //success

    }
   });

Could you please accept the answer if the concept was helpful though it was not written specifically for your use case? — notionquest, Oct 28 '16 at 08:34

score 8 · Answer 3 · answered Apr 21 '19 at 03:51

I've done it like this, parsing the params list once

const users = [{userId:1, name:'joe'}, {userId:2, name:'mike'}]
const expressionAttributeValues = {};
const userIdParams = users.map((u, i) => {
  const userParam = `:user${i}`;
  expressionAttributeValues[userParam] = u.userId;
  return userParam;
}).join(',')
var params = {
    TableName : 'Users',
    FilterExpression : `username IN (${userIdParams})`,
    ExpressionAttributeValues : expressionAttributeValues
};

grnc · Answer 4 · 2020-07-03T10:47:56.817

The Python3 version:

def get_filter_for_in_clause(column_name, in_values, column_type="S"):
    expressions = []
    for i in range(len(in_values)):
        expressions.append(f':temp{i}')
    filter_expression = f'{column_name} IN ({", ".join(expressions)})'

    expression_attribute_values = {}
    for index, expression in enumerate(expressions):
        expression_attribute_values[expression] = {column_type: in_values[index]}

    return filter_expression, expression_attribute_values

Usage:

column = 'testcolumn'
inputs = ['testvalue1', 'testvalue2', 'testvalue3']
filter_expression, expression_attribute_values = get_filter_for_in_clause(column, inputs)

Result:

# filter_expression = 'testcolumn IN (:temp0, :temp1, :temp2)'
# expression_attribute_values = {':temp0': {'S': 'testvalue1'}, ':temp1': {'S': 'testvalue2'}, ':temp2': {'S': 'testvalue3'}}

score 2 · Answer 5 · answered Nov 22 '18 at 02:16

You could also use notionquest's approach and simply insert your other attribute values inside the titleObject{}. e.g.

var titleObject = { ": country": "USA", ":status": 1, };

Note: You might have to add schema like "S" if you're not using DocumentClient

     var titleObject = { 
      ":country": { "S":"USA" }, ...

}

score 1 · Answer 6 · answered Aug 11 '18 at 12:43

1

Using es5 and map:

const params = {
  TableName: 'personalizations',
  KeyConditionExpression: 'accountId = :accountId',
  FilterExpression: `websiteId IN (${websites.map(w => `:${w.websiteId}`).join(',')})`,
  ExpressionAttributeValues: {':accountId': accountId}
}
for (const website of websites) {
  params.ExpressionAttributeValues[`:${website.websiteId}`] = website.websiteId
}

const {Items: personalizations} = await docClient.query(params).promise()

answered Aug 11 '18 at 12:43

Marcel Panse

572
1
6
13

Potential issue here if you had duplicate websiteId's (would cause dup keys). @kords answer avoids this by using .map index for key names. – Seba Illingworth Sep 02 '19 at 22:19

How to use “IN” statement in FilterExpression using array - dynamodb

6 Answers6

Linked