Writing dynamoDB "OR" condition query?

Question

I want to query the dynamodb table with boolean or condition like SQL e.g. Get me all the items where attribute1 = "no" or attribute2="no"

I tried with scanRequest.withScanFilter but all the conditions are performed by doing boolean ANDing. How do I do boolean ORing.?

score 8 · Accepted Answer · edited Apr 29 '19 at 11:42

You can set ConditionalOperator of your ScanRequest to "OR". The default value is "AND"

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

ScanRequest scanRequest = new ScanRequest("tableName");
scanRequest.setConditionalOperator(ConditionalOperator.OR);

Map<String, Condition> scanFilter = new HashMap<String, Condition>();
scanFilter.put("attribute1", new Condition().withAttributeValueList(new AttributeValue("no")).withComparisonOperator(ComparisonOperator.EQ));
scanFilter.put("attribute2", new Condition().withAttributeValueList(new AttributeValue("no")).withComparisonOperator(ComparisonOperator.EQ));

scanRequest.setScanFilter(scanFilter);
ScanResult scanResult = dynamo.scan(scanRequest);

for(Map<String, AttributeValue> item : scanResult.getItems()) {
    System.out.println(item);
}

Be careful using scans. You'll be charged for all rows scanned, not the rows returned. You really should just never use scans in production if the table contains a lot of data. — Aneil Mallavarapu, Jun 30 '18 at 13:34
`ScanRequest` does not contain a definition for `setConditionalOperator` — Kok How Teh, Mar 20 '23 at 02:53

score 5 · Answer 2 · edited Apr 29 '19 at 10:57

If you happen to know the HashKey value, another option would be to use QUERY and a FilterExpression. Here is an example with the Java SDK:

Table table = dynamoDB.getTable(tableName);

Map<String, Object> expressionAttributeValues = new HashMap<String, Object>();
expressionAttributeValues.put(":x", "no");
expressionAttributeValues.put(":y", "no");

QuerySpec spec = new QuerySpec()
    .withHashKey("HashKeyAttributeName", "HashKeyValueHere")
    .withFilterExpression("attribute1 = :x  or attribute2 = :y")
    .withValueMap(expressionAttributeValues);


ItemCollection<QueryOutcome> items = table.query(spec);

Iterator<Item> iterator = items.iterator();

while (iterator.hasNext()) {
    System.out.println(iterator.next().toJSONPretty());
}

See Specifying Conditions with Condition Expressions for more details.

Note that filter expressions operate on the query results, and you're charged on the size of the query. So if you have a large amount of data associated with each hashKey, you'll be paying through the nose every time you run the filter expression. — Aneil Mallavarapu, Jun 30 '18 at 13:38

score 5 · Answer 3 · edited Dec 09 '20 at 10:29

You can also use brackets in a FilterExpression:

const params = {
  TableName: process.env.PROJECTS_TABLE,
  IndexName: 'teamId-createdAt-index',
  KeyConditionExpression: 'teamId = :teamId',
  ExpressionAttributeValues: {
    ':teamId': verifiedJwt.teamId,
    ':userId': verifiedJwt.userId,
    ':provider': verifiedJwt.provider
  },
  FilterExpression: 'attribute_exists(isNotDeleted) and ((attribute_not_exists(isPrivate)) or (attribute_exists(isPrivate) and userId = :userId and provider = :provider))'
};

Writing dynamoDB "OR" condition query?

3 Answers3

Linked