4

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,
      },
    })
degr
  • 1,559
  • 1
  • 19
  • 37

3 Answers3

6

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.

  1. 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.

ch_g
  • 1,394
  • 8
  • 12
  • 1 - I need "like" or "starts with" condition, not "equal". Is it possible to do without "scan" operation? 2 - I type wrong params in my post, now it fixed – degr Jul 14 '20 at 09:00
  • As I said, you need to use another attribute as the index's partition key, sampling_date only can be used as sort key. This means you need to introduce a constraint in your searching. For example, if each search can be narrowed down to one particular year, then it makes sense to use "year" as partition key. If such constraint is not possible, you can simply use a dummy column populated with same value as partition key. – ch_g Jul 14 '20 at 23:27
3

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"
}
Harish KM
  • 1,303
  • 7
  • 17
  • 1
    Sorry man, I was too slow and did not check question in a time and system assign my +100 to another answer automatically. I solve my problem similar to your answer, I create additional fields, where I store only [year-month] and [year-month-day] (without time). But I was not able to do the trick with `sort key`. As you can see in my OP-post, I assign sort key, but "begin with" or ">" still did not work. – degr Jul 21 '20 at 11:16
1

Try

KeyConditionExpression: `begins_with(sampling_date, :sampling_date)`

See available condition expressions here... https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Condition.html

Mulhoon
  • 1,852
  • 21
  • 26
  • @degr are you sure `sampling_date` is the primary key and not `sampling_date_hash`? Your condition needs to include the primary key. – Mulhoon Jun 25 '20 at 15:19
  • sampling_date is global secondary index, primary key is ID, which is not used in query – degr Jun 25 '20 at 15:53