27

I try to query my table Tinpon with a secondary index yielding a partition-key category and sort-key tinponId. My goal is to exclude items with certain tinponIds. My first thought would be to make a negative compare: keyConditionExpression = "category = :category AND tinponId != :tinponId" but there is only a equal = comparison. Then I tried serval other methods (with sadly do not exist): keyConditionExpression = "category = :category NOT tinponId = :tinponId" keyConditionExpression = "category = :category AND tinponId <> :tinponId" keyConditionExpression = "category = :category AND tinponId < :tinponId AND tinponId > :tinponId" Following the AWS guide there is no not equal comparisson. Why so? And is there a way to query DynamoDB excluding a list of ids or is the only option to retrieve a whole bunch of items and filter them later manually?

Sidharth Ramesh
  • 646
  • 2
  • 6
  • 21
DrDirk
  • 1,937
  • 3
  • 25
  • 36

1 Answers1

64

The KeyConditionExpression doesn't allow not equals for the sort key. However, you can use the "Not Equals i.e. <>" in FilterExpression.

KeyConditionExpression : 'category = :category',    
FilterExpression : 'tinponId  <> :tinponIdVal',
ExpressionAttributeValues : {
    ':category' : 'somevalue',
    ':tinponIdVal' :  'somevalue'
}
notionquest
  • 37,595
  • 6
  • 111
  • 105
  • 2
    Ok my next problem is that I have chosen **tinponId** as the primary key of my **Tinpon** table and now get the error `Filter Expression can only contain non-primary key attributes` is there another solution, or do I have to change the design of my table to use your suggested filtering method? – DrDirk Jul 09 '17 at 16:03
  • 2
    You have to use GSI (and potentially query GSI which has different partition key) or change the design of the main table to have different partition key. – notionquest Jul 09 '17 at 16:12
  • @notionquest for this answer are you user query or scan operation. – Ashish Kadam Aug 31 '18 at 07:01
  • Thank u V.Much... :) – Anandan K Jan 18 '19 at 04:27
  • This is not a useful answer, because you cannot apply filter expressions on the sort key. – toaster May 11 '23 at 12:41