DynamoDB table query KeyConditionExpression error

Question

How do I fix the below error and why does "operand type: M" show up? I have read aws docs and searched on stack overflow, but still couldn't solve this problem after a few hours. I want to query an item that is active and has a dateSold between the start and end date in iso format. I am interpreting the error "operand type: M" to mean that I am using the map type in my operands: dateSold, :start, and :end. However, all the operands are just strings.

The SQL command would have looked something like: SELECT dateSold, profit FROM Items WHERE isActive = 'true' AND dateSold > start AND dateSold < end

Error: "message":"Invalid KeyConditionExpression: Incorrect operand type for operator or function; operator or function: BETWEEN, operand type: M","code":"ValidationException"

Code:

AWS.config.update({ region: "us-east-2" });
    const documentClient = AWS.DynamoDB.DocumentClient({ apiVersion: "2012-08-10" });

    const options = {
        TableName: "Items",
        ProjectionExpression: "dateSold, profit",
        IndexName: "isActive-dateSold-index",
        KeyConditionExpression:
          "isActive = :isActive AND dateSold BETWEEN :start AND :end",
        ExpressionAttributeValues: {
          ":start": { S: start.toISOString() },
          ":end": { S: end.toISOString() },
          ":isActive": { S: "true" },
        },
      }

    const result = await documentClient
      .query(options)
      .promise()

Schema:

Table: {
    AttributeDefinitions: [
    { AttributeName: "dateSold", AttributeType: "S" },
    { AttributeName: "id", AttributeType: "S" },
    { AttributeName: "isActive", AttributeType: "S" },
    ],
    TableName: "Items",
    KeySchema: [{ AttributeName: "id", KeyType: "HASH" }],
    GlobalSecondaryIndexes: [
    {
        IndexName: "isActive-dateSold-index",
        KeySchema: [
        { AttributeName: "isActive", KeyType: "HASH" },
        { AttributeName: "dateSold", KeyType: "RANGE" },
        ],
    },
    ],
},

Nadav Har'El · Accepted Answer · 2020-06-01 19:56:00Z

2

You have:

ExpressionAttributeValues: {
          ":start": { S: start.toISOString() },
          ":end": { S: end.toISOString() },
          ":isActive": { S: "true" },
        },

Some DynamoDB SDKs do the type encoding for you automatically. You don't need to write { S: start.toISOString() }, it may be enough to just write start.toIsoString(), without putting it in a map. I don't know if the SDK you're using is one of those. The Python one (boto3) does tis, for example.

By the way, how does S instead of "S" (a quoted string) in your code even work?

answered Jun 1, 2020 at 19:56

Nadav Har'El

14.1k2 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Cherin Over a year ago

Just writing start.toISOString() worked. It looks like { S: start.toISOString() } was interpreted as a map. The typescript typing has a bug in it. I actually had to cast the options variable to be the type "any" otherwise my code wouldn't compile. const result = await documentClient .query(options as any) .promise() S instead of "S" is just a typescript syntax thing. It seems like it the prefered default because my prettier and eslint linters are removing the quotes around the S.

Collectives™ on Stack Overflow

DynamoDB table query KeyConditionExpression error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related