How to sort DynamoDB query result without specifying range key value

Question

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:

Fetch a single item

Right now I do a getItem with

Key: {
  groupId,
  actionId
}

Works great.

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

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

What is the exact key condition expression you are using right now? — Matthew Pope
@MatthewPope your question helped me see my issue more clearly - I can in fact query without the createdAt value but not with my desired filter. I've edited the question. — cyberwombat
So are you asking about how to specify a sort in a DynamoDB query, or are you asking how to design your table to enable the queries you want? If it’s the latter, it might be helpful for you to list out your queries as English sentences or as SQL. — Matthew Pope
I've added an sql to illustrate. Whether I need help with the query or the structure of my index I guess depends on what is needed to happen. Hopefully the sql clarifies? — cyberwombat
Is that the only query you need to do? If there are others, can you list them as well? With DynamoDB, you get great performance, but the trade off is that you need to design your tables with all of your access patterns in mind. — Matthew Pope

Matthew Pope Matthew Pope · Accepted Answer · 2019-07-20T21:54:04

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.

How to sort DynamoDB query result without specifying range key value

1 Answers