How can I query by sort key in AWS DynamoDB?

Question

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);
    }
});

thomasmichaelwallace · Answer 1 · 2018-11-26T09:21:51.713

1

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)

edited Nov 26 '18 at 09:21

answered Nov 12 '18 at 09:08

thomasmichaelwallace

7,926
1
27
33

Thanks for the response! If I decided to go the route of doing a scan instead, how would I modify my code? – Joe Smith Nov 12 '18 at 19:01
No worries- I've added an example- although you really should avoid frequent scans on a dynamo db table if you possibly can. – thomasmichaelwallace Nov 13 '18 at 08:24
Awesome, thank you. I'll take a look at the global index option as well. – Joe Smith Nov 14 '18 at 01:51
Good luck! Don't forget to mark the question answered! – thomasmichaelwallace Nov 14 '18 at 07:25
1

FYI, a GSI does not require the primary key to be unique, so you don't need to provide a sort key for a GSI. (Though it never hurts to add one.) https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html – Matthew Pope Nov 26 '18 at 00:11
I actually did not know that- had just always assumed- cheers for the heads up @MatthewPope – thomasmichaelwallace Nov 26 '18 at 09:22
@thomasmichaelwallace what if my data is in a large amount and has more frequent operations, still would you suggest me to go with GSI? as I need projection with ALL attributes and I read on docs below -------If you need to access most of the non-key attributes on a frequent basis, you can project these attributes—or even the entire base table— into a global secondary index. This gives you maximum flexibility. However, your storage cost would increase, or even double. – Bhaumik Thakkar Mar 25 '20 at 12:34

score 0 · Answer 2 · answered Jun 01 '19 at 16:54

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.

How can I query by sort key in AWS DynamoDB?

2 Answers2