DynamoDB: Query all data and sorted by date descending

Question

I'm new to the AWS DynamoDB and need a guidance from the experts in here.

I'm creating an application that will display a list of events with a condition the event date must be greater than today date and order by event date descending. My table schema like below:

Partition key = eventid

Sort Key = eventdate

If I use relational db i can use "SELECT * FROM events where eventdate > todaydate ORDERBY eventdate DESC", but how i want to achieve this with AWS DynamoDB? I'm looking to use QUERY instead of SCAN.

Solution 1

According to DynamoDB Query API Documentation :

Query :

A Query operation uses the primary key of a table or a secondary index to directly access items from that table or index.

Use the KeyConditionExpression parameter to provide a specific value for the partition key. The Query operation will return all of the items from the table or index with that partition key value. You can optionally narrow the scope of the Query operation by specifying a sort key value and a comparison operator in KeyConditionExpression. You can use the ScanIndexForward parameter to get results in forward or reverse order, by sort key.

KeyConditionExpression :

The condition that specifies the key value(s) for items to be retrieved by the Query action.

The condition must perform an equality test on a single partition key value. The condition can also perform one of several comparison tests on a single sort key value. Query can use KeyConditionExpression to retrieve one item with a given partition key value and sort key value, or several items that have the same partition key value but different sort key values.

The partition key equality test is required, and must be specified in the following format:

partitionKeyName = :partitionkeyval

If you also want to provide a condition for the sort key, it must be combined using AND with the condition for the sort key. Following is an example, using the = comparison operator for the sort key:

partitionKeyName = :partitionkeyval AND sortKeyName = :sortkeyval

Valid comparisons for the sort key condition are as follows:

• sortKeyName = :sortkeyval - true if the sort key value is equal to :sortkeyval.
• sortKeyName < :sortkeyval - true if the sort key value is less than :sortkeyval.
• sortKeyName <= :sortkeyval - true if the sort key value is less than or equal to :sortkeyval.
• sortKeyName > :sortkeyval - true if the sort key value is greater than :sortkeyval.
• sortKeyName >= :sortkeyval - true if the sort key value is greater than or equal to :sortkeyval.
• sortKeyName BETWEEN :sortkeyval1 AND :sortkeyval2 - true if the sort key value is greater than or equal to :sortkeyval1, and less than or equal to :sortkeyval2.
• begins_with ( sortKeyName, :sortkeyval ) - true if the sort key value begins with a particular operand. (You cannot use this function with a sort key that is of type Number.)

You can optionally use the ExpressionAttributeNames parameter to replace the names of the partition key and sort key with placeholder tokens. This option might be necessary if an attribute name conflicts with a DynamoDB reserved word. For example, the following KeyConditionExpression parameter causes an error because Size is a reserved word:

Size = :myval

To work around this, define a placeholder (such a #S) to represent the attribute name Size. KeyConditionExpression then is as follows:

#S = :myval

For a list of reserved words, see Reserved Words in the Amazon DynamoDB Developer Guide.

For more information on ExpressionAttributeNames and ExpressionAttributeValues, see Using Placeholders for Attribute Names and Values in the Amazon DynamoDB Developer Guide.

Type: String

Required: No

Your Scenario Can be converted to following Code :

$tableName = "genericTable";
$response = $dynamodb->query([
    'TableName' => $tableName,
    'IndexName' => 'OrderCreationDateIndex',
    'KeyConditionExpression' => 'partitionKeyName = :partitionkeyval AND sortKeyName = :sortkeyval',
    'ExpressionAttributeValues' =>  [
        ':partitionkeyval' => ['S' => 'pkey'],
        ':sortkeyval' => ['S' => 'sortkey']
    ],
    'Select' => 'ALL_PROJECTED_ATTRIBUTES',
    'ScanIndexForward' => false,
    'ConsistentRead' => true,
    'Limit' => 5,
    'ReturnConsumedCapacity' => 'TOTAL'
]);

Solution 2

Unfortunately, you can't achieve the above use case using Query API.

Reasons:-

• You need the greater than comparison operator. The non-equality comparison operator can be used on FilterExpression or on sort key attribute using KeyConditionExpression.

• In the above use case, you don't have eventId, so you can't use KeyConditionExpression. Even if you create a GSI using eventDate as hash key, you can't use non-equality operator on hash key and sorting is not possible for any attributes except Sort key. So, you can't achieve the sorting requirement using GSI option.

Workaround solution to use Query API for above use case:-

• Create a GSI with hash key as eventDate

• Use Query API to query the index by eventDate

• Sort the data by eventDate at client side. Most of the programming languages has good sorting APIs. You can easily achieve this at client side

Note:-

• You can't achieve both sorting and using Query API requirements especially without knowing the hash key of the main table

Comments

[Zalizan Zolkipali]

Tq for your reply sir. That's mean there is no workaround for me to achieve results as I mentioned in above question?

[LuFFy]

@notionquest, Please note that Using Query API, You can use the ScanIndexForward parameter to get results in forward or reverse order, by sort key.

[notionquest]

@LuFFy Here the use case doesn't have the hash key value. OP wanted to use query API without having the hash key value. That's why I proposed to use GSI with hash key as eventDate.

[LuFFy]

@zalizan-zolkipali, convert Created Date to unixtime and then follow my answer to achieve your task, It's already working & live solution at my end.

[LuFFy]

@notionquest, if OP want to get solution for dynamodb only, then he would have only option to change his table structure and add a SortKey & DONE....He has not posted anything that he is using Hash Key Value, Can you brief me where he said such sentence ?

[notionquest]

@LuFFy, "If I use relational db i can use "SELECT * FROM events where eventdate > todaydate ORDERBY eventdate DESC", but how i want to achieve this with AWS DynamoDB"... Where is the hash key mentioned here. OP wants the equivalent for above query.

[notionquest]

@Zalizan Zolkipali, The above solution would not work as eventId is not part of the query. If eventId is available, it is a straight forward use case for Query API usage.

[Yasith Prabuddhaka]

This should be the accepted answer since you cannot achieve conditional operator from the hash key in query