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

Question

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 same error.

What I expect is to just gather all objects with the partition key "USER" and then with those 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?

Answer 1

From the docs for KeyConditionExpression

You must specify the partition key name and value as an equality condition. You can optionally provide a second condition for the sort key (if present).

In this specific query, you are trying to query the CreatedAt-index index and specifying PK in the KeyConditionExpression. However, your index uses CreatedAt as the primary key, not the attribute named PK. You said

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

You didn't show the query, but it sounds like you did this

    ...
    KeyConditionExpression = "CreatedAt BETWEEN :v_timeOldest AND :v_timeNow",
    FilterExpression = "PK = :v_pk",
    ...

which will not work because (from the docs) you must specify the partition key name and value as an equality condition. In other words, you can't do a range query on a partition key.

You don't show how you've defined the secondary index, but it sounds like you've defined a partition key using createdAt with no sort key on the index. Logically, that would look like this:

This will not help you very much, since you cannot perform a range query on a partition key.

However, if you were to define a secondary index with a partition key of USER and a sort key of createdAt. That index would look like this

This index would allow you to perform a query operation as you describe.

As an aside, the base table you've described does not sound very useful. You are storing all users in a single partition with a UUID as the sort key. The sort order of UUIDs doesn't sound particularly useful to your application. You may want to consider KSUIDs for your user id's. KSUIDs have the useful feature of being unique (like a UUID) and sortable by creation time. This is particularly useful when you need a unique identifier and need to order them by date created.