Dynamo DB advice on structure of table

Question

I'm wanting to store data like this

{ date: '2020-10-06T20:46:01.942Z', topic: 'a great topic', votes: 123 },
{ date: '2020-10-07T23:46:01.942Z', topic: 'a great topic', votes: 125 },
{ date: '2020-10-06T20:46:03.942Z', topic: 'a not so great topic', votes: 12 }

I've created a Dynamo and have my timestamp as the primary key, due to low traffic it's safe to assume these will be unique.

I'm hoping to return data based on topic so a dynamo query using the sort key where it equals a great topic.

I keep getting "message": "Query condition missed key schema element" when using the following in a dyanmo db query statement:

params = {
    TableName: table,
    KeyConditionExpression: "#topic = :topic",
    ExpressionAttributeNames:{
        "#topic": "topic"
    },
    ExpressionAttributeValues: {
        ":topic": "a great topic"
    }
  }

This is my table

var tableParams = {
    TableName : "a cool table",
    KeySchema: [       
        { AttributeName: "date", KeyType: "HASH"},  //Partition key
        {
          AttributeName: "topic",
          KeyType: "RANGE"
      }
    ],
    AttributeDefinitions: [       
        { AttributeName: "date", AttributeType: "S" },
        { AttributeName: "topic", AttributeType: "S" }
    ],
    ProvisionedThroughput: {       
        ReadCapacityUnits: 5, 
        WriteCapacityUnits: 5
    }
};

I'm hesitant to use scan because it can be quite wasteful even though it would be easier to filter the data using javascript :p

Thanks for your help.

Answer 1

You are attempting to query your table using the topic field, which won't work because it's your sort key. When querying DynamoDB, you need to provide the partition key with the optional sort key.

Let's quickly review some terminology so we are all on the same page (from the docs).

• Primary Key - The primary key uniquely identifies each item in the table, so that no two items can have the same key. There are two different types of Primary Key:
• Partition key – A simple primary key, composed of one attribute known as the partition key.
• Partition key and sort key – Referred to as a composite primary key, this type of key is composed of two attributes. The first attribute is the partition key, and the second attribute is the sort key

When you perform a query operation in Dynamodb, you use the KeyConditionExpression parameter to provide a specific value for the partition key. The Query operation will return all of the items from the table or index with that partition key value. You can optionally narrow the scope of the Query operation by specifying a sort key value and a comparison operator in KeyConditionExpression

Looking at your query, you're trying to query an item by specifying only the sort key, which is not going to work. I think you've got your primary key backwards. You want the partition key to be topic and your sort key to be date.

To get your query to work as written, change the KeySchema of your table to this:

    KeySchema: [       
        { AttributeName: "topic", KeyType: "HASH"},
        { AttributeName: "date", KeyType: "RANGE"}
    ]

If you did this, your query operation would return the items for that topic sorted by date.

Answer 2

If you query by topic, it would be much more effective to set the topic as partition (hash) key and the timestamp as sort (range) key. Otherwise you would always need to provide the specific timestamp because a query always needs to include the partition key.

A beneficial side effect would be that the returned results would already be sorted by timestamp.

Update: For further details, please refer to Seth‘s great answer above!