I want to query a dynamo db table without a Primary partition key Primary sort key

Those are in my table

Primary partition key   userid (String)
Primary sort key    jobcodeid (String)

This table also contain a key jobid.

So i want to make a query which looks like this.

  var opts  = {
          'ConsistentRead': true,
          TableName : 'interviews',
          KeyConditionExpression: "jobid = :jobid",
          ExpressionAttributeValues: { ":jobid": {"S":jobid} },
        }

I am using this code

  dynamodb.query(opts, function(err, data) {
  if(err){
   //somthing
   }else{
   //Somthing
   }
 }

But the above query returning an error like this.

ValidationException: Query condition missed key schema element

How can I execute this query?

I want to query a dynamo db table without a Primary partition key Primary sort key

Those are in my table

Primary partition key   userid (String)
Primary sort key    jobcodeid (String)

This table also contain a key jobid.

So i want to make a query which looks like this.

  var opts  = {
          'ConsistentRead': true,
          TableName : 'interviews',
          KeyConditionExpression: "jobid = :jobid",
          ExpressionAttributeValues: { ":jobid": {"S":jobid} },
        }

I am using this code

  dynamodb.query(opts, function(err, data) {
  if(err){
   //somthing
   }else{
   //Somthing
   }
 }

But the above query returning an error like this.

ValidationException: Query condition missed key schema element

How can I execute this query?

• javascript
• node.js
• amazon-web-services
• amazon-dynamodb

• 1 What you want to do is scan the table: docs.aws.amazon./amazondynamodb/latest/APIReference/…. Querying a DynamoDB table must always be done using the primary key. But be careful with the scan operation since it might be very expensive depending on your table size. It seems that your table layout doesn't quite fit your queries so I suggest you think about a different layout that lets you efficiently query jobs by ID. – Makkes Commented Oct 31, 2018 at 7:54
• @Makkes Can you give me an example with the scan in node js – Arun VM Commented Oct 31, 2018 at 8:06
• 1 No. You will find a vast amount of documentation using Google. Start here: docs.aws.amazon./amazondynamodb/latest/developerguide/… Also, I urge you to get to know the basics of DynamoDB, especially with regards to proper table layout, query performance etc. – Makkes Commented Oct 31, 2018 at 8:08
• 1 Hi thank you for your help i have solved the problem. Best, This helped me stackoverflow./questions/44589967/… Amazon is very good in documentation :) – Arun VM Commented Oct 31, 2018 at 9:22
• Be very aware of the cost and latency associated with scan. You should probably use a Global Secondary Index, then you can query efficiently. – jarmod Commented Jul 25, 2022 at 14:11

2 Answers 2

DynamoDB has two distinct operations: Scan and Query. Scan scans the entire table, whereas Query can efficiently narrow down on one specific partition key (it can still return a huge number of items, because there can be a huge number of items with the same partition key).

Because you don't have a specific partition key you want to scan (jobid is your sort key, not your partition key), you'll need to use the Scan operation and read the entire table.

This also means that the operation will be very expensive: A Scan reads, and charges you for reading, every single item in your database. Even if you pass a FilterExpression and eliminate most of the items as non interesting, DynamoDB still reads each one and filters it - and therefore charges you for it.

One of the reasons why this scan will be expensive is that the Scan operation does not support the KeyConditionExpression option that a Query has which could have, in some cases, reduced the cost of this scan: Your query involves looking for a specific sort key (jobid = :jobid) in each partition. If partitions are each fairly long, DynamoDB could have looked up jobid = :jobid in each partition without reading the entire partition - and not charge you for reading the entire partition.

Finally, a general piece of advice: The key to designing applications on DynamoDB is to first consider the types of queries you have, and then design the data layout to make these queries efficient. In your case, it seems the data model is inappropriate for this type of queries - so if they are mon, you should probably rethink your data layout. For example making jobid a partition key, or adding a GSI for jobid, or other changes depending on what your data actually has and what other queries you need to do on it.

I know a little late to the races, but will add this anyways.

You'd want to create a Global Secondary Index. This will allow you to do queries based off of any item attribute.

Scans do not scale well, and as your table grows...it will greatly increase your back end's fetch time as it goes through every record.

GSI's create a secondary 'lookup table' which operates much faster. The link below will get you started.

https://docs.aws.amazon./amazondynamodb/latest/developerguide/GSI.html