DynamoDB “OR” conditional Range query

Question

Let's assume my table looks like:

Code    |StartDate  |EndDate    |Additional Attributes...

ABC     |11-24-2015 |11-26-2015 | ....

ABC     |12-12-2015 |12-15-2015 | ....

ABC     |10-05-2015 |10-10-2015 | ....

PQR     |03-24-2015 |03-27-2015 | ....

PQR     |05-04-2015 |05-08-2015 | ....

Provided a Code (c) and a date range (x, y), I need to be able to query items something like:

Query => (Code = c) AND ((StartDate BETWEEN x AND y) OR (EndDate BETWEEN x AND y))

I was planning to use a Primary Key as a Hash and Range Key (Code, StartDate) with an additional LSI (EndDate) and do a query on it. I am not sure if there is a way to achieve this. I don't want to use the SCAN operation as it seems to scan the entire table which could be very costly.

Also, would like to achieve this in a single query.

Are your dates stored in that format? 05-04-2015 ? There are much better ways to store unambiguous dates, as 05-04-2015 can be interpreted as May 04th 2015 or April 5th 2015. — mkobit

b-s-d b-s-d · Accepted Answer · 2015-04-26T18:01:25

One option would be to do this using QUERY and a FilterExpression. No need to define the LSI on this case. You would have to query by Hash Key with the EQ operator and then narrow the results with the Filter Expression. Here is an example with the Java SDK:

Table table = dynamoDB.getTable(tableName);

Map<String, Object> expressionAttributeValues = new HashMap<String, Object>();
expressionAttributeValues.put(":x", "11-24-2015");
expressionAttributeValues.put(":y", "11-26-2015");

QuerySpec spec = new QuerySpec()
    .withHashKey("Code", "CodeValueHere")
    .withFilterExpression("(StartDate between :x and :y) or (EndDate between :x and :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.

Additionally, although the previous query only uses the Hash Key , you can still group the records with the Range Key containing the dates in the following format:

StartDate#EndDate

Table Structure:

Code    DateRange             |StartDate  |EndDate        
ABC     11-24-2015#11-26-2015 |11-24-2015 |11-26-2015  

ABC     12-12-2015#12-15-2015 |12-12-2015 |12-15-2015

ABC     10-05-2015#10-10-2015 |10-05-2015 |10-10-2015

PQR     03-24-2015#03-27-2015 |03-24-2015 |03-27-2015

PQR     05-04-2015#05-08-2015 |05-04-2015 |05-08-2015

This way If you happen to query only by Hash Key you would still get the records sorted by the dates. Also, I believe it is a good idea to follow the advice given about the unambiguous date format.nu

DynamoDB “OR” conditional Range query

1 Answers