How do I query by only part of a composite key in DynamoDB?

Question

Let's say, I have Users writing reviews of Products.

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

Can you show the full query that's giving you the error? – Seth Geoghegan Sep 30 '20 at 15:35 — Seth Geoghegan, Sep 30 '20 at 15:35

Seth Geoghegan · Accepted Answer · 2020-09-30T23:14:54.427

I have created a table review in DynamoDB with both userId and productId as HASH keys.

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

ddbClient.query(
  "TableName": "<YOUR TABLE NAME>",
  "KeyConditionExpression": "#userId = :userId",
  "ExpressionAttributeValues": {
    ":userId": {
      "S": "USER#ABC"
    }
  },
  "ExpressionAttributeNames": {
    "#userId": "userId"
  }
)

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:

ddbclient.query(
{
  "TableName": "<YOUR TABLE NAME>",
  "IndexName": "reviews_by_product_index",
  "KeyConditionExpression": "#productId = :productId",
  "ExpressionAttributeValues": {
    ":productId": {
      "S": "PRODUCT#456"
    }
  },
  "ExpressionAttributeNames": {
    "#productId": "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)

query where partition key = "USER#ABC" and sort key begins_with "PRODUCT"

it doesnt work for me: WHERE business_id#type_id#partner_id=435634652#3#69992528 — showtime, Jun 22 '22 at 08:02

How do I query by only part of a composite key in DynamoDB?

1 Answers1

Linked