DynamoDB Range-Key bit-wise search

Question

I have a problem where I have a Building and the building can be in multiple projects. I need to query all the buildings that belong to a specific project.

I tried to do something like this but contains cannot be used to search for the flags.

PK => company#BUILDINGS
SK => buildingId#INFO
LSI => flags

I know that:
*  I can do it by duplicating the building record adding the project in the range key.
* Local Secondary Index per project cannot solve this as the number of projects is grater then 5
* Global Secondary Index can solve this ( but having many global secondary index ( one per project ) would increase writes cost for each building update ( the cost is not that big of a issue, but still have a  limit of 20, more than needed at the moment, but I wouldn't like to have that type of hard limit and would prefer to duplicate the records instead )

This could be easily ( from users query perspective ) be solved using:
* Numeric range key and bit-wise to search for the project.
* String range key and **contains** instead of only **begins_with** 
* String range key with pattern
        * something like Range Value = "01100" and search for begins_with "??1"

I'm new to dynamoDB, and I'm migrating a mongoDB project to it. So I may be missing something.

is there a easy way to solve this query problem that I'm unaware of?

thanks in advance

Accepted Answer

You need to understand DynamoDB by first understanding its underlying data structure, which can be likened to a B+ Tree. DynamoDB is not like MongoDB in the fact it does not allow you to create access patterns which are in-efficient. All of your requests must locate items which are stored physically together, which is why contains is not a valid function on a sort key. You must use begins_with or between etc.. as you can be guaranteed those items are close together. This ensures DynamoDB holds one of its key tenets, "Predictable performance at any scale".

Querying all buildings that belong to a specific project is as simple as creating a GSI with project as the partition key.

SELECT building FROM mytable.myindex WHERE project = '123'

This would return you a list of buildings for that project.

Querying all `buildings` that belong to a specific `project` is as simple as creating a GSI with `project` as the partition key.

`SELECT building FROM mytable.myindex WHERE project = '123'`

This would return you a list of buildings for that project.

DynamoDB Range-Key bit-wise search

Relevant content