Querying for DynamoDB items using "contains"

Question

Let's say I have a DynamoDB table such as

TableName: 'Items'
Key: {Hash: 'Id'}

Each item has a name and belongs to a customer, so I also have an Index on

{Hash: CustomerId, Range:Name}

Let's say I have this data:

Id    CustomerId    Name
1     18            Milk
2     42            Orange juice
3     42            Apple juice
4     42            Coffee
5     54            Tomato juice

Now, I want to query for all items for a particular customer and filter the results for partial names (essentially a search operation). For example, give me all items belonging to customer 42 that contains "juice" in its name (Orange juice and Apple juice are expected results).

If I query for CustomerId = '42' AND contains(Name, 'juice') I will get an error saying that KeyConditionExpression doesn't support contains. I can sort of understand this limitation since contains means having to scan all items (within the given hash key), but, well, you can query for all items with CustomerId = '42' which is also a full scan within that hash, so I'm not sure I understand this limitation. Things like begins_with are supported just as expected (which makes sense since it's easy to quickly return a subset from a sorted set).

Anyway, so I curse a little bit and say I'll just use a FilterExpression instead and live with the wasted RCU:s, resulting in a query with

KeyConditionExpression: CustomerId = '42'
FilterExpression: contains(Name, 'juice')

But now I get an error saying that I am not allowed to include primary key attributes in my FilterExpression ("use KeyConditionExpression instead!").

This leaves me in somewhat of a dilemma. I can't filter with contains in my KeyCondition and I can't filter on Name in my FilterExpression. Should I have to create a separate index only on CustomerId in order to implement my use-case or is there any other way to solve this...?

Answer 1

DynamoDB only allow begin_with() for key conditions. So contains() is not supported, but for your case it is possible to arrange the rangeKey in hierarchical order like:

CustomerId    Name
18            Milk
42            juice.Orange 
42            juice.Apple
42            Coffee
54            Tomato juice

So the query can be structured like

KeyConditionExpression: CustomerId = '42' AND Name BEGINS_WITH 'juice'

---

NOTE:

• The only way to use contains() seems to be in filter expressions, and filter expressions may only operate on non-keys.