DynamoDB filter items not contains

Question

I have the following problem:

Partition key (pk) and Sort key (sk):

pk   sk
1    ITEMS#1
1    ORDERS#1

2    ITEMS#1
3    ITEMS#2

How can I retrieve all pk's that does not contain orders? I have tried the filter:

sk not contains "ORDERS"

But that returns

pk   sk
1    ITEMS#1
2    ITEMS#1
3    ITEMS#2

Where I only want to return pk 2 and 3.

Answer 1

To do this efficiently, you'll have to pre-materialize the orders-or-not fact into your data and structure things in such a way that the fact is appropriately indexed and ready to use.

For example, you can create an item under each PK with an SK of META for metadata about that PK, and on that item you can have an attribute of HasOrders that's present if it has any orders in that item collection. Then you can create a GSI using that as the GSI partition key and very efficiently find all items that have orders with a Query.

You'll have to update the META every time someone places their first order.