Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

AWS DynamoDB - combining multiple query filters on a single non-key attribute in java

Earlier this year Amazon announced support of query filters on non-key attributes.

Can conditions be combined on a single attribute value? For example, in this scenario I would like to retrieve all items which do not match a certain list of values in a single 'non-key' column.

Their documentation states that each condition can only hold one attribute value for comparisons like NOT_EQUALS or BEGINS_WITH. The following therefore does not work:

HashMap<String, Condition> queryFilter = new HashMap<String, Condition>();

List<AttributeValue> AttributeValues = new ArrayList<AttributeValue>();
AttributeValues.add(new AttributeValue().withS("someValue1"));
AttributeValues.add(new AttributeValue().withS("someValue2"));

Condition attributeCondition = new Condition()
    .withComparisonOperator(ComparisonOperator.NE)
    .withAttributeValueList(AttributeValues);

queryFilter.put("COLUMN_1", attributeCondition);

DynamoDBQueryExpression<Item> queryExpression = new DynamoDBQueryExpression<Item>()
    .withHashKeyValues(itemKey)
    .withQueryFilter(queryFilter)
    .withLimit(pPageSize);

It looks like only the IN comparison operator can hold a list of attribute values. Ideally these conditions should be chainable? Since the query filter is a hash map we cannot put multiple conditions on the same column (I've tried):

Condition c1 = new Condition()
    .withAttributeValueList(new AttributeValue().withS("someValue1"))
    .withComparisonOperator(ComparisonOperator.NE);

Condition c2 = new Condition()
    .withAttributeValueList(new AttributeValue().withS("someValue2"))
    .withComparisonOperator(ComparisonOperator.NE);

DynamoDBQueryExpression<Item> queryExpression = new DynamoDBQueryExpression<Item>()
    .withHashKeyValues(itemKey)
    .withConditionalOperator(ConditionalOperator.AND)
    .withQueryFilterEntry("COLUMN_1", c1)
    .withQueryFilterEntry("COLUMN_1", c2)
    .withLimit(pPageSize);

Any help or clarification would be greatly appreciated!

Thanks

like image 766
ben.bourdin Avatar asked Nov 25 '14 20:11

ben.bourdin


People also ask

Can you Query without sort key DynamoDB?

Hash key in DynamoDB The primary reason for that complexity is that you cannot query DynamoDB without the hash key. So, it's not allowed to query the entire database. That means you cannot do what you would call a full table scan in other databases.

What is FilterExpression in DynamoDB?

A filter expression determines which items within the Query results should be returned to you. All of the other results are discarded. A filter expression is applied after a Query finishes, but before the results are returned.

Can DynamoDB have multiple hash keys?

Querying is a very powerful operation in DynamoDB. It allows you to select multiple Items that have the same partition ("HASH") key but different sort ("RANGE") keys.

Does DynamoDB support complex queries?

DynamoDB does support the complex condition on FilterExpression . Perfectly fine.


1 Answers

So turns out this is made possible by adding a FilterExpression to the query (introduced recently in this blog post)

I had seen this in the DynamoDB documentation but hadn't upgraded to the latest AWS Java SDK :(

Using my above example this would look like:

Map<String, AttributeValue> expressionAttributeValues = new HashMap<String, AttributeValue>();
expressionAttributeValues.put(":val1", new AttributeValue().withS("someValue1"));
expressionAttributeValues.put(":val2", new AttributeValue().withS("someValue2"));

DynamoDBQueryExpression<Item> queryExpression = new DynamoDBQueryExpression<Item>()
    .withHashKeyValues(itemKey)
    .withFilterExpression("COLUMN_1 <> :val1 AND COLUMN_1 <> :val2")
    .withExpressionAttributeValues(expressionAttributeValues)
    .withLimit(pPageSize);

Thanks AWS Support!

like image 110
ben.bourdin Avatar answered Oct 18 '22 04:10

ben.bourdin