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.

notionquest · Answer 1 · 2016-10-25T13:03:09.073

20

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.

edited Oct 25 '16 at 13:03

answered Oct 24 '16 at 21:59

notionquest

37,595
6
111
105

say i have 1000 elements in username array so do i have to write in 1000 times ? – Avinash Gautam Oct 25 '16 at 06:08
I have updated the answer to dynamically form the filterexpression and expressionattributevalues. Take a look at it. – notionquest Oct 25 '16 at 13:03
thanks good example. Please can you give me example for pagination or limit with the query ? – Manish Nov 16 '18 at 06:33
https://stackoverflow.com/questions/56074919/dynamo-db-pagination?noredirect=1#comment98788697_56074919 – Nouman Dilshad May 10 '19 at 10:57
2

The dynamodb documentation states: The list can contain up to 100 values. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html – Mile Zajkovski Feb 18 '20 at 19:29

score 0 · Answer 2 · answered Mar 09 '23 at 19:55

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

how to use in operator in dynamo db

2 Answers2

Linked