CODEWITHSUNDEEP
amazon-dynamodbdynamodb-queries
cyberwombat
cyberwombat

Reputation: 40074

How to sort DynamoDB query result without specifying range key value

I am building a DynamoDB table and am running into issues on how to best structure my indexes. I have 3 queries I need to perform.

My Table:

AttributeDefinitions:
  # This is large groups that can have many events
  - AttributeName: groupId
    AttributeType: S
  # An event can have many actions
  - AttributeName: eventId
    AttributeType: S
  # Each item has a unique actionId
  - AttributeName: actionId
    AttributeType: S
  # Each item has a creation date
  - AttributeName: createdAt
    AttributeType: S
  # Some type I need to filter by (enum: trigger|task for example)
  - AttributeName: actionType
    AttributeType: S

# Main query to return items by action ID - that works fine
KeySchema:
  - AttributeName: groupId
    KeyType: HASH
  - AttributeName: actionId
    KeyType: RANGE

These are the 3 queries I need to achieve:

  1. Fetch a single item

Right now I do a getItem with 

Key: {
  groupId,
  actionId
}

Works great.

  1. Fetch all items (actions) by eventId

SQL:

SELECT * FROM theTable WHERE eventId = 123

If I do this local index then that works great:

KeySchema:
  - AttributeName: groupId
    KeyType: HASH
  - AttributeName: eventId
    KeyType: RANGE
  1. Fetch all items with the actionType='trigger' ordered by createdAt date that belong to a groupId (irrespective of eventId)

SQL:

SELECT * FROM theTable WHERE actionType = 'trigger' AND groupId = 123 SORT BY createdAt

This is the one giving me issues. I would like to query my data and have it returned sorted by date. However I need to query using another field as my RANGE. So I if I add createdAt as my range I can't use actionType to filter. If I use actionType then there is no sort.

How can I best structure this table? In terms of amnount of data. There can be many groups (groupId). Each group can have many events (eventId). But each event is likely to only have <100 actions (actionId).

Upvotes: 0

Views: 331

Answers (1)

Matthew Pope
Matthew Pope

Reputation: 7669

In order to implement a query like SELECT * FROM theTable WHERE actionType = 'trigger' AND groupId = 123 SORT BY createdAt in DynamoDB, you need to have an index with a hash key of groupId and a composite sort key of actionTypeCreatedAt (which is, predictably, the actionType, a delimiter, and then the createdAt date).

In your index, the data would look like this (assuming a delimiter of "_" in the sort key):

groupId | actionTypeCreatedAt
--------|------------------------------
    123 | trigger_2019-06-30T08:30:00Z
    123 | trigger_2019-07-05T23:00:00Z
    123 | trigger_2019-07-20T10:15:00Z
    123 | action2_2019-06-25T15:10:00Z
    123 | action2_2019-07-08T02:45:00Z

Now, to achieve the query you desire, you would need to use a key condition expression of groupId = 123 AND begins_with(actionTypeCreatedAt, "trigger_"). DynamoDB will automatically sort the results by the sort key, and since all of the query results have the same actionType prefix, the results will be sorted only by the createdAt date.

Upvotes: 1

Related Questions