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

user_8891224 · Answer

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?

DynamoDB table query KeyConditionExpression error

1 Answers