I need to execute query like this: select * from table where sampling_date like "2020-05-%"
To do this, I'm calling for
db.query({
TableName: "Tubes",
Select: "ALL_ATTRIBUTES",
IndexName: "sampling_date_idx",
KeyConditionExpression: " sampling_date > :sampling_date ",
ExpressionAttributeValues:{ ':sampling_date': {'S': '2020-05-'}}
}, function(error: AWSError, data: QueryOutput){
console.log(error, data);
})
And I get this error message:
{"errorType":"Error","errorMessage":"{\"message\":\"Query key condition not supported\",\"code\":\"ValidationException\",
My table:
this.tubes = new dynamodb.Table(this, "tubes", {
tableName: "Tubes",
billingMode: dynamodb.BillingMode.PAY_PER_REQUEST,
partitionKey: {
name: "id",
type: dynamodb.AttributeType.STRING
},
pointInTimeRecovery: true,
removalPolicy: cdk.RemovalPolicy.RETAIN
});
this.tubes.addGlobalSecondaryIndex({
indexName: "sampling_date_idx",
sortKey: {
name: 'sampling_date_srt',
type: AttributeType.STRING
},
partitionKey: {
name: "sampling_date",
type: AttributeType.STRING,
},
})
I think there are two issues in your current code -
Please read "KeyConditionExpression" section in - https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html
In short, you only can perform equality test against partition key.
The syntax given in AWS doc is -
"ExpressionAttributeValues": {
"string" : {
"B": blob,
"BOOL": boolean,
"BS": [ blob ],
"L": [
"AttributeValue"
],
"M": {
"string" : "AttributeValue"
},
"N": "string",
"NS": [ "string" ],
"NULL": boolean,
"S": "string",
"SS": [ "string" ]
}
}
In your case, it may be something like -
"ExpressionAttributeValues": {
":sampling_date": {"S": "2020-05-01"}
}
My experience is in C#, it may be something like -
ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
{
{ ":sampling_date", new AttributeValue{S = "2005-05-01"} }
}
To solve your problem, you may need to use another attribute as the index's partition key. sampling_date only can be used as sort key.
sampling_date is the partition key for your GSI sampling_date_idx.
DynamoDB documentation says that in key condition expressions:
You must specify the partition key name and value as an equality condition.
So sampling_date can only be used with the "equal to" comparison operator. None of the other operators like less than, greater than, between, contains, begins with, etc. can be used with sampling_date.
However, these operators can be used with a sort key!
So if you can redesign your table and/or indexes such that sampling_date becomes a sort key of some index, you can use begins_with on it.
Here's a suggestion:
Create a GSI with partition key = sampling_year & sort key = sampling_date.
Then if your table has the following items:
{
"id": "id1",
"sampling_year": 2020,
"sampling_date": "2020-04-01"
}
{
"id": "id2",
"sampling_year": 2020,
"sampling_date": "2020-05-01"
}
{
"id": "id3",
"sampling_year": 2020,
"sampling_date": "2020-06-01"
}
And you use the following Node.js code:
let AWS = require("aws-sdk")
let dc = new AWS.DynamoDB.DocumentClient()
dc.query({
TableName: "Tubes",
IndexName: "sampling_year-sampling_date-index",
KeyConditions: {
"sampling_year": {
ComparisonOperator: "EQ",
AttributeValueList: [2020]
},
"sampling_date": {
ComparisonOperator: "BEGINS_WITH",
AttributeValueList: ["2020-05-"]
}
}
}
You'll get your desired output:
{
"id": "id2",
"sampling_year": 2020,
"sampling_date": "2020-05-01"
}
Try
KeyConditionExpression: `begins_with(sampling_date, :sampling_date)`
See available condition expressions here... https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Condition.html