AWS, dynamodb startwith condiftion gsi

Question

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,
      },
    })

Answer 1

I think there are two issues in your current code -

1. In KeyConditionExpression, there must be an equality condition on a single partition key value. In your case, it must include "sampling_date = :sampling_date".

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.

2. I am not sure which language you use. I suspect your syntax for ExpressionAttributeValues is not correct.

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.

Answer 2

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.

Source: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html#Query.KeyConditionExpressions

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"
}