DynamoDB How to query using GSI when you know the partition key but not the sort key

0 votes

My table is set up like this:

 PK  |  SK  | CreatedAt (DateTime)(Secondary Global Index) | Random Attributes
USER | 0xFF | Today                                        | 1
USER | 0xFE | Yesterday                                    | 2

So my partition key is a general group name for the objects. "USER" just tells me it's a user object. Later I will have "ITEM" or other random objects, or links between objects for quick queries. The sort key is the actual ID of the user which is randomly generated (uuid). The secondary global index is the "CreatedAt" which is a DateTime object.

I just want to find the latest user (I only really care about the SK).

I tried this;

QueryRequest qr = new QueryRequest
{
    TableName = "mytable",
    IndexName = "CreatedAt-index",
    ScanIndexForward = true,
    KeyConditionExpression = "PK = :v_pk",
    FilterExpression = "CreatedAt BETWEEN :v_timeOldest AND :v_timeNow",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
    {
        {":v_pk", new AttributeValue{S = "USER" } },
        {":v_timeNow", new AttributeValue{S = DateTime.Now.ToString(AWSSDKUtils.ISO8601DateFormat)} },
        {":v_timeOldest", new AttributeValue{S = DateTime.Now.AddYears(-1).ToString(AWSSDKUtils.ISO8601DateFormat)} }
    },
    Limit = 1
};

QueryResponse res = await _client.QueryAsync(qr);

The error is;

Query key condition not supported

I assume it's because it expects CreatedAt to be the partition key, and it needs to be unique so it can't have a BETWEEN comparison. But that isn't useful for this case due to duplicate dates for many objects and the fact that it can be between other dates.

I tried it the other way where the KeyCondition is the CreatedAt statement and the FilterExpression is the PK but the same error.

What I expect is to just gather all objects with the partition key "USER" and then with that sort ascending/descending based on the GSI partition key and pick the one at the top of the list.

What am I missing here? Is my understanding of the GSI conceptually flawed?

Apr 5 in Others by Kichu
• 16,250 points
36 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5 in Others by Kichu
• 16,250 points
34 views
0 votes
1 answer
0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,240 points
3,867 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
614 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
175 views
0 votes
0 answers

Write a DAX query to obtain the bottom 5 customers based on the order price

Jan 22, 2020 in Others by anonymous
• 150 points
143 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP