CODEWITHSUNDEEP
amazon-dynamodb
Santhosh
Santhosh

Reputation: 955

Dynamodb query expression

Team,

I have a dynamodb with a given hashkey (userid) and sort key (ages). Lets say if we want to retrieve the elements as "per each hashkey(userid), smallest age" output, what would be the query and filter expression for the dynamo query.

Thanks!

Upvotes: 0

Views: 1456

Answers (3)

Lokesh Gupta
Lokesh Gupta

Reputation: 304

You can do it using ScanIndexForward

YourEntity requestEntity = new YourEntity();
requestEntity.setHashKey(hashkey);
DynamoDBQueryExpression<YourEntity> queryExpression = new DynamoDBQueryExpression<YourEntity>()
            .withHashKeyValues(requestEntity)
            .withConsistentRead(false);
equeryExpression.setIndexName(IndexName); // if you are using any index
queryExpression.setScanIndexForward(false);
queryExpression.setLimit(1);

Upvotes: 0

Ivan Mushketyk
Ivan Mushketyk

Reputation: 8305

There are two ways to achieve that:

If you don't need to get this data in realtime you can export your data into a other AWS systems, like EMR or Redshift and perform complex analytics queries there. With this you can write SQL expressions using joins and group by operators.

You can even perform EMR Hive queries on DynamoDB data, but they perform scans, so it's not very cost efficient.

Another option is use DynamoDB streams. You can maintain a separate table that stores:

Table: MinAges

UserId - primary key

MinAge - regular numeric attribute

On every update/delete/insert of an original query you can query minimum age for an updated user and store into the MinAges table

Another option is to write something like this:

storeNewAge(userId, newAge)
def smallestAge = getSmallestAgeFor(userId)
storeSmallestAge(userId, smallestAge)

But since DynamoDB does not has native transactions support it's dangerous to run code like that, since you may end up with inconsistent data. You can use DynamoDB transactions library, but these transactions are expensive. While if you are using streams you will have consistent data, at a very low price.

Upvotes: 0

Tofig Hasanov
Tofig Hasanov

Reputation: 3709

I don't think you can do it in a query. You would need to do full table scan. If you have a list of hash keys somewhere, then you can do N queries (in parallel) instead.

[Update] Here is another possible approach: Maintain a second table, where you have just a hash key (userID). This table will contain record with the smallest age for given user. To achieve that, make sure that every time you update main table you also update second one if new age is less than current age in the second table. You can use conditional update for that. Update can either be done by application itself, or you can have AWS lambda listening to dynamoDB stream. Now if you need smallest age for each use, you still do full table scan of the second table, but this scan will only read relevant records, to it will be optimal.

Upvotes: 0

Related Questions