Data Modeling DynamoDB Data With Filtering, Sorting & Pagination

DynamoDB is so much more than a simple key-value datastore.

Yet many of its users only use it to make simple 1–1 and 1-many queries.

In this article, I’ll demonstrate how to design your DynamoDB data to make more complex queries, filter on these queries, sort the results and paginate on it as well.

We’ll get right into it starting with the data model.

The Data Model

Imagine we are building a hotel booking management software.

We’ll focus only on the “admin management” portal of this application.

We need to design our data for a specific page on this admin portal, that includes the following:

• Display the full list of rooms in the hotel

• Display a filtered list of rooms based on categories like “view”, “size”, and “type”.

• Display different sort orders by the above categories

• Paginate the results

Based on these requirements, we can model the data as follows:

Let’s break down this data model.

For the partition key (pk), we use the hotelID “abc” inside the value as “hotel#abc#rooms”. Here “hotel” is the prefix, abc is the hotelID and “rooms” denotes the collection suffix.

For the sort key we use what is called “key overloading”. This means we “overload” the sort key value with multiple values.

Notice how we store the prefix “room”, then:

• the room’s view type value (denoted in blue color)

• the size of the room (denoted in green color)

• the room type e.g. a deluxe or suite room (denoted in yellow color)

• the actual room number (denoted in purple)

Now to understand why this data model is effective, we have to look at some queries.

How to filter data

Let’s look at the first query against this data model.

We’ll fetch all rooms with a sea view.

TableName: "hotel-app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":sk": "room#sea-view#",
},

The value for “:sk” is what we want to prefix our query of the sk value. In other words, we say “get me all items whose sk value begins with the string room#sea-view#”.

We can then do the same to filter all sea view rooms by size:

TableName: "hotel-app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":sk": "room#sea-view#40m",
},

In this query we fetch all sea view rooms with 40 square meters in size.

Then we can add to the “:sk” value the room type (e.g. suite) to filter down more.

Now if we want to get more flexible and filter rooms by size first, instead of by view then size, we can create a LSI (local secondary index) and use the LSI sort key as follows:

pk: "hotel#abc#rooms" //same pk as base table
lsisk: "room#30m#sea-view#deluxe#101"

And query it like this:

TableName: "hotel-app",
IndexName: "LSI1:,
KeyConditionExpression: "pk = :pk AND begins_with(lsi1sk, :lsi1sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":lsi1sk": "room#40m#sea-view",
},

We can do this for the rest of the other items and change up the order of the filters to filter our data in a fine-grained manner.

How to sort data

Sorting our data can be done by switching between indexes (GSI or LSI) and using the ScanIndexForward attribute.

Here’s an example:

With this query, we sort our data by whatever filter value is first.

TableName: "hotel-app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":sk": "room#sea-view#40m",
},

Here we will sort data by view type (e.g. sea-view).

To change the sort to room type or size, we can simply use another LSI (or GSI). 

The best part is that our data model is designed to handle sorting effortlessly. We don’t need to change our code as the sorting will always be performed on the sort key (sk), which we change with our different LSI sort keys (lsi1sk).

Then to control the sort order — descending or ascending order — we can define the ScanIndexForward attribute like so:

TableName: "hotel-app",
IndexName: "LSI1:,
KeyConditionExpression: "pk = :pk AND begins_with(lsi1sk, :lsi1sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":lsi1sk": "room#40m#sea-view",
},
ScanIndexForward: false //false = descending, true = ascending order

How to paginate data

DynamoDB makes pagination a managed task for you. 

Everytime you run a Query() operation to fetch multiple items, you get a attribute “LastEvaluatedKey” in the response object.

Together with a limit value you can design easy pagination.

Looking at our example above, say we wanted to get back at most 10 rooms for every query and display a pagination feature with the ability to go to the next page of results.

Here’s the 2 step process:

1. Query data and retrieve “LastEvaluatedKey”

Run a simple query of hotel rooms and add a limit value:

TableName: "hotel-app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":sk": "room#sea-view#40m",
},
Limit: 10,

In the query response object you will get “Items” (the actual data returned) and “LastEvaluatedKey”.

2. Pass in “LastEvaluatedKey” in the next Query

Next, you can pass in this “LastEvaluatedKey” to the “ExclusiveStartKey” attribute in your next query:

export const handler = (event) => ...
const { LastEvaluatedKey } = JSON.parse(event.body);

TableName: "hotel-app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :sk)",
ExpressionAttributeValues: {
  ":pk": "hotel#abc#rooms",
  ":sk": "room#sea-view#40m",
},
Limit: 10,
ExclusiveStartKey: LastEvaluatedKey

Now this query will return items starting from that LastEvaluatedKey, which is the item after the last set of items in the previous query.

You can repeat this process until the value in LastEvaluatedKey is equal to null. That’s when you know you’ve reached the end of the results.

👋 My name is Uriel Bitton and I hope you learned something in this edition of Excelling With DynamoDB.

📅 If you're looking for help with DynamoDB, let's have a quick chat.

🙌 I hope to see you in next week's edition!