Using contains filter in DynamoDB scan with Java

Question

Suppose I have a working query such as:

ScanRequest scanRequest = new ScanRequest()
            .withTableName("myTable")
            .withFilterExpression("attr1 = :val1 and attr2 = :val2")
            .withExpressionAttributeValues(vm)  //contains values for :val1 and :val2
            .withLimit(PAGE_SIZE)
            .withConsistentRead(READ_TYPE);

Now I would like to extend this scan. Suppose my table also has an attribute attr3 which has the form:

"attr3": {
    "S": "AAA BBB CCC DDD"
}

How can I filter elements who's attr3 contains AAA? Or AAA and BBB?

Boni

Answer 1

The DynamoDB Condition Expressions Reference documentation is your friend!

In your specific case you can use the contains function to search for a sub-string. Your filter expression might look something like this:

"attr1 = :val1 and attr2 = :val2 and (contains(attr3, :val3a) or contains(attr3, :val3b))"             
// where :val3a and :val3b are value placeholders for say AAA and BBB

But I suspect that what you want to achieve is a bit more sophisticated than can be handled server-side by DynamoDB filters, so you have two options:

1. do the filtering in the application logic (ie. bring down your results to the client and filter there), or;
2. change your attribute from a string to a list, or string set (if duplicates not allowed)

In either case, you should know that a scan with filters is only more efficient than a regular scan in terms of network band-with (in the case when most results are excluded by the filter). But in terms of capacity consumed a Scan is equally expensive with or without filters. So if you can avoid it, don't rely on scanning your table too much!

Anibalanica

Thanks. With regard to your last comment, supposing you want to search your database according to arbitrary fields, is there any alternative to a scan with filters? – Boni

You could create a global secondary index, but you should carefully consider your use cases and don't try to use DynamoDB like a relational database because it's not. – Anibalanica

The contains operator is described in Comparison Operator and Function Reference – Clarey

Are the operators case insensitive? I see in the docs they use OR and AND. So wondering. – Braces

Yeah, IIRC the operators are case insensitive – Anibalanica