DynamoDB query on two ranges and then sort

1
votes

Let’s say I have an album table where partition key is author and sort key is album. Each item also has a price, startDate and endDate attributes. Let say I want to find all the albums that “author=a”, “album=b”, “startDate < c”, “endDate > d” and “price is between e and f”, sorted by price. Is the most efficient way to do that is query on partition key and sort key, and then filter the results on conditions c, d, e and f, and then sort by price? Can secondary index help here? (It seems one secondary index can only be used for query on one or two non-key attributes, but my use case requires < and > operations on multiple non-key attributes and then sorting)

Thanks!

1
javaamazon-dynamodb

1 Answers

1
votes

I am working through a similar schema design process. The short answer is it will depend on exactly how much data you have that falls into the various categories, as well as on the exact QUERIES you hope to run against that data.

The main thing to remember is that you can only ever QUERY based on your Sort Key (where you know the Partition Key) but you ALSO have to maintain uniqueness in order to not overwrite needed data.

A good way to visualize this in your case would be as follows:

  1. Each Artist is Unique (Artist seems to me like a good Partition Key)
  2. Each Artist can have Mutliple Albums making this a good Sort Key (in cases where you will search for an Album for a known Artist)

In the above case your Sort Key is being combined with your Partition Key to create your Hash Key per the following answer (which is worth a read!) to allow you to write a query where you know the artist but only PART of the title.

Ie. here artist = "Pink Floyd" QUERY where string album contains "Moon" That would match "Pink Floyd" Dark Side of the Moon.

That being said you would only ever have one "Price" for Pink Floyd - Dark Side of the Moon since the Partition Key and Sort Key combine to handle uniqueness. You would overwrite the existing object when you updated the entry with your second price.

So the real question is, what are the best Sort Keys for my use case?

To answer that you need to know what your most frequent QUERIES will be before you build the system.

Price Based Queries?

In your question you mentioned Price attributes in a case where you appear to know the artist and album.

“author=a”, “album=b”, “startDated” and “price is between e and f”, sorted by price"

To me in this case you probably DO NOT know the Artist, or if you do you probably do not know the Album, since you are probably looking to write a Query that returns albums from multiple artists or at least multiple Albums from the same artist.

HOWEVER

That may not be the case if you are creating a database that contains multiple entries (say from multiple vendors selling the same artist / album at different prices). In which case I would say the easiest way to either store only ONE entry for an Artist-Album (partition key) at a given price (sort key) but you would lose all other entries that match that same price for the Artist-Album.

Multiple Queries MAY require Multiple Tables

I had a similar use case and ended up needing to create multiple tables in order to handle my queries. Data is passed / processed from one table and spit out into another one using a Lambda that is triggered on insertion. I then send some queries to one table and some other queries to the initial table.