How to query AWS DynamoDb using KeyConditionExpression?

Question

I have an AWS DynamoDb table,
I have user_id as an Index or GSI(user_id-index ),
And I have product_type also as an Index or GSI(prod_type-index ).

I am trying to use KeyConditionExpression to query the DynamoDb table,
But I am getting a -

Validation Exception, with message:"Query key condition not supported" and statusCode: 400
ValidationException: Query key condition not supported\n at Request.extractError 

I have the following Item structure on the table -

{
  "id": "12345f9f-f08c-45ae-986a-f1b5ac712345",
  "user_id": 1234,
  "prod_type": "OTHER"
}

Following is my NodeJs code for Querying the table -

let AWS = require('aws-sdk');

AWS.config.update({
    region: 'us-east-1'
});

let connection = new AWS.DynamoDB.DocumentClient();

let table = "some_table";

let params = {
    IndexName : "user_id-index",
    ExpressionAttributeValues: {
        ":v1": {
            N: 1234
        },
        ":v2": {
            S: "OTHER"
        }
    },
    ExpressionAttributeNames: {
        "#userId": "user_id",
        "#prodType": "prod_type"
    },
    TableName: table,
    KeyConditionExpression: "#userId = :v1 and #prodType = :v2"
};

connection.query(params, function(err, data) {
    if (err) {
        console.log(err);
    } else {
        console.log(data);
    }
});

References -
Dynamodb query error - Query key condition not supported
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LegacyConditionalParameters.KeyConditions.html

Answer 1

As per the documentation here, The issue is that your ExpressionAttributeValues value is incorrect. You need to provide a mapping of the variable and the data type with the value. You need to provide a mapping like so:

let params = {
   IndexName : "user_id-index",
   ExpressionAttributeValues: {
      ":v1": {
           N: 1234
       },
       ":v2": {
           S: "OTHER"
       }
   },
   TableName: table,
   KeyConditionExpression: "user_id = :v1 and prod_type = :v2"
};

You need to specify the data type as per the documentation. S is for string literals, N is for numbers, etc. You can find the details in the documentation above. I would also highly recommend you use ExpressionAttributeNames as well. I have found that it works better and it is best practices with this SDK. You need to substitute the variables you specify in the mapping in the KeyConditionExpression like so:

let params = {
   IndexName : "user_id-index",
   ExpressionAttributeValues: {
       ":v1": {
           N: 1234
       },
       ":v2": {
           S: "OTHER"
       }
   },
   ExpressionAttributeNames: {
       "#userId": "user_id",
       "#prodType": "prod_type"
   }
   TableName: table,
   KeyConditionExpression: "#userId = :v1 and #prodType = :v2"
};

Reference -
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LegacyConditionalParameters.KeyConditions.html

Answer 2

As I mentioned in the previous answer. You can not put the hash key of one GSI/Primary and the hash key of another GSI/Primary on a single KeyConditionExpression

The condition must perform an equality test on a single partition key value.

The condition can optionally perform one of several comparison tests on a single sort key value. This allows Query to retrieve one item with a given partition key value and sort key value, or several items that have the same partition key value but different sort key values.

from the docs

It is not supported by DynamoDB and it is actually to save your money. What you can do here is to use the more specific GSI hash key as the KeyConditionExpression then you can do FilterExpression on the result set

Otherwise, Set up an GSI that have one of the property as the Hash Key and the other as Range Key. That way you can query using the syntax

partitionKeyName = :partitionkeyval AND sortKeyName = :sortkeyval

Remember that partitionKeyName only support equality. sortKeyName support multiple different operation