Improved Query Filtering and Conditional Updates for DynamoDB

Amazon DynamoDB is a fast, fully-managed NoSQL database. You can easily create tables, provision the desired amount of read and write capacity, and then store as much information as you’d like. Each item in a DynamoDB table consists of one or more key/value pairs, indexed by a hash key or a combination of a hash key and a range key (read more about the DynamoDB data model), with additional indexing options in the form of Local and Global Secondary Indexes.

NoSQL databases like DynamoDB are designed for scalability and consistent, high performance. This stands in marked contrast to the traditional relational database model where these attributes are not always as important as flexibility and generality.

Today we are making DynamoDB even more flexible with additional options for filtering query results and for updating existing items. Let’s take a look!

Query Filtering
DynamoDB’s Query function retrieves items using a primary key or an index key from a Local or Global Secondary Index. Each query can use Boolean comparison operators to control which items will be returned.

With today’s release, we are extending this model with support for query filtering on non-key attributes. You can now include a QueryFilter as part of a call to the Query function. The filter is applied after the key-based retrieval and before the results are returned to you. Filtering in this manner can reduce the amount of data returned to your application while also simplifying and streamlining your code.

The QueryFilter that you pass to the Query API must include one or more conditions. Each condition references an attribute name and includes one or more attribute values, along with a comparison operator. In addition to the usual Boolean comparison operators, you can also use CONTAINS, NOT_CONTAINS, and BEGINS_WITH for string matching, BETWEEN for range checking,  and IN to check for membership in a set.

In addition to the QueryFilter, you can also supply a ConditionalOperator. This logical operator (either AND or OR) is used to connect each of the elements in the QueryFilter.

Updating Items
DynamoDB’s PutItem, UpdateItem, and DeleteItem functions can optionally perform a conditional update of an item. This feature allows two or more processes to make concurrent updates to a particular item in a controlled fashion. Let’s say you are using a couple of DynamoDB tables to track real-time data that is flowing in from a sensor network. You can use the UpdateItem function to implement a highly reliable counting system in a scalable way. Your code would do the following:

1. Call GetItem to read the item.
2. Extract the count field from the item and increment it by 1.
3. Call UpdateItem with the current and the new values of the count field.
4. Return to step 1 if UpdateItem indicates that the current value is incorrect.

With today’s release, you have additional options to specify the condition that must hold for the item to be replaced (put), updated, or deleted. You can also specify multiple conditions, connected by AND or OR.

For example, you could subtract an amount from a bank balance (indicating a withdrawal), only if the balance is greater than or equal to the amount of the withdrawal.

The AWS SDKs for .NET, Ruby, Java, and JavaScript have been updated and you can start using these powerful new features today. We plan to update the Python and PHP SDKs in the next couple of days.

DynamoDB Local has also been updated and you can download it now.

— Jeff;