I'm having a tough time in DynamoDB with selecting a group of entries between two dates. My dates are strings and formatted simply as "2018-01-01".
Should I be using query or scan? Do my params look ok? The operation seems to work, but I get no results back. What am I doing wrong?
Here's my code:
// get transactions for {month} and {year}
api.get('/transaction/tab/{year}/{month}', (request) => {
const year = request.pathParams.year
const month = request.pathParams.month
const params = {
TableName: request.env.tableName,
KeyConditionExpression: '#date between :val1 and :val2',
ExpressionAttributeNames: {
'#date': 'date'
},
ExpressionAttributeValues: {
':val1': {
S: year +'-' + month + '-01'
},
':val2': {
S: year + '-' + month + '-' + getDaysInMonth(month, year)
}
}
}
console.log(params)
// post-process dynamo result before returning
dynamoDb.query(params, (err, data) => {
console.log(data)
if (err) {
console.error('Unable to query. Error:', JSON.stringify(err, null, 2))
return 'Unable to query. Error: '+ JSON.stringify(err, null, 2)
} else {
console.log('Query succeeded.')
data.Items.forEach((item) => {
console.log(' -', item.year + ': ' + item.title)
})
return data.Items
}
})
})
When you use KeyConditionExpression
expression that mean you have use Query
on a GSI
.
If date
is the partition key and not the sort key. Then, you have a problem:
You do not define IndexName
in your params.
In a Query operation, you cannot perform a comparison test (<, >, BETWEEN, ...) on the partition key. The condition must perform an equality test (=) on a single partition key value and, optionally, one of several comparison tests on a single sort key value.
For example:
KeyConditionExpression: 'HashKey = :hkey and RangeKey > :rkey'
Are you want to get transactions
of a month? I think, you have to create a GSI with: year-month
is PrimaryKey(ex: 2018-01, 2018-02), day-createdAt
is SortedKey (ex: 28-1535429111358, ...)
The query will be like above:
const params = {
TableName: request.env.tableName,
IndexName: 'MONTH-DAY-IDX',
KeyConditionExpression: 'year-month = :val1',
ExpressionAttributeValues: {
':val1': {
S: year +'-' + month
},
}
}