DynamoDB Hierarchical, Sorted Queries
I would like to be able to query data hierarchically and return the results ordered by another attribute. What is the most efficient way to store and query sorted hierarchical data?
For example, if I have a table with four attributes:
location contains hierarchical information such as
state:county:city, so a few records may look like:
------------|--------|-------------------|-------------| customer_id |country |location |last_updated | ------------|--------|-------------------|-------------| 123456 |USA |WA:King:Seattle |2022-03-18 | 789012 |USA |WA:King:Kent |2022-03-15 | 098765 |USA |NY:Bronx:NYC |2022-02-28 | 432109 |USA |WA:Spokane:Spokane |2022-03-20 |
PK of the table is the
customer_id because most queries will pull information by
customer_id, but there are other use cases that will want to (a) find all customers within a given location (e.g.
county), and (b) return the results sorted (descending) by
last_updated. To accomplish (a), I have a
country as the
location as the
But I can't figure out how to accomplish (b). My understanding is that ordering operations are usually performed with
scanIndexForward, but I'm already using the
GSI for the hierarchical query. Is there a way to do both (a) and (b)?
Create a GSI where the PK is Country+Location concatenated and the SK is the last_updated. Then for any given Country+Location value you can find the last updated values (and corresponding customer ids in the projection) for that location. Note you can setup 20 GSIs.
Or are you hoping to do a begins_with against the location (like for the wider value of WA:King) while also getting only the latest X many? In which case, well, you can't. You can however insert new items for each hierarchical level (like WA:King, etc) so that that value can be used as the PK. Basically pre-materialize at each level. You only get one sort key per table/index and you can't use the sort key functionality twice.
Of course, if you intend to get ALL the matching items (not just the latest X many) and just want them sorted, you can sort on the client.
Thanks! I was indeed hoping to do something like a
begins_with, so it looks like I'm a little out of luck. I think what I'm going to do is create a different GSI for each potential level of hierarchy (e.g. one for Country, one for State, etc.) with the location as the PK and
last_updatedas the SK, and query the appropriate GSI based on the level of hierarchy needed.
Now DynamoDB can return the throughput capacity consumed by PartiQL API calls to help you optimize your queries and throughput costsasked 4 months ago
Athena - Execute multiple query and capture output in a file on e2asked 2 months ago
Suggestion : Update multiple items based on a common queryAccepted Answer
DynamoDB Update API response filterasked 6 days ago
RowCount for DynamoDBAccepted Answerasked 3 years ago
Delete whole partition or Delete using "begins_with" on the Partition Key?
Objects used in queriesAccepted Answerasked 3 years ago
Retrieving Athena Query History dataasked 3 years ago
How to store the Athena Query Results in DynamoDB table?asked 3 years ago
DynamoDB Hierarchical, Sorted QueriesAccepted Answerasked 2 months ago