CODEWITHSUNDEEP
amazon-dynamodbdynamodb-queries
luan hdwebsoft
luan hdwebsoft

Reputation: 35

DynamoDB how to make a query that have compare 2 field?

Query Pattern:

Get all posts where upvotes > downvotes



Schema

Post = new Schema({
  id: {
    type: String,
    hashKey: true
  },
  upvote: {
    type: Number,
  },
  downvote: {
    type: Number,
  }
});





How to achieve this query pattern?

Upvotes: 1

Views: 1284

Answers (1)

swayamraina
swayamraina

Reputation: 3158

DynamoDB splits all the data according to the partition key (PK) i.e. your data is divided into multiple servers for storage.
So to retrieve the data you need to atleast pass the partition key.

I believe your use-case is get all posts where upvotes > downvotes

Since this is a global query and not related to any partition or specific entry in the dynamodb table, You need to use a secondary index (sparse index via GSI).

To achieve this, you can create an additional attribute called upvotes_gt_downvotes and store this attribute only if upvotes are greater than downvotes (or store the diff to use it in more queries). Additionally, you will need to make this new attribute (upvotes_gt_downvotes) along with timestamp the sort-key (SK) for the GSI table.

To get the result you will have to scan this GSI. Note that this GSI will only have records which satisfy your query and will be removed from the GSI as soon as the attribute is deleted from the record.
While downvoting, if this value becomes 0 or -ve you will have to delete this attribute when updating the record. (deleting this attribute automatically removes the record from GSI)

Upvotes: 2

Related Questions