Query condition missed key schema element : Validation Error

Question

I am trying to query dynamodb using the following code:

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

let dynamo = new AWS.DynamoDB.DocumentClient({
  service: new AWS.DynamoDB(
    {
      apiVersion: "2012-08-10",
      region: "us-east-1"
    }),
  convertEmptyValues: true
});

dynamo.query({
  TableName: "Jobs",
  KeyConditionExpression: 'sstatus = :st',
  ExpressionAttributeValues: {
    ':st': 'processing'
  }
}, (err, resp) => {
  console.log(err, resp);
});

When I run this, I get an error saying:

ValidationException: Query condition missed key schema element: id

I do not understand this. I have defined id as the partition key for the jobs table and need to find all the jobs that are in processing status.

Answer 1

You're trying to run a query using a condition that does not include the primary key. This is how queries work in DynamoDB. You would need to do a scan for the info in your case, however, I don't think that is the best option.

I think you want to set up a global secondary index and use that to query for the processing status.

Answer 2

In another answer @smcstewart responded to this question. But he provides a link instead of commenting why this error occurs. I want to add a brief comment hoping it will save your time.

AWS docs on Querying a Table states that you can do WHERE condition queries (e.g. SQL query SELECT * FROM Music WHERE Artist='No One You Know') in the DynamoDB way, but with one important caveat:

You MUST specify an EQUALITY condition for the PARTITION key, and you can optionally provide another condition for the SORT key.

Meaning you can only use key attributes with Query. Doing it in any other way would mean that DynamoDB would run a full scan for you which is NOT efficient - less efficient than using Global secondary indexes.

So if you need to query on non-key attributes using Query is usually NOT an option - best option is using Global Secondary Indexes as suggested by @smcstewart.

I found this guide to be useful to create a Global secondary index manually.

If you need to add it using CloudFormation here is a relevant page.