aws DynamoDB boto3 Query GROUP BY and ORDER BY

Question

I'm looking to request a dynamodb table but I can not figure out how to do a GROUP BY and a ORDER BY with the python 2.7 sdk boto3.

I have read and read again the aws documentation and most of the stacks topics but I haven't find clearly answer because my request is quite a bit complex.

I would get all the items from my tables which have a 'permaname' GROUP BY 'source' and I would have the result ORDER BY created_on DESC.

This is how I'm doing my request my simple request for the moment :

response = self.table.query(KeyConditionExpression = key('permaname').eq(self.permaname))

I hope someone has the answer

Answer 1

You can perform ORDER BY operation only on the SORT KEY attribute.If your SORT KEY is set to be created_on, you can use ScanIndexForward to perform the ORDER BY operation.

response = self.table.query(KeyConditionExpression = key('permaname').eq(self.permaname), ScanIndexForward=False)

Answer 2

DynamoDB doesn't have aggregate function like GROUP BY in RDBMS. The sorting can be performed on SORT KEY attribute only. For all other attributes, you may need to do it in client side.

The alternate approach would be to create Global Secondary Index (GSI) with SORT Key. However, GSI would incur additional cost for read and write capacity units.

Query results are always sorted by the sort key value. If the data type of the sort key is Number, the results are returned in numeric order; otherwise, the results are returned in order of UTF-8 bytes. By default, the sort order is ascending. To reverse the order, set the ScanIndexForward parameter to false.