How can I define an efficient range query based on a DynamoDB composite sort key?

Question

With composite sort keys it is possible to define hierarchical (one-to-many) relationships in data that can be queried at any level of the hierarchy.

For example, in a table listing geographical locations, the sort key could be structured as follows:

[country]#[city]#[founded_date]#[population]

Based on the best practices for using sort keys to organize data it should be possible to make efficient range queries for a list of locations at any one of these levels of aggregation, from country, to population, and everything in between.

How can I compose a query e.g. to get all items where the city name starts with "new", was founded prior to 3/14/1923 and has a population greater than 100'000?

Answer 1

The example you cited is 100% about hierarchical data. You've introduced data that is not hierarchical into it, which completely breaks that pattern. In the example you couldn't find cities that start with "new" without first knowing the country, region, state and county. The hierarchy model assumes you are always searching within a known part of the hierarchy, and want to get everything below it. When they say "everything in between" they mean you can query at any of those levels, not that you can query for any of those elements. For example, you can query for every location in the state of NY by specifying the country, region and state. You can find everything in NYC by specifying the country, region, state, county and city.

[country]#[region]#[state]#[county]#[city]#[neighborhood]

To do what you are looking to do you need to support some completely different access patterns. You probably need some set of GSIs to allow for the different access patterns you have, and will likely need to do some filtering beyond what you can store in the keys. Remember that NoSQL is all about access patterns. You should spend some time figuring out what those are, and once you do that you can figure out how to store your data, including any indexes needed to support the ways in which you need to access your data.