Can I List all items that contain these values on my DynamoDB table?-CodePudding

I have a DynamoDB table that holds items with the following structure:

{
  "applicationName": { //PARTITION KEY
    "S": "FOO_APP"
  },
  "requestId": { // SORT KEY
    "S": "zzz/yyy/xxx/58C28B6"
  },
  "creationTime": {
    "N": "1636332219136"
  },
  "requestStatus": {
    "S": "DENIED"
  },
  "resolver": {
    "S": "SOMEONE"
  }
}

In DynamoDB, can I query this table to List all items that match the provided values for applicationName, requestStatus and, resolver?

In other words, how can I list all items that match:

applicationName = 'FOO',
requestStatus = 'DENIED', and
resolver = 'SOMEONE'

With this table design, do I need GSIs? Can I do a Query or would it be a Scan?

What is the most cost-effective, efficient way of accomplishing this task?

I'm using Java's DynamoDBMapper.

CodePudding user response：

You can add another attribute that combines the values you're querying for, like this:

GSI1PK: <applicationName>#<requestStatus>#<resolver>

Then you define a Global Secondary Index (GSI1) with the Partition Key as GSI1PK and the sort key like your current sort key requestId.

Whenever you want to find all requests that match these three conditions, you build your search thing and query the global secondary index:

Query @GSI1
Partition Key = FOO_APP#DENIED#SOMEONE

That will yield all requests that match the combination of criteria. This kind of denormalization is common in NoSQL databases like DynamoDB.

CodePudding user response：

You may not be able to query this schema as your sort key - requestId is not in criteria. That means, your query will fail. For a better schema design, you should have a sort key in such a way which can help you narrow down result set obtained by just querying on PartitionKey.

So for solution, you will have to create new index as following:

applicationName -> Partition Key

requestStatus -> Sort Key

resolver

Then you can query with keyConditionExpression on applicationName and requestStatus with filterExpression on resolver.