0
263
ViewsDynamodb query with GSI and filter expression on non partition key and sort key columns in nodejs
I have a GSI on my table and am using that GSI for querying results. Am using a filter expression as well
const active_cases = await storesMonthlyAudit();
console.info("actives cases : ", active_cases)
async function storesMonthlyAudit() {
const params = {
TableName: "jms-case-management-dev",
IndexName: "entity-sKey-index",
ProjectionExpression: "storeId,caseId",
KeyConditionExpression: "#entity = :entity",
FilterExpression: "#status = :status",
ExpressionAttributeNames: {
"#entity": "entity",
"#status": "status",
},
ExpressionAttributeValues: {
":entity": "Case",
":status": "Active"
}
};
const cases = await Query(params);
return cases
}
Response :
actives cases : {
Items: [],
Count: 0,
ScannedCount: 8012,
LastEvaluatedKey: { entity: 'Case', sKey: 'C#10134066', pKey: 'ST#1013' }
}
But when I tried the same thing from AWS console, I get the correct result of 2 records with status = "Active"
2 answers
Answer question0
DynamoDB will only access 1MB of data for each query request. This data access is counted before any filters or projection expressions. It appears you have more than 1 MB of data stored under your specified partition key. You need to make multiple requests to access all of the data.
The console used the LastEvaluatedKey from the response to make additional queries. You can see it consumed 559 RCUs when running your query, which means it made 5 requests in total for you to find these two values.
You likewise need to make multiple queries. You do this by setting the ExclusiveStartKey on subsequent requests, setting this value as the LastEvaluatedKey from the previous request. When LastEvaluatedKey is not returned from a request, you know that you have searched through all items that match your query.
0
I read about LastEvaluatedKey and ExclusiveStartKey and wrote a small function
async function dbRead(params) {
let result = await db.query(params).promise();
let data = result.Items;
if (result.LastEvaluatedKey) {
params.ExclusiveStartKey = result.LastEvaluatedKey;
data = data.concat(await dbRead(params));
}
return data;
}