DynamoDB Complex Queries Cheat Sheet
This is an article on Amazon DynamoDB complex queries and it builds upon DynamoDB basic queries.
(This tutorial is part of our DynamoDB Guide. Use the right-hand menu to navigate.)
Remember the basic rules for querying in DynamoDB:
You can apply a DynamoDB filter expression after a query finishes, but before it returns results. That way, your query consumes the same amount of read capacity, which is limited to 1 MB of data. DynamoDB filter expressions do not contain partition keys or sort key attributes—those belong in the key condition expression. The syntax, however, is like a key condition expression, using the same comparators, functions and logical operators.
Use a key condition expression, a specification of the items to be read from a table or index, to indicate search criteria. You will need to include the partition key name and value as an equality condition. You also have the option of using a second sort key condition. You cannot, however, use non-key attributes.
In writing key condition expressions, you can use the following comparison operators:
a = b — true if the attribute a is equal to the value b.
a < b — true if a is less than b.
a <= b — true if a is less than or equal to b.
a > b — true if a is greater than b.
a >= b — true if a is greater than or equal to b.
a BETWEEN b AND c — true if a is greater than or equal to b, and less than or equal to c.
You can also use this function:
begins_with (a, substr)— true if the value of attribute a begins with a particular substring.
To perform these complex DynamoDB queries, we need some data to work with. Download this sample data from GitHub, which is data from IMDB that I’ve slightly modified.
In this document we are using DynamoDB on a local machine. So, we specify –endpoint-url http://localhost:8000.
Create the title table like this:
aws dynamodb create-table --endpoint-url http://localhost:8000 --table-name title --attribute-definitions AttributeName=tconst,AttributeType=S AttributeName=primaryTitle,AttributeType=S --key-schema AttributeName=tconst,KeyType=HASH AttributeName=primaryTitle,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5
Notice that the primary key is the combination of the attributes tconst (partition key) and primaryTitle (sort key).
For our sample data we have data like shown below. All the partition keys are set to the same value movie. Then the movie primaryTitle is the sort key.
{
"tconst": {
"S": "movie"
},
"primaryTitle": {
"S": "Travel Daze"
},
Then load the data like this:
aws dynamodb batch-write-item --endpoint-url http://localhost:8000 --request-items file:////Users/walkerrowe/Documents/imdb/movies.json --return-consumed-capacity TOTAL --return-item-collection-metrics SIZE
Below is a DynamoDB between query example. We use the first (space) and last (ÿ) characters in the UTF-8 character set to select all titles.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst and primaryTitle BETWEEN :fromTitle AND :toTitle"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":fromTitle":{"S":" "},
":toTitle":{"S":"ÿ"}
}'
A DynamoDB begins_with query example is below.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst and begins_with(primaryTitle, :beginsWith)"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":beginsWith":{"S":"A"}
}'
In the DynamoDB contains query example below, we write a filter expression instead of a key condition. This is just to show how to write a DynamoDB filter expression, as opposed to a key condition. As we mentioned above, the operators are the same, except you cannot use the operator contains as a key condition.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression 'contains(originalTitle, :containsStr)'
--expression-attribute-values '{
":tconst":{"S":"movie"},
":containsStr":{"S":"Brooklyn"}
}'
To find out whether an attribute exists, use the DynamoDB attribute exists query:
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression 'attribute_exists(genres)'
--expression-attribute-values '{
":tconst":{"S":"movie"}
}'
To verify that an attribute doesn’t exist, use the DynamoDB attribute not exists query:
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression 'attribute_not_exists(genres)'
--expression-attribute-values '{
":tconst":{"S":"movie"}
}'
To test a value for membership in a set, you can write an IN query or IN condition. The syntax of arguments includes a numeric, character, or datetime expression, with an expr_ist of parenthesis-bound, comma-delimited expressions, a table subquery, and, of course, an IN or NOT IN query.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "genres IN (:inDrama, :inComedy)"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":inDrama":{"S":"Drama"},
":inComedy":{"S":"Comedy"}
}'
A string set refers to specific attributes attached to items in a set of data. If you want to query items in a table of data, for example, to find all people in a table that are identified as “actors,” you would write a string set query. Our query contains data like this:
"actors": {
"SS":
},
So, an equality condition on that string set (SS) element would necessarily contain all those strings.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "actors = :actors"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":actors":{"SS": }
}'
Boolean data is stored like this:
"isComedy": {
"BOOL": false
}
So, you query it like this:
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "isComedy = :isComedy"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":isComedy":{"BOOL":true}
}'
The DynamoDB map query is similar to the nested query (see the next item).
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "aditionalInfo = :aditionalInfo"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":aditionalInfo": {
"M": {"Location": {"S": "Bost"}, "Language": {"S": "FR"}}
}
}'
A nested DynamoDB object is one that contains a map. You refer to the element using the dot notation parent.child, like for this data you would write aditionalInfo.Location.
"aditionalInfo": {
"M": {"Location": {"S": "Bost"}, "Language": {"S": "FR"}}
},
Location is a reserved word, so we have to give it an alias using:
--expression-attribute-names '{"#loc": "Location"}'
And here is the nested DynamoDB query example:
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "aditionalInfo.#loc = :loc"
--expression-attribute-names '{"#loc": "Location"}'
--expression-attribute-values '{
":tconst":{"S":"movie"},
":loc": {"S": "Bost"}
}'
When you want to read some attributes versus all attributes in a table, you can use a projection expression. It allows you to specify the items you want, according to attributes that you specify by name. You can specify multiple attributes if you separate the attribute names with a comma.
Use this projection expression to limit the attributes returned by DynamoDB, as it returns all attributes by default.
aws dynamodb query
--endpoint-url http://localhost:8000
--table-name title
--key-condition-expression "tconst = :tconst"
--filter-expression "aditionalInfo = :aditionalInfo"
--expression-attribute-values '{
":tconst":{"S":"movie"},
":aditionalInfo": {
"M": {"Location": {"S": "Bost"}, "Language": {"S": "FR"}}
}
}'
--projection-expression "originalTitle, runtimeMinutes"
For more on this topic, explore the BMC Big Data & Machine Learning Blog or check out these resources: