Today I Learned

Limitations of Dynamodb KeyConditionExpression

Dynamodb has a query system with a particularly hard learning curve (in my opinion.) Even though I've been using it for years now, I still run into some sticky problems like this one today when I tried to use an IN operator in a query expression. Should be fine, right?

    const key = {
      IndexName: 'by_label_and_status',
      KeyConditionExpression:
        'labelId = :labelId and submissionStatus in (:sent, :opened, :listened)',
      ExpressionAttributeValues: {
        ':labelId': event.pathParameters.labelId,
        ':sent': 'sent',
        ':opened': 'opened',
        ':listened': 'listened',
      },
    };
    const result = await dynamodb.query('submissions', key);

The code above actually fails. Dynamodb is kind enough to report Invalid operator used in KeyConditionExpression: IN, but finding the reason why took me a bit of digging.

It turns out that indeed if you go to the guide for Dynamodb expression syntax, you'll see that the allowed comparators for KeyConditionExpression are limited.

Valid comparisons for the sort key condition are as follows:

sortKeyName = :sortkeyval - true if the sort key value is equal to :sortkeyval.

sortKeyName < :sortkeyval - true if the sort key value is less than :sortkeyval.

sortKeyName <= :sortkeyval - true if the sort key value is less than or equal to :sortkeyval.

sortKeyName > :sortkeyval - true if the sort key value is greater than :sortkeyval.

sortKeyName >= :sortkeyval - true if the sort key value is greater than or equal to :sortkeyval.

sortKeyName BETWEEN :sortkeyval1 AND :sortkeyval2 - true if the sort key value is greater than or equal to :sortkeyval1, and less than or equal to :sortkeyval2.

begins_with ( sortKeyName, :sortkeyval ) - true if the sort key value begins with a particular operand. (You cannot use this function with a sort key that is of type Number.) Note that the function name begins_with is case-sensitive.

Use the ExpressionAttributeValues parameter to replace tokens such as :partitionval and :sortval with actual values at runtime.

Since the system I'm working on is brand new, I was able to get the code example working by changing the values of our statuses. Instead of having completely disparate statuses for sent, clicked, and opened, I changed them to be sent, sent_clicked, and sent_opened. They are semantically correct, since clicked and opened states are also sent. That change allowed us to use begins_with instead of the prohibited IN operator.

    const key = {
      IndexName: 'by_label_and_status',
      KeyConditionExpression:
        'labelId = :labelId and begins_with(submissionStatus, :submissionStatus)',
      ExpressionAttributeValues: {
        ':labelId': event.pathParameters.labelId,
        ':submissionStatus': 'sent',
      },
    };

Purchase my Serverless book at https://leanpub.com/serverless