CODEWITHSUNDEEP
javascriptnode.jsamazon-web-servicesamazon-dynamodbdynamodb-queries
Joe Smith
Joe Smith

Reputation: 59

How can I query by sort key in AWS DynamoDB?

I have a database of users, and I'd like to return all users with the last name X. I currently have "last" as the sort key for my table. I'm running the following code, but getting a "Query condition missed key schema element" error.

var params = { TableName: 'Patients',
    KeyConditionExpression: '#id = :idNum',
    ExpressionAttributeNames: { 
       '#id': 'last' 
    },
    ExpressionAttributeValues: { 
      ':idNum': 'Test' 
    } 
};

docClient.query(params, function(err, data) {
    if (err) {
        console.error("Unable to query. Error:", JSON.stringify(err, null, 2));
    } else {
        console.log("Query succeeded.");
        res.json(data);
    }
});

Upvotes: 3

Views: 11272

Answers (2)

Jingyi Wang
Jingyi Wang

Reputation: 966

This is not the way DynamoDB/NoSQL should be used. You either create a GSI as thomasmichaelwallace suggested or have the users lastname as partition key too. You can add "ln_" before all of your last name partitionkeys to separte them from your other partition keys. If you had firstname as partitions before your primary key should look like that now:

partition | sort
fn_hans | ln_zimmer
fn_steve | ln_jobs
ln_zimmer | fn_hans
ln_jobs | fn_steve

Now you can query all partition which start with ln_. And yes you are supposed to have douplicate data in noSql. NoSQL Design has some articles about how to design a dynamoDB table.

Upvotes: 0

thomasmichaelwallace
thomasmichaelwallace

Reputation: 8482

To query you must provide a partition key, so with your table, as it is, your only option would be to do a Scan (which is expensive and almost never recommended).

However you can easily add Global Secondary Indexes to allow you to use another key as your partition.

In this case you can add a GSI with last as your partition key.

Then you would be able to Query the global secondary index (note the IndexName parameter) by last name.

If you want to go the scan route, however, you'll need to use:

docClient.scan({
  TableName: 'Patients',
  FilterExpression: '#l = :l',
  ExpressionAttributeNames: { '#l': 'last' },
  ExpressionAttributeValues: { ':l': 'Test' },
  Limit: 1
}, (err, data) => { /* callback */ })

Just remember that using scans can get expensive and put a strain on your table quickly (more detail here)

Upvotes: 1

Related Questions