DynamoDB Java SDK How to query on a String field using NOT EQUALS

Question

It doesn't look like there is a "NOT EQUALS" comparison operator in the QuerySpec withKeyConditionExpression or withFilterExpression. I'm trying to do something like:

new QuerySpec()
    .withKeyConditionExpression("#name <> :var_name AND #date > :var_date")
    .withNameMap(new NameMap()
        .with("#name", "name")
        .with("#date", "date"))
    .withValueMap(new ValueMap()
        .withString(":var_name","Unknown")
        .withString(":var_date", thirtyDaysAgo));

Where name is not equal to the String "Unknown".

But, I get "Invalid operator used in KeyConditionExpress: <>.

Is there a way around this (other than excluding the condition from the query and iterating across the results in memory)?

I found the following documentation which references a not equal operator but it looks like it's not available in query filter.

http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html

Making Comparisons

Use these comparators to compare an operand against a range of values, or an enumerated list of values:

a = b — true if a is equal to b
a <> b — true if a is not equal to b
a < b — true if a is less than b
a <= b — true if a is less than or equal to b
a > b — true if a is greater than b
a >= b — true if a is greater than or equal to b

http://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Condition.html

For a Query operation, Condition is used for specifying the KeyConditions to use when querying a table or an index. For KeyConditions, only the following comparison operators are supported:

EQ | LE | LT | GE | GT | BEGINS_WITH | BETWEEN

Condition is also used in a QueryFilter, which evaluates the query results and returns only the desired values.

 NE : Not equal. NE is supported for all data types, including lists and maps.

AttributeValueList can contain only one AttributeValue of type String, Number, Binary, String Set, Number Set, or Binary Set. If an item contains an AttributeValue of a different type than the one provided in the request, the value does not match. For example, {"S":"6"} does not equal {"N":"6"}. Also, {"N":"6"} does not equal {"NS":["6", "2", "1"]}.

F_SO_K F_SO_K · Accepted Answer · 2017-11-30T10:07:07

If you use a KeyConditionExpression, you have to specify a single partition key. Documentation includes the following:

The [KeyConditionExpression] condition must perform an equality test on a single partition key value

The partition key equality test is required, and must be specified in the following format:

partitionKeyName = :partitionkeyval

You can still do the query but use a Scan operation rather than a 'Query'. By default Scan returns all items in a table, so you use your operators in a filter expression.

EDIT:

Map<String, String> attributeNames = new HashMap<String, String >();
attributeNames.put("#name", "name");
attributeNames.put("#date", "date");
 
Map<String, AttributeValue> attributeValues = new HashMap<String, AttributeValue>();
attributeValues.put(":var_date", new AttributeValue().withN(thirtyDaysAgo));
attributeValues.put(":var_name", new AttributeValue().withS("Unknown"));
 
ScanRequest scanRequest = new ScanRequest()
        .withTableName(TABLE_NAME)
        .withFilterExpression("#name <> :var_name AND #date > :var_date")
        .withExpressionAttributeNames(attributeNames)
        .withExpressionAttributeValues(attributeValues)

DynamoDB Java SDK How to query on a String field using NOT EQUALS

1 Answers