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
• 19,040 points
1,222 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
• 19,040 points
197 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
163 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP