Category: Database

Dynamo DB Filtering

DynamoDb works on mainly HASH(partation) key and RANGE(Sort) key

Two types of index

LSI (Local Secondary Indexes)
– It is tightly attached to structure of the table.

– Should have the same hash key as the table, But will have different range key
GSI (Global Secondary Indexes)
– Is altogether different Hash and Range Key

We can have max 5 LSI and GSI per table.

Searching/ Filtering the records
We can apply the search based on range key
Code snippet to get the filtered record using QuerySpec

Here is the scenario,

We have a table EMPLOYEE and need to get the employee’s whose status is “ACTIVE” and joining date between “2017-01-01” and “2018-01-01”.
On top it filter based on name and no of days column with pagination support.
Dynamo DB has unique way of applying the filtering and it returns max 1MB of data at asuppor
This scenario covers most of the filtering concepts like,

  • With table name
  • With GSI index name
  • Key Conditional expression
  • Filter Expression
  • With name map
  • With value map
  • With reserved key word as column name.
  • Pagination (withExclusiveStartKey)

Code snippet to get the filtered record using QuerySpec, If you are looking for pagination support follow the next section.

String filter_Expression = "name = :v_name and noOfDay = :v_noOfDay";
Map value_Map = new LinkedHashMap();
Map name_Map = new LinkedHashMap();
//Status is a reserved keyword so so we need to create alias name and pass
name_Map.put("#v_status_name", "status");

value_Map.put(":v_startDate", "2017-01-01");
value_Map.put(":v_endDate", "2018-01-01" );
value_Map.put(":v_status", "ACTIVE");
value_Map.put(":v_name", "TEST");
value_Map.put(":v_noOfDay", "10");

QuerySpec spec = new QuerySpec();
spec.withKeyConditionExpression("#v_status_name = :v_status and createdDate between :v_startDate and :v_endDate ");
spec.withFilterExpression(filter_Expression)
.withNameMap(name_Map)
.withValueMap(value_Map);
spec.withNameMap(name_Map).withValueMap(value_Map);

Code snippet to get the filtered record using QueryRequest and QueryResult
It has the advantage of passing the LastEvaluatedKey(used for pagination)

AmazonDynamoDB amazonDynamoDB = AmazonDynamoDBClientBuilder.standard().build();
//One way
String filterExpression = "name = :v_name and noOfDay = :v_noOfDay";
Map<String, String> nameMap = new LinkedHashMap<>();
Map<String, AttributeValue> exclusiveStartKey = new HashMap<>();
Map<String,AttributeValue> expressionAttributeValues = new HashMap<>();
expressionAttributeValues.put(":v_startDate",new AttributeValue().withS("2017-01-01"));
expressionAttributeValues.put(":v_endDate",new AttributeValue().withS("2018-01-01"));
expressionAttributeValues.put(":v_status",new AttributeValue().withS("ACTIVE"));
QueryRequest queryRequest = new QueryRequest()
.withTableName("EMPLOYEE")
.withIndexName("empId")
.withKeyConditionExpression("#v_status_name = :v_status and createdDate between :v_startDate and :v_endDate ")
.withExpressionAttributeNames(nameMap)
.withExpressionAttributeValues(expressionAttributeValues);
queryRequest.withFilterExpression(filterExpression);
//Construct the exclusiveStartKey based on the input you received for pagination from UI
exclusiveStartKey.put("status", new AttributeValue().withS("COMPLETED"));
if (!exclusiveStartKey.isEmpty()) {
queryRequest.withExclusiveStartKey(exclusiveStartKey);
}
QueryResult query = amazonDynamoDB.query(queryRequest);
List<Map<String, AttributeValue>> valueItems = query.getItems();
List<Map<String,Object>> convertedMap = objectConversion(valueItems);
//fasterxml.jackson.databind.ObjectMapper
ObjectMapper objectMapper = new ObjectMapper();
//TODO:convert convertedMap value to custom object model using objectMapper here
// Getting the overall count used by UI for pagination
QueryRequest countQuery = queryRequest.clone();
countQuery.withSelect(Select.COUNT);
QueryResult countResults = amazonDynamoDB.query(countQuery);
int overAllCount = countResults.getCount();
System.out.println("count :" + overAllCount);

private static List<Map<String,Object>> objectConversion(List<Map<String, AttributeValue>> dbValues) {
List<Map<String,Object>> tranformedMap = new ArrayList<>();
dbValues.stream()
.forEach(item -> {
Map<String, Object> map = new HashMap<>();
item.entrySet()
.iterator()
.forEachRemaining(e -> {
AttributeValue value = e.getValue();
Object val = null;
if (value.getS() != null) {
val = value.getS();
} else if (value.getM() != null) {
val = value.getM();
}
map.put(e.getKey(), val);
});
tranformedMap.add(map);
});
return tranformedMap;
}