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, 2022 in Others by Kichu
• 19,050 points
2,708 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, 2022 in Others by Kichu
• 19,050 points
585 views
0 votes
1 answer

How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,420 points
281 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 63,420 points
1,739 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