CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodb
Pranjal Choladhara
Pranjal Choladhara

Reputation: 915

DynamoDbException: Conditions can be of length 1 or 2 only

I am from SQL background and very new to Dynamodb. I have a single table like this:

-----------------------------------------------------------
 | dbId | genId | colData | deviceId | updateOn | params |
-----------------------------------------------------------
 |      |       |         |          |          |        |
 ----------------------------------------------------------

Here dbId is primary key and genId is sort key. I have created two local secondary indexs in deviceId and updateOn. In SQL I can query from the table:

String dbId  = "151/2020-21";
int deviceId = 1001;
long updOn   = 1608456211308;
String query = "select * from tableName where dbId = '"+dbId+"' and deviceId != "+deviceId+" and updateOn > " + updOn;

In DynamoDb my keyConditionExpression is : dbId = :dbId and updateOn > :updateOn and deviceId != :deviceId. It gives me error :

DynamoDbException: Invalid KeyConditionExpression: Syntax error; token: "!", near: "deviceId !="

I removed the '!' to this : dbId = :dbId and updateOn > :updateOn and deviceId = :deviceId. It gives me error:

DynamoDbException: Conditions can be of length 1 or 2 only

How can I perform my desired query in Dynamodb? How should I design my Dynamodb table (I mean, primary key, indexes etc) so that I get the same sql like result?

Upvotes: 11

Views: 10686

Answers (2)

Willian
Willian

Reputation: 3405

When it comes to query() in dynamoDb, it is recommended to specify only the partition key and sort key in KeyConditionExpression.

So if you want to perform the query() with more conditions, you should consider FilterExpression. E.g:

const params = {
    TableName: 'table-name',
    KeyConditionExpression: 'pk = :pk AND sk = :sk',
    FilterExpression: 'dbId = :dbId and updateOn > :updateOn and deviceId != :deviceId',
    ExpressionAttributeValues: {
      ':pk': '12345',
      ':sk': 'lorem-ipsum',
      ':dbId': '0987654321',
      ':updateOn': '2022-06-25',
      ':deviceId': '123',
    },
  }

  const docClient = new AWS.DynamoDB.DocumentClient();
  const result = await docClient.query(params).promise();

Upvotes: 17

Abdul Moeez
Abdul Moeez

Reputation: 1401

Now, reason you are facing error 'Conditions can be of length 1 or 2 only' is because you are specifying 3 conditions within KeyConditionExpression. Please specify only partition key and sort key.

Lastly, you don't need to create a LSI for this purpose. You can perform same operation on original table or need to add Global Secondary Index, Which can help you to achieve the query on two conditions only.

As mentioned, the attribute included in "KeyConditionExpression" should be your hash key only, matching your base table schema. If you want to query on all Then this not possible because there will be no sort key in any case as recommended by AWS Doc's.

Upvotes: 10

Related Questions