CODEWITHSUNDEEP
node.jsamazon-dynamodb
Avinash Gautam
Avinash Gautam

Reputation: 101

how to use in operator in dynamo db

I have a user table with a field username. I need to write something equivalent to this in dynamo db: Select * from user where username in('a','b','c');

Adding more from code prosepective i have usernames in an array say var arr=['a','b','c'];

I so far tried this which is giving me zero result

    this.dynamo.client.scanAsync({
        TableName: this.dynamo.table('users'),
        FilterExpression: 'username IN (:list)',
        ExpressionAttributeValues: {
            ':list': arr.toString()
        }
    }).then((response) => {
        console.log(response);
        return {
            userFriends: result.Item.friends
        };
    });

When I pass one element in array it give me result searching passed single element in user table but its not working with more than one element in array.

Upvotes: 10

Views: 17025

Answers (2)

João Vitor Miranda
João Vitor Miranda

Reputation: 1

I made some improvements in @notionquest answer

var searchKey = "title";
var searchPossibleValues = ["The Big New Movie 2012", "The Big New Movie"];

var titleObject = searchPossibleValues.reduce((state, term, idx) => {
  state[":titlevalue" + idx] = term;
  return state;
}, {});

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

It's a little bit more useful 😃

Upvotes: 0

notionquest
notionquest

Reputation: 39166

The individual users should be given as comma separated String variables. JavaScript array is equivalent to List in AWS DynamoDB data type. The DynamoDB can't compare the String data type in database with List attribute (i.e. Array in JavaScript).

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

Construct the object from array for FilterExpression:-

Please refer the below code for forming the object dynamically based on Array value.

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
};

Note:-

I don't think IN clause with 1000s of usernames is a good idea in terms of performance.

Upvotes: 21

Related Questions