CODEWITHSUNDEEP
amazon-dynamodb
Cmag
Cmag

Reputation: 15770

DynamoDB data model secondary index search

Folks, Given we have to store the following shopping cart data:

 userID1         ['itemID1','itemID2','itemID3']
 userID2         ['itemID3','itemID2','itemID7']
 userID3         ['itemID3','itemID2','itemID1']

We need to run the following queries:

How would you model this in DynamoDB?

Option 1, only have the Hash key? ie

    HashKey(users)  cartItems
    userID1         ['itemID1','itemID2','itemID3']
    userID2         ['itemID3','itemID2','itemID7']
    userID3         ['itemID3','itemID2','itemID1']

Option 2, Hash and Range keys?

    HashKey(users)  RangeKey(cartItems)
    userID1         ['itemID1','itemID2','itemID3']
    userID2         ['itemID3','itemID2','itemID7']
    userID3         ['itemID3','itemID2','itemID1']

But it seems that range keys can only be strings, numbers, or binary...

Should this be solved by having 2 tables? How would you model them?

Thanks!

Upvotes: 0

Views: 109

Answers (1)

Mingliang Liu
Mingliang Liu

Reputation: 5787

Rule 1: The range keys in DynamoDB table must be scalar, and that's why the type must be strings, numbers, boolean or binaries. You can't take a list, set, or a map type.

Rule 2: You cannot (currently) create a secondary index off of a nested attribute. From the Improving Data Access with Secondary Indexes in DynamoDB documentation. That means, you can not index the cartItems since it's not a top level JSON attribute. You may need another table for this.

So, the simple answer to your question is another question: how do you use your data?

If you query the users with input item (say itemID3 in your case) infrequently, perhaps a Scan operation with filter expression will work just fine. To model your data, you may use the user id as the HASH key and cartItems as the string set (SS type) attribute. For queries, you need to provide a filter expression for the Scan operation like this:

contains(cartItems, :expectedItem)

and, provide the value itemID3 for the placeholder :expectedItem in parameter valueMap.

If you run many queries like this frequently, perhaps you can create another table taking the item id as the HASH key, and set of users having that item as the string set attribute. In this case, the 2nd query in your question turns out to be the 1st query in the other table.

Be aware of that, you need to maintain the data at two tables for each CRUD action, which may be trivial with DynamoDB Streams.

Upvotes: 3

Related Questions