Let's say, I have User
s writing reviews of Product
s.
User and Product are separate entities with their own ids.
Review
is an entity with a composite id composed of userId
and productId
.
I have created a table review
in DynamoDB with both userId
and productId
as HASH keys.
aws dynamodb create-table --table-name review \
--attribute-definitions \
AttributeName=user_id,AttributeType=S \
AttributeName=product_id,AttributeType=S \
--key-schema \
AttributeName=user_id,KeyType=HASH \
AttributeName=product_id,KeyType=RANGE \
--provisioned-throughput ReadCapacityUnits=10,WriteCapacityUnits=5
Thus making userId
+productId
the composite key.
The review data object is held against that key.
Querying for a review by user and product is fine.
But how do I query for all reviews by a user or all reviews for a product?
With a single parameter, e.g. if I do a query by single key conditional expression with just "#user_id = :userId"
or just "#product_id = :productId"
I get an error of the form
Query condition missed key schema element: user_id
or
Query condition missed key schema element: product_id
You've created a composite primary key for your review table, which consists of a Partition Key of
userId
and a and a Sort Key of 'productId' . You did not create two HASH keys.Logically, your review table will look something like this (I've made up some data for illustration purposes):
This table structure makes it easy to fetch reviews by user. Here's an example of a query for all reviews of
USER#ABC
This will return a collection of items reviewed by USER#ABC.
DynamoDB will not allow you to fetch items by only specifying the Sort Key (e.g. productId). You always need to provide the Partition Key. So how do you get a list of Users who have reviewed a given product?
If you want to search for all Users that have reviewed a single Product, you could introduce a global secondary index that swaps the Partition Key and Sort Key of your table. This pattern is known as an inverted index. Using my example from above, an inverted index would look like this:
This would allow you to fetch users by productId:
This query would return a collection of two items representing reviews for
PRODUCT#456
.When working with a composite primary key, you can search based on conditions of the sort key as long as you also specify the partition key. That's a mouthful, but it allows you to perform queries like (in pseudocode)