DynamoDb givies error "Filter Expression can only contain non-primary key attributes"

Question

I am using dynamoDb via dynamoose and trying to use filtering and sorting.

I had secondary Index as Partition key (Organization id) and sort key as (First name).

I want to sort with same column as well wants to filter with same column, I am using below query for:

users.query("organization_id").eq(user_organization_id).filter('first_name').conatins(first_name).sort('ascending').using('organization_id_first_name_index').exec();

Its gives me error

Filter Expression can only contain non-primary key attributes: Primary key attribute: first_name

Expected result is flitering and sorting will run smoothly

Answer 1

DynamoDB's Query operation has two distinct parameters - KeyConditionExpression and FilterExpression. The former is an efficient mechnism which can jump directly to the required keys, and the latter is a filter which goes over all the results returnd by KeyConditionExpression (you pay for reading all of them!) and decide for each of them whether or not it passes the filter.

As DynamoDB told you, FilterExpression doesn't want to work on the sort key, because it will be much more efficient for you to pick the sort key in the KeyConditionExpression.

You could argue that you can't do the "contains()" with a KeyConditionExpression but this shows that your data model is suboptimal - there is no way to implement your contains() request without reading the entire partition. Are you sure you really wanted to check contains() and not eq()?

Answer 2

DynamoDB sorts by ascending order of the sort key, so there is no need to address the sorting aspect.

For the FilterExpression, for key attributes you use KeyConditionExpression as its purpose is to target a table or indexes keys and is more efficient than a FilterExpression.

contains is not something you can execute on a sort key, it must be equal, begins_with, greater_than, less_than and perhaps a few more, but the main point is that they all know the start of the sort key. Notice you cannot do ends_with. If you read into how a B-tree works you'll understand the logic.

users.query("organization_id").eq(user_organization_id).filter('first_name').eq(first_name).sort('ascending').using('organization_id_first_name_index').exec();