Querying DynamoDB with multiple fields in an AppSync Resolver

Question

I have a DynamoDB messages table with id, content, createdAt (int), userID fields.

I can obtain a user's messages using below resolver:

{
    "version" : "2017-02-28",
    "operation" : "Query",
    "index" : "userid-createdat-index",
    "query" : {
      "expression": "userID = :userID",
        "expressionValues" : {
          ":userID" : $util.dynamodb.toDynamoDBJson($context.arguments.userID)
        }
    }
}

My objective is to get user messages within the last 5 seconds using the createdAt field which is epoch time in milliseconds. I would like to avoid using Scan operation as my table will be large.

How do I do that? What kind of DynamoDB index do I need for it?

score 1 · Answer 1 · answered Dec 28 '20 at 13:57

Assuming the id field is unique, you need to create the table partition key on id then a Global Secondary Index on (userID , createdAt). The query to access the result that you are looking for should look something like this --key-condition-expression "userID = :userID and createdAt >= :createdAt"

Table creation

aws dynamodb create-table \
--table-name messages \
--attribute-definitions \
AttributeName=id,AttributeType=S \
AttributeName=userID,AttributeType=S \
AttributeName=createdAt,AttributeType=N \
--key-schema AttributeName=id,KeyType=HASH \
--provisioned-throughput ReadCapacityUnits=10,WriteCapacityUnits=5 \
--global-secondary-indexes \
"[{\"IndexName\": \"UserIDIndex\",
\"KeySchema\": [{\"AttributeName\":\"userID\",\"KeyType\":\"HASH\"},
{\"AttributeName\":\"createdAt\",\"KeyType\":\"RANGE\"}],
\"Projection\":{\"ProjectionType\":\"ALL\"},
\"ProvisionedThroughput\":     {\"ReadCapacityUnits\":10,\"WriteCapacityUnits\":10}}]"

Example query with GSI

aws dynamodb query \
--table-name messages \
--index-name UserIDIndex \
--key-condition-expression "userID = :userID and createdAt >= :createdAt" \
--expression-attribute-values '{":userID":{"S":"u1"} , ":createdAt":{"N":"2"} }'

More information on GSI can be found here

if you are running DynamoDB locally you can add --endpoint-url http://localhost:8000 to both of the above code snippets.

score -1 · Answer 2 · answered Dec 30 '20 at 02:42

Using suggestions from CruncherBigData's answer, I successfully run such a query.

I created an index with a partition key for userID and a sort key with createdAt. My mistake here was forgetting to select Number as a data type for createdAt during index creation. Keeping it as a String failed my query.

I then used a query resolver to check messages of a user in the last 5 seconds:

#set( $messageTimeLimit = 5000 )
#set( $lastNSeconds = $util.time.nowEpochMilliSeconds() - $messageTimeLimit )
{
    "version" : "2018-05-29",
    "operation" : "Query",
    "index" : "userID-createdAt-index",
    "query" : {
      "expression": "userID = :userID and createdAt >= :lastFiveSeconds",
        "expressionValues" : {
          ":lastFiveSeconds" : $util.dynamodb.toDynamoDBJson($lastFiveSeconds),
          ":userID" : $util.dynamodb.toDynamoDBJson($context.arguments.userID)
        }
    }
}

Querying DynamoDB with multiple fields in an AppSync Resolver

2 Answers2