How to use “IN” statement in FilterExpression using array - dynamodb
Solution 1
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
};
Solution 2
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
}
});
Solution 3
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
};
Solution 4
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" }, ...
}
Solution 5
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()
Nirmal Goswami
BY DAY : Javascript and PHP Developer BY NIGHT : Enjoying free time with family FOR FUN : Movies,TV,Mobile Game FAVORITE QUOTE : Impossible it self I M POSSIBLE
Updated on August 10, 2022Comments
-
Nirmal Goswami over 1 year
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 thisvar 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