how to use in operator in dynamo db

Question

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.

Answer 1

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.