CODEWITHSUNDEEP
node.jsamazon-dynamodbdynamodb-queries
Aniket Prajapati
Aniket Prajapati

Reputation: 403

DynamoDB query without knowing the primary key

I have a table with 3 attributes

ARTICLE | CLAPS | STAMP
 -----  | ----- | ----
 RUST   |   1   | 2 OCT 2020 19:20
 C++    |   3   | 10 OCT 2020 12:30

I want to do these 3 queries on the table :

  1. Check if the article name is taken
  2. The article with highest number of claps
  3. The most recent article

What is the most efficient way to query 2 and 3 without using a scan operation?

Upvotes: 0

Views: 407

Answers (2)

Aniket Prajapati
Aniket Prajapati

Reputation: 403

After going through @Charles answer. I am taking the following approach.

TABLE

  POST | TYPE | CLAPS | STAMP
 ----- | ---- | ----- | ----
 RUST  |  A   |   1   | 1594185483964
 C++   |  A   |   3   | 1594185984082

Here TYPE A denotes an article.

I have made 2 GSIs.

  1. Partition key Type, Sort Key CLAPS ; Index : TYPE-CLPAS-POSTS
  2. Partition key Type, Sort Key STAMP ; Index : TYPE-STAMP-POSTS

I think this will increase flexibility while allowing me to do valid queries.

  1. To find the article with the highest number of claps.
{
  TableName: 'WB360-POSTS',
  IndexName: 'TYPE-CLAPS-POSTS',
  KeyConditionExpression: '#T = :T',
  ScanIndexForward: false,
  Limit: 10,
  ExpressionAttributeNames: {
   '#T': 'TYPE'
  },
  ExpressionAttributeValues: {
   ':T': 'A'
  },
  ProjectionExpression: 'POST'
}
  1. To find the most recent article.
{
  TableName: 'WB360-POSTS',
  IndexName: 'TYPE-STAMP-POSTS',
  KeyConditionExpression: '#T = :T',
  ScanIndexForward: false,
  Limit: 10,
  ExpressionAttributeNames: {
   '#T': 'TYPE'
  },
  ExpressionAttributeValues: {
   ':T': 'A'
  },
  ProjectionExpression: 'POST'
}

Upvotes: 0

Charles
Charles

Reputation: 23793

What is the most efficient way to query 2 and 3 without using a scan operation?

Create Global Secondary Indexes (GSI)

Hash key would be either the string "CLAPS" or the string "STAMP", sort key would be the corresponding CLAPS value or the STAMP value.

When you Query(), specify '"ScanIndexForward=false"and"Limit=1"` to get the most highest (most recent) value.

Note you need to store the time stamp in a sortable format, "2020-10-10-12:30" not "10 OCT 2020 12:30"

Upvotes: 2

Related Questions