CODEWITHSUNDEEP
javascriptamazon-web-servicesamazon-dynamodb
Ankit
Ankit

Reputation: 67

Filter value in DynamoDB Table using Latest TimeStamp & EmailID

I want to filter the user with the emailId and get the recent row entry i.e. get the latest data of the user on basis of email ID to be displayed. In my case subscriptionID is the partition Key. Don't have secondary index nor sort key. Is there to do this? or any other approach I should go for?

My Dynamo Query :-

 var docClient = new AWS.DynamoDB.DocumentClient({ region: AWS.config.region });
 var params = {
  TableName: 'License',
  FilterExpression: "customerEmail = :email",       
  ExpressionAttributeValues: {
            ':email': email
        },
  };
  docClient.scan(params, function(err, data) {
      if (err) 
      {
      console.log(err);
      }
      else 
        {
         console.log('length ',data.Items.length);
         console.log(data.Items);
         
        
        }
    });

enter image description here enter image description here

Upvotes: 0

Views: 2193

Answers (1)

Seth Geoghegan
Seth Geoghegan

Reputation: 5747

A scan is the most expensive way to get data from DynamoDB, and should be avoided in most cases.

You are currently using subscriptionID as your partition key, which does not help you fetch an item by customer email. The most efficient way to do that is to have a Primary Key with the users email as the Partition Key and a timestamp (purchase date) as the Sort Key. If you store your data in this way, you can do the following:

query(
      TableName= 'License',
      KeyConditionExpression= '#pk = :pk',
      ExpressionAttributeNames={
          '#pk': 'subscriptionID'
      },
      ExpressionAttributeValues={
          ':pk': { 'S': '[email protected]' }
      },
      ScanIndexForward=False,
      Limit=1
)

A few things to notice about this operation:

  1. It is a query operation, which is much more efficient than a scan operation.
  2. The ScanIndexForward=False will sort using the sort key in descending order (set to true or omit for ascending order).
  3. Limit=1 gets you the top result (e.g. latest entry)

Of course, your data is currently not stored in a way that supports this query. In order to do that, you have two options:

  1. Change the way you store the data to support this access pattern. This may not be an option if doing so would break other access patterns.
  2. Introduce a secondary index that uses the user email as the Partition Key and the purchase date as the Sort Key.

To get the most out of DynamoDB, you have to design your table(s) and how you store your data to support your applications access patterns. As you can see, your current design does not easily support your desired access pattern. There are many ways to design your table to support this type of query, but this pattern is common in many applications.

Upvotes: 4

Related Questions