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

score 10 · Answer 1 · edited Jun 20 '20 at 09:12

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

It's probably related to your schema and how you have your indexes set up. There's a good solution here: https://stackoverflow.com/a/32216137/9307595 — Sam Rastovich, May 09 '19 at 04:51
So is it that attributes in the KeyConditionExpression must be either the Table's PrimaryKey or an Index like GSI? But in my case the Table structure has total 3 columns, of which 1 is PK, and 2 are GSIs, still, the query fails, what am I doing wrong, what do I change? — Dev1ce, May 09 '19 at 05:23

score 7 · Accepted Answer · answered May 09 '19 at 07:20

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

worked! the KeyConditionExpression works on the GSI and SortKey attributes in the query. — Dev1ce, May 09 '19 at 07:31

score 2 · Answer 3 · edited Aug 09 '22 at 07:13

There are 2 different ways to interact with dynamoDb, you can either use: AWS.DynamoDB (to perform some custom type wrapping, eg wrap numbers as strings) or AWS.DynamoDB.DocumentClient which does the type wrapping for you - the latter is more simple to use for most cases.

You're calling DocumentClient, but wrapping with types as if you're calling AWS.DynamoDB directly which will fail

Just using your user_id index (I am assuming it's just a sort key and doesn't require a range key also), this should work:

  let params = {
     TableName: table,
     IndexName : "user_id-index",
     KeyConditionExpression: "user_id = :userId",
     ExpressionAttributeValues: { ":userId": 1234 }
  }

How to query AWS DynamoDb using KeyConditionExpression?

3 Answers3