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

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 2

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 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

I never thought that such a trivial task could turn into a problem in DynamoDB. Dynamo requires some basic partition. I managed to order data by adding an extra column status and then create GSI index using both fields. I order data with status="active" by createdAt field.

Create GSI

{
        IndexName: "createdAt",
        KeySchema: [
            { AttributeName: "status", KeyType: "HASH" },
            { AttributeName: "createdAt", KeyType: "RANGE" }
        ],
        Projection: { ProjectionType: "ALL" },
        ProvisionedThroughput: {
          ReadCapacityUnits: N,
          WriteCapacityUnits: N
        }
      }

query data

const result = await this.dynamoClient.query({
  TableName: "my table",
  IndexName: "createdAt",
  KeyConditionExpression: "#status = :status and #createdAt > :createdAt",
  Limit: 5,
  ExpressionAttributeValues: {
    ":status": {
      "S": "active"
    },
    ":createdAt": {
      "S": "2020-12-10T15:00:00.000Z"
    }
  },
  ExpressionAttributeNames: {
    "#status": "status",
    "#createdAt": "createdAt"
  },
});

Answer 5

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.

Answer 6

If you are using boto2 and you have the sort key on one of the columns in your table, you can sort what you retrieve in order or in reverse order by saying:

result = users.query_2(
    account_type__eq='standard_user',
    reverse=True)

If you are using boto3 and you have the sort key on the column that you want to sort the result by, you can sort the data you retrieve by saying:

result = users.query(
    KeyConditionExpression=Key('account_type').eq('standard_user'),
    ScanIndexForward=True)

Remember in boto3 if ScanIndexForward is true , DynamoDB returns the results in the order in which they are stored (by sort key value). This is the default behavior. If ScanIndexForward is false, DynamoDB reads the results in reverse order by sort key value, and then returns the results to the client.

Answer 7

If the table already existed then add GSI (Global Secondary Index) to the attribute you want for the table and use Query, not Scan. If you are about to create the table then you can add LSI (Local Secondary Index) to the attribute you want.