Is it possible to ORDER results with query or scan in DynamoDB?

Question

Is it possible to ORDER results with Query or Scan API in DynamoDB?

I need to know if DynamoDB has something like ORDER BY 'field' from SQL queries?

Thanks.

Answer 1

Not explicitly, however, ordering is obviously needed for many real world use cases and can be modeled by means of the Hash and Range Type Primary Key accordingly:

In this case, the primary key is made of two attributes. The first attributes is the hash attribute and the second one is the range attribute. Amazon DynamoDB builds an unordered hash index on the hash primary key attribute and a sorted range index on the range primary key attribute. [emphasis mine]

You can then use this range index to optionally request items via the RangeKeyCondition parameter of the Query API and specify forward or backward traversal of the index (i.e. the sort direction) via the ScanIndexForward parameter.

Update: You can order by an attribute with a local secondary index in the same way.

Answer 2

You can use the sort-key and apply the ScanIndexForward parameter in a query to sort in either ascending or descending order. Here I limit items returned to 1.

var params = {
    TableName: 'Events',
    KeyConditionExpression: 'Organizer = :organizer',
    Limit: 1,
    ScanIndexForward: false,    // true = ascending, false = descending
    ExpressionAttributeValues: {
        ':organizer': organizer
    }
};

docClient.query(params, function(err, data) {
    if (err) {
        console.log(JSON.stringify(err, null, 2));
    } else {
        console.log(JSON.stringify(data, null, 2));
    }
});

Answer 3

Use ScanIndexForward(true for ascending and false for descending) and can also limit the result using setLimit value of Query Expression.

Please find below the code where used QueryPage for finding the single record.

public void fetchLatestEvents() {
    EventLogEntitySave entity = new EventLogEntitySave();
    entity.setId("1C6RR7JM0JS100037_contentManagementActionComplete");

    DynamoDBQueryExpression<EventLogEntitySave> queryExpression = new DynamoDBQueryExpression<EventLogEntitySave>().withHashKeyValues(entity);
    queryExpression.setScanIndexForward(false);
    queryExpression.withLimit(1);
    queryExpression.setLimit(1);

    List<EventLogEntitySave> result = dynamoDBMapper.queryPage(EventLogEntitySave.class, queryExpression).getResults();
    System.out.println("size of records = "+result.size() );
}

@DynamoDBTable(tableName = "PROD_EA_Test")
public class EventLogEntitySave {

        @DynamoDBHashKey
        private String id;
        private String reconciliationProcessId;
        private String vin;
        private String source;
}

public class DynamoDBConfig {
    @Bean
    public AmazonDynamoDB amazonDynamoDB() {

            String accesskey = "";
            String secretkey = "";
            //
            // creating dynamo client
            BasicAWSCredentials credentials = new BasicAWSCredentials(accesskey, secretkey);
            AmazonDynamoDB dynamo = new AmazonDynamoDBClient(credentials);
            dynamo.setRegion(Region.getRegion(Regions.US_WEST_2));
            return dynamo;
        }

    @Bean
    public DynamoDBMapper dynamoDBMapper() {
        return new DynamoDBMapper(amazonDynamoDB());
    }
}

Answer 4

Another option which should solve the problem is to

1. Define a local secondary index with the "normal" hash key to be the hash key of the LSI as well
2. Define the field you want to sort as "Sort Key" of the LSI
3. Query the LSI and set the ordering as desired (see above)

This will enable sorting of any value of your table as required. It is a very efficient way to find the highest ranking items in your table without the need to get the whole query and then filtering it afterwards.