# Using expressions in DynamoDB
In Amazon DynamoDB, you can use *expressions* to specify which attributes to read from an item, write data when a condition is met, specify how to update an item, define queries and filter the results of a query.
This table describes the basic expression grammar and the available kinds of expressions.
| Expression type | Description |
| --- | --- |
| Projection expression | A projection expression identifies the attributes that you want to retrieve from an item when you use operations such as GetItem, Query, or Scan. |
| Condition expression | A condition expression determines which items should be modified when you use the PutItem, UpdateItem, and DeleteItem operations. |
| Update expression | An update expression specifies how UpdateItem will modify the attributes of an item— for example, setting a scalar value or removing elements from a list or a map. |
| Key condition expression | A key condition expression determines which items a query will read from a table or index. |
| Filter expression | A filter expression determines which items among the Query results should be returned to you. All the other results are discarded. |
For information about expression syntax and more detailed information about each type of expression, see the following sections.
**Topics**
+ [Referring to item attributes when using expressions in DynamoDB](Expressions.Attributes.md)
+ [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md)
+ [Using expression attribute values in DynamoDB](Expressions.ExpressionAttributeValues.md)
+ [Using projection expressions in DynamoDB](Expressions.ProjectionExpressions.md)
+ [Using update expressions in DynamoDB](Expressions.UpdateExpressions.md)
+ [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md)
+ [DynamoDB condition expression CLI example](Expressions.ConditionExpressions.md)
**Note**
For backward compatibility, DynamoDB also supports conditional parameters that do not use expressions. For more information, see [Legacy DynamoDB conditional parameters](LegacyConditionalParameters.md).
New applications should use expressions rather than the legacy parameters.
# Referring to item attributes when using expressions in DynamoDB
This section describes how to refer to item attributes in an expression in Amazon DynamoDB. You can work with any attribute, even if it is deeply nested within multiple lists and maps.
**Topics**
+ [Top-level attributes](#Expressions.Attributes.TopLevelAttributes)
+ [Nested attributes](#Expressions.Attributes.NestedAttributes)
+ [Document paths](#Expressions.Attributes.NestedElements.DocumentPathExamples)
**A Sample Item: ProductCatalog**
The examples on this page use the following sample item in the `ProductCatalog` table. (This table is described in [Example tables and data for use in DynamoDB](AppendixSampleTables.md).)
```
{
"Id": 123,
"Title": "Bicycle 123",
"Description": "123 description",
"BicycleType": "Hybrid",
"Brand": "Brand-Company C",
"Price": 500,
"Color": ["Red", "Black"],
"ProductCategory": "Bicycle",
"InStock": true,
"QuantityOnHand": null,
"RelatedItems": [
341,
472,
649
],
"Pictures": {
"FrontView": "http://example.com/products/123_front.jpg",
"RearView": "http://example.com/products/123_rear.jpg",
"SideView": "http://example.com/products/123_left_side.jpg"
},
"ProductReviews": {
"FiveStar": [
"Excellent! Can't recommend it highly enough! Buy it!",
"Do yourself a favor and buy this."
],
"OneStar": [
"Terrible product! Do not buy this."
]
},
"Comment": "This product sells out quickly during the summer",
"Safety.Warning": "Always wear a helmet"
}
```
Note the following:
+ The partition key value (`Id`) is `123`. There is no sort key.
+ Most of the attributes have scalar data types, such as `String`, `Number`, `Boolean`, and `Null`.
+ One attribute (`Color`) is a `String Set`.
+ The following attributes are document data types:
+ A list of `RelatedItems`. Each element is an `Id` for a related product.
+ A map of `Pictures`. Each element is a short description of a picture, along with a URL for the corresponding image file.
+ A map of `ProductReviews`. Each element represents a rating and a list of reviews corresponding to that rating. Initially, this map is populated with five-star and one-star reviews.
## Top-level attributes
An attribute is said to be *top level* if it is not embedded within another attribute. For the `ProductCatalog` item, the top-level attributes are as follows:
+ `Id`
+ `Title`
+ `Description`
+ `BicycleType`
+ `Brand`
+ `Price`
+ `Color`
+ `ProductCategory`
+ `InStock`
+ `QuantityOnHand`
+ `RelatedItems`
+ `Pictures`
+ `ProductReviews`
+ `Comment`
+ `Safety.Warning`
All of these top-level attributes are scalars, except for `Color` (list), `RelatedItems` (list), `Pictures` (map), and `ProductReviews` (map).
## Nested attributes
An attribute is said to be *nested* if it is embedded within another attribute. To access a nested attribute, you use *dereference operators*:
+ `[n]` — for list elements
+ `.` (dot) — for map elements
### Accessing list elements
The dereference operator for a list element is **[*N*]**, where *n* is the element number. List elements are zero-based, so [0] represents the first element in the list, [1] represents the second, and so on. Here are some examples:
+ `MyList[0]`
+ `AnotherList[12]`
+ `ThisList[5][11]`
The element `ThisList[5]` is itself a nested list. Therefore, `ThisList[5][11]` refers to the 12th element in that list.
The number within the square brackets must be a non-negative integer. Therefore, the following expressions are not valid:
+ `MyList[-1]`
+ `MyList[0.4]`
### Accessing map elements
The dereference operator for a map element is **.** (a dot). Use a dot as a separator between elements in a map:
+ `MyMap.nestedField`
+ `MyMap.nestedField.deeplyNestedField`
## Document paths
In an expression, you use a *document path* to tell DynamoDB where to find an attribute. For a top-level attribute, the document path is simply the attribute name. For a nested attribute, you construct the document path using dereference operators.
The following are some examples of document paths. (Refer to the item shown in [Referring to item attributes when using expressions in DynamoDB](#Expressions.Attributes).)
+ A top-level scalar attribute.
`Description`
+ A top-level list attribute. (This returns the entire list, not just some of the elements.)
`RelatedItems`
+ The third element from the `RelatedItems` list. (Remember that list elements are zero-based.)
`RelatedItems[2]`
+ The front-view picture of the product.
`Pictures.FrontView`
+ All of the five-star reviews.
`ProductReviews.FiveStar`
+ The first of the five-star reviews.
`ProductReviews.FiveStar[0]`
**Note**
The maximum depth for a document path is 32. Therefore, the number of dereferences operators in a path cannot exceed this limit.
You can use any attribute name in a document path as long as they meet these requirements:
+ The first character is `a-z` or `A-Z` and or `0-9`
+ The second character (if present) is `a-z`, `A-Z`
**Note**
If an attribute name does not meet this requirement, you must define an expression attribute name as a placeholder.
For more information, see [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md).
# Expression attribute names (aliases) in DynamoDB
An *expression attribute name* is an alias (or placeholder) that you use in an Amazon DynamoDB expression as an alternative to an actual attribute name. An expression attribute name must begin with a pound sign (`#`) and be followed by one or more alphanumeric characters. The underscore (`_`) character is also allowed.
This section describes several situations in which you must use expression attribute names.
**Note**
The examples in this section use the AWS Command Line Interface (AWS CLI).
**Topics**
+ [Reserved words](#Expressions.ExpressionAttributeNames.ReservedWords)
+ [Attribute names containing special characters](#Expressions.ExpressionAttributeNames.AttributeNamesContainingSpecialCharacters)
+ [Nested attributes](#Expressions.ExpressionAttributeNames.NestedAttributes)
+ [Repeatedly referencing attribute names](#Expressions.ExpressionAttributeNames.RepeatingAttributeNames)
## Reserved words
Sometimes you might need to write an expression containing an attribute name that conflicts with a DynamoDB reserved word. (For a complete list of reserved words, see [Reserved words in DynamoDB](ReservedWords.md).)
For example, the following AWS CLI example would fail because `COMMENT` is a reserved word.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "Comment"
```
To work around this, you can replace `Comment` with an expression attribute name such as `#c`. The `#` (pound sign) is required and indicates that this is a placeholder for an attribute name. The AWS CLI example would now look like the following.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "#c" \
--expression-attribute-names '{"#c":"Comment"}'
```
**Note**
If an attribute name begins with a number, contains a space or contains a reserved word, you *must* use an expression attribute name to replace that attribute's name in the expression.
## Attribute names containing special characters
In an expression, a dot (".") is interpreted as a separator character in a document path. However, DynamoDB also allows you to use a dot character and other special characters, such as a hyphen ("-") as part of an attribute name. This can be ambiguous in some cases. To illustrate, suppose that you wanted to retrieve the `Safety.Warning` attribute from a `ProductCatalog` item (see [Referring to item attributes when using expressions in DynamoDB](Expressions.Attributes.md)).
Suppose that you wanted to access `Safety.Warning` using a projection expression.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "Safety.Warning"
```
DynamoDB would return an empty result, rather than the expected string ("`Always wear a helmet`"). This is because DynamoDB interprets a dot in an expression as a document path separator. In this case, you must define an expression attribute name (such as `#sw`) as a substitute for `Safety.Warning`. You could then use the following projection expression.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "#sw" \
--expression-attribute-names '{"#sw":"Safety.Warning"}'
```
DynamoDB would then return the correct result.
**Note**
If an attribute name contains a dot (".") or a hyphen ("-"), you *must* use an expression attribute name to replace that attribute's name in the expression.
## Nested attributes
Suppose that you wanted to access the nested attribute `ProductReviews.OneStar`. In an expression attribute name, DynamoDB treats the dot (".") as a character within an attribute's name. To reference the nested attribute, define an expression attribute name for each element in the document path:
+ `#pr — ProductReviews`
+ `#1star — OneStar`
You could then use `#pr.#1star` for the projection expression.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "#pr.#1star" \
--expression-attribute-names '{"#pr":"ProductReviews", "#1star":"OneStar"}'
```
DynamoDB would then return the correct result.
## Repeatedly referencing attribute names
Expression attribute names are helpful when you need to refer to the same attribute name repeatedly. For example, consider the following expression for retrieving some of the reviews from a `ProductCatalog` item.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "ProductReviews.FiveStar, ProductReviews.ThreeStar, ProductReviews.OneStar"
```
To make this more concise, you can replace `ProductReviews` with an expression attribute name such as `#pr`. The revised expression would now look like the following.
+ `#pr.FiveStar, #pr.ThreeStar, #pr.OneStar`
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"123"}}' \
--projection-expression "#pr.FiveStar, #pr.ThreeStar, #pr.OneStar" \
--expression-attribute-names '{"#pr":"ProductReviews"}'
```
If you define an expression attribute name, you must use it consistently throughout the entire expression. Also, you cannot omit the `#` symbol.
# Using expression attribute values in DynamoDB
*Expression attribute values* in Amazon DynamoDB act as variables. They're substitutes for the actual values that you want to compare—values that you might not know until runtime. An expression attribute value must begin with a colon (`:`) and be followed by one or more alphanumeric characters.
For example, suppose that you wanted to return all of the `ProductCatalog` items that are available in `Black` and cost `500` or less. You could use a `Scan` operation with a filter expression, as in this AWS Command Line Interface (AWS CLI) example.
```
aws dynamodb scan \
--table-name ProductCatalog \
--filter-expression "contains(Color, :c) and Price <= :p" \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":c": { "S": "Black" },
":p": { "N": "500" }
}
```
If you define an expression attribute value, you must use it consistently throughout the entire expression. Also, you can't omit the `:` symbol.
Expression attribute values are used with key condition expressions, condition expressions, update expressions, and filter expressions.
# Using projection expressions in DynamoDB
To read data from a table, you use operations such as `GetItem`, `Query`, or `Scan`. Amazon DynamoDB returns all the item attributes by default. To get only some, rather than all of the attributes, use a projection expression.
A *projection expression* is a string that identifies the attributes that you want. To retrieve a single attribute, specify its name. For multiple attributes, the names must be comma-separated.
The following are some examples of projection expressions, based on the `ProductCatalog` item from [Referring to item attributes when using expressions in DynamoDB](Expressions.Attributes.md):
+ A single top-level attribute.
`Title `
+ Three top-level attributes. DynamoDB retrieves the entire `Color` set.
`Title, Price, Color`
+ Four top-level attributes. DynamoDB returns the entire contents of `RelatedItems` and `ProductReviews`.
`Title, Description, RelatedItems, ProductReviews`
**Note**
Projection expression has no effect on provisioned throughput consumption. DynamoDB determines capacity units consumed based on item size, instead of the amount of data that is returned to an application.
**Reserved words and special characters**
DynamoDB has reserved words and special characters. DynamoDB allows you to use these reserved words and special characters for names, but we recommend that you avoid doing so because you have to use aliases for them whenever you use these names in an expression. For a complete list, see [Reserved words in DynamoDB](ReservedWords.md).
You'll need to use expression attribute names in place of the actual name if:
+ The attribute name is on the list of reserved words in DynamoDB.
+ The attribute name does not meet the requirement that the first character is `a-z` or `A-Z` and that the second character (if present) is `a-Z`, `A-Z`, or `0-9`.
+ The attribute name contains a **\$1** (hash) or **:** (colon).
The following AWS CLI example shows how to use a projection expression with a `GetItem` operation. This projection expression retrieves a top-level scalar attribute (`Description`), the first element in a list (`RelatedItems[0]`), and a list nested within a map (`ProductReviews.FiveStar`).
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '"Id": { "N": "123" } \
--projection-expression "Description, RelatedItems[0], ProductReviews.FiveStar"
```
The following JSON would be returned for this example.
```
{
"Item": {
"Description": {
"S": "123 description"
},
"ProductReviews": {
"M": {
"FiveStar": {
"L": [
{
"S": "Excellent! Can't recommend it highly enough! Buy it!"
},
{
"S": "Do yourself a favor and buy this."
}
]
}
}
},
"RelatedItems": {
"L": [
{
"N": "341"
}
]
}
}
}
```
# Using update expressions in DynamoDB
The `UpdateItem` operation updates an existing item, or adds a new item to the table if it does not already exist. You must provide the key of the item that you want to update. You must also provide an update expression, indicating the attributes that you want to modify and the values that you want to assign to them.
An *update expression* specifies how `UpdateItem` will modify the attributes of an item—for example, setting a scalar value or removing elements from a list or a map.
The following is a syntax summary for update expressions.
```
update-expression ::=
[ SET action [, action] ... ]
[ REMOVE action [, action] ...]
[ ADD action [, action] ... ]
[ DELETE action [, action] ...]
```
An update expression consists of one or more clauses. Each clause begins with a `SET`, `REMOVE`, `ADD`, or `DELETE` keyword. You can include any of these clauses in an update expression, in any order. However, each action keyword can appear only once.
Within each clause, there are one or more actions separated by commas. Each action represents a data modification.
The examples in this section are based on the `ProductCatalog` item shown in [Using projection expressions in DynamoDB](Expressions.ProjectionExpressions.md).
The topics below cover some different use cases for the `SET` action.
**Topics**
+ [SET — modifying or adding item attributes](#Expressions.UpdateExpressions.SET)
+ [REMOVE — deleting attributes from an item](#Expressions.UpdateExpressions.REMOVE)
+ [ADD — updating numbers and sets](#Expressions.UpdateExpressions.ADD)
+ [DELETE — removing elements from a set](#Expressions.UpdateExpressions.DELETE)
+ [Using multiple update expressions](#Expressions.UpdateExpressions.Multiple)
## SET — modifying or adding item attributes
Use the `SET` action in an update expression to add one or more attributes to an item. If any of these attributes already exists, they are overwritten by the new values. If you want to avoid overwriting an existing attribute, you can use `SET` with the `if_not_exists` function. The `if_not_exists` function is specific to the `SET` action and can only be used in an update expression.
When you use `SET` to update a list element, the contents of that element are replaced with the new data that you specify. If the element doesn't already exist, `SET` appends the new element at the end of the list.
If you add multiple elements in a single `SET` operation, the elements are sorted in order by element number.
You can also use `SET` to add or subtract from an attribute that is of type `Number`. To perform multiple `SET` actions, separate them with commas.
In the following syntax summary:
+ The *path* element is the document path to the item.
+ An **operand** element can be either a document path to an item or a function.
```
set-action ::=
path = value
value ::=
operand
| operand '+' operand
| operand '-' operand
operand ::=
path | function
function ::=
if_not_exists (path, value)
```
If the item does not contain an attribute at the specified path, `if_not_exists` evaluates to `value`. Otherwise, it evaluates to `path`.
The following `PutItem` operation creates a sample item that the examples refer to.
```
aws dynamodb put-item \
--table-name ProductCatalog \
--item file://item.json
```
The arguments for `--item` are stored in the `item.json` file. (For simplicity, only a few item attributes are used.)
```
{
"Id": {"N": "789"},
"ProductCategory": {"S": "Home Improvement"},
"Price": {"N": "52"},
"InStock": {"BOOL": true},
"Brand": {"S": "Acme"}
}
```
**Topics**
+ [Modifying attributes](#Expressions.UpdateExpressions.SET.ModifyingAttributes)
+ [Adding lists and maps](#Expressions.UpdateExpressions.SET.AddingListsAndMaps)
+ [Adding elements to a list](#Expressions.UpdateExpressions.SET.AddingListElements)
+ [Adding nested map attributes](#Expressions.UpdateExpressions.SET.AddingNestedMapAttributes)
+ [Incrementing and decrementing numeric attributes](#Expressions.UpdateExpressions.SET.IncrementAndDecrement)
+ [Appending elements to a list](#Expressions.UpdateExpressions.SET.UpdatingListElements)
+ [Preventing overwrites of an existing attribute](#Expressions.UpdateExpressions.SET.PreventingAttributeOverwrites)
### Modifying attributes
**Example**
Update the `ProductCategory` and `Price` attributes.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET ProductCategory = :c, Price = :p" \
--expression-attribute-values file://values.json \
--return-values ALL_NEW
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":c": { "S": "Hardware" },
":p": { "N": "60" }
}
```
**Note**
In the `UpdateItem` operation, `--return-values ALL_NEW` causes DynamoDB to return the item as it appears after the update.
### Adding lists and maps
**Example**
Add a new list and a new map.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET RelatedItems = :ri, ProductReviews = :pr" \
--expression-attribute-values file://values.json \
--return-values ALL_NEW
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":ri": {
"L": [
{ "S": "Hammer" }
]
},
":pr": {
"M": {
"FiveStar": {
"L": [
{ "S": "Best product ever!" }
]
}
}
}
}
```
### Adding elements to a list
**Example**
Add a new attribute to the `RelatedItems` list. (Remember that list elements are zero-based, so [0] represents the first element in the list, [1] represents the second, and so on.)
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET RelatedItems[1] = :ri" \
--expression-attribute-values file://values.json \
--return-values ALL_NEW
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":ri": { "S": "Nails" }
}
```
**Note**
When you use `SET` to update a list element, the contents of that element are replaced with the new data that you specify. If the element doesn't already exist, `SET` appends the new element at the end of the list.
If you add multiple elements in a single `SET` operation, the elements are sorted in order by element number.
### Adding nested map attributes
**Example**
Add some nested map attributes.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET #pr.#5star[1] = :r5, #pr.#3star = :r3" \
--expression-attribute-names file://names.json \
--expression-attribute-values file://values.json \
--return-values ALL_NEW
```
The arguments for `--expression-attribute-names` are stored in the `names.json` file.
```
{
"#pr": "ProductReviews",
"#5star": "FiveStar",
"#3star": "ThreeStar"
}
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":r5": { "S": "Very happy with my purchase" },
":r3": {
"L": [
{ "S": "Just OK - not that great" }
]
}
}
```
**Important**
You cannot update nested map attributes if the parent map does not exist. If you attempt to update a nested attribute (for example, `ProductReviews.FiveStar`) when the parent map (`ProductReviews`) does not exist, DynamoDB returns a `ValidationException` with the message *"The document path provided in the update expression is invalid for update."*
When creating items that will have nested map attributes updated later, initialize empty maps for the parent attributes. For example:
```
{
"Id": {"N": "789"},
"ProductReviews": {"M": {}},
"Metadata": {"M": {}}
}
```
This allows you to update nested attributes like `ProductReviews.FiveStar` without errors.
### Incrementing and decrementing numeric attributes
You can add to or subtract from an existing numeric attribute. To do this, use the `+` (plus) and `-` (minus) operators.
**Example**
Decrease the `Price` of an item.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET Price = Price - :p" \
--expression-attribute-values '{":p": {"N":"15"}}' \
--return-values ALL_NEW
```
To increase the `Price`, you would use the `+` operator in the update expression.
### Appending elements to a list
You can add elements to the end of a list. To do this, use `SET` with the `list_append` function. (The function name is case sensitive.) The `list_append` function is specific to the `SET` action and can only be used in an update expression. The syntax is as follows.
+ `list_append (list1, list2)`
The function takes two lists as input and appends all elements from `list2` to ` list1`.
**Example**
In [Adding elements to a list](#Expressions.UpdateExpressions.SET.AddingListElements), you create the `RelatedItems` list and populate it with two elements: `Hammer` and `Nails`. Now you append two more elements to the end of `RelatedItems`.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET #ri = list_append(#ri, :vals)" \
--expression-attribute-names '{"#ri": "RelatedItems"}' \
--expression-attribute-values file://values.json \
--return-values ALL_NEW
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":vals": {
"L": [
{ "S": "Screwdriver" },
{"S": "Hacksaw" }
]
}
}
```
Finally, you append one more element to the *beginning* of `RelatedItems`. To do this, swap the order of the `list_append` elements. (Remember that `list_append` takes two lists as input and appends the second list to the first.)
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET #ri = list_append(:vals, #ri)" \
--expression-attribute-names '{"#ri": "RelatedItems"}' \
--expression-attribute-values '{":vals": {"L": [ { "S": "Chisel" }]}}' \
--return-values ALL_NEW
```
The resulting `RelatedItems` attribute now contains five elements, in the following order: `Chisel`, `Hammer`, `Nails`, `Screwdriver`, `Hacksaw`.
### Preventing overwrites of an existing attribute
**Example**
Set the `Price` of an item, but only if the item does not already have a `Price` attribute. (If `Price` already exists, nothing happens.)
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET Price = if_not_exists(Price, :p)" \
--expression-attribute-values '{":p": {"N": "100"}}' \
--return-values ALL_NEW
```
## REMOVE — deleting attributes from an item
Use the `REMOVE` action in an update expression to remove one or more attributes from an item in Amazon DynamoDB. To perform multiple `REMOVE` actions, separate them with commas.
The following is a syntax summary for `REMOVE` in an update expression. The only operand is the document path for the attribute that you want to remove.
```
remove-action ::=
path
```
**Example**
Remove some attributes from an item. (If the attributes don't exist, nothing happens.)
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "REMOVE Brand, InStock, QuantityOnHand" \
--return-values ALL_NEW
```
### Removing elements from a list
You can use `REMOVE` to delete individual elements from a list.
**Example**
In [Appending elements to a list](#Expressions.UpdateExpressions.SET.UpdatingListElements), you modify a list attribute (`RelatedItems`) so that it contained five elements:
+ `[0]`—`Chisel`
+ `[1]`—`Hammer`
+ `[2]`—`Nails`
+ `[3]`—`Screwdriver`
+ `[4]`—`Hacksaw`
The following AWS Command Line Interface (AWS CLI) example deletes `Hammer` and `Nails` from the list.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "REMOVE RelatedItems[1], RelatedItems[2]" \
--return-values ALL_NEW
```
After `Hammer` and `Nails` are removed, the remaining elements are shifted. The list now contains the following:
+ `[0]`—`Chisel`
+ `[1]`—`Screwdriver`
+ `[2]`—`Hacksaw`
## ADD — updating numbers and sets
**Note**
In general, we recommend using `SET` rather than `ADD` to ensure idempotent operations.
Use the `ADD` action in an update expression to add a new attribute and its values to an item.
If the attribute already exists, the behavior of `ADD` depends on the attribute's data type:
+ If the attribute is a number, and the value you are adding is also a number, the value is mathematically added to the existing attribute. (If the value is a negative number, it is subtracted from the existing attribute.)
+ If the attribute is a set, and the value you are adding is also a set, the value is appended to the existing set.
**Note**
The `ADD` action supports only number and set data types.
To perform multiple `ADD` actions, separate them with commas.
In the following syntax summary:
+ The *path* element is the document path to an attribute. The attribute must be either a `Number` or a set data type.
+ The *value* element is a number that you want to add to the attribute (for `Number` data types), or a set to append to the attribute (for set types).
```
add-action ::=
path value
```
The topics below cover some different use cases for the `ADD` action.
**Topics**
+ [Adding a number](#Expressions.UpdateExpressions.ADD.Number)
+ [Adding elements to a set](#Expressions.UpdateExpressions.ADD.Set)
### Adding a number
Assume that the `QuantityOnHand` attribute does not exist. The following AWS CLI example sets `QuantityOnHand` to 5.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "ADD QuantityOnHand :q" \
--expression-attribute-values '{":q": {"N": "5"}}' \
--return-values ALL_NEW
```
Now that `QuantityOnHand` exists, you can rerun the example to increment `QuantityOnHand` by 5 each time.
### Adding elements to a set
Assume that the `Color` attribute does not exist. The following AWS CLI example sets `Color` to a string set with two elements.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "ADD Color :c" \
--expression-attribute-values '{":c": {"SS":["Orange", "Purple"]}}' \
--return-values ALL_NEW
```
Now that `Color` exists, you can add more elements to it.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "ADD Color :c" \
--expression-attribute-values '{":c": {"SS":["Yellow", "Green", "Blue"]}}' \
--return-values ALL_NEW
```
## DELETE — removing elements from a set
**Important**
The `DELETE` action supports only `Set` data types.
Use the `DELETE` action in an update expression to remove one or more elements from a set. To perform multiple `DELETE` actions, separate them with commas.
In the following syntax summary:
+ The *path* element is the document path to an attribute. The attribute must be a set data type.
+ The *subset* is one or more elements that you want to delete from *path*. You must specify *subset* as a set type.
```
delete-action ::=
path subset
```
**Example**
In [Adding elements to a set](#Expressions.UpdateExpressions.ADD.Set), you create the `Color` string set. This example removes some of the elements from that set.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "DELETE Color :p" \
--expression-attribute-values '{":p": {"SS": ["Yellow", "Purple"]}}' \
--return-values ALL_NEW
```
## Using multiple update expressions
You can use multiple actions in a single update expression. All attribute references are resolved against the item's state before any of the actions are applied.
**Example**
Given an item `{"id": "1", "a": 1, "b": 2, "c": 3}`, the following expression removes `a` and shifts the values of `b` and `c`:
```
aws dynamodb update-item \
--table-name test \
--key '{"id":{"S":"1"}}' \
--update-expression "REMOVE a SET b = a, c = b" \
--return-values ALL_NEW
```
The result is `{"id": "1", "b": 1, "c": 2}`. Even though `a` is removed and `b` is reassigned in the same expression, both references resolve to their original values.
**Example**
If you want to modify an attribute's value and completely remove another attribute, you could use a SET and a REMOVE action in a single statement. This operation would reduce the `Price` value to 15 while also removing the `InStock` attribute from the item.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET Price = Price - :p REMOVE InStock" \
--expression-attribute-values '{":p": {"N":"15"}}' \
--return-values ALL_NEW
```
**Example**
If you want to add to a list while also changing another attribute's value, you could use two SET actions in a single statement. This operation would add "Nails" to the `RelatedItems` list attribute and also set the `Price` value to 21.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"789"}}' \
--update-expression "SET RelatedItems[1] = :newValue, Price = :newPrice" \
--expression-attribute-values '{":newValue": {"S":"Nails"}, ":newPrice": {"N":"21"}}' \
--return-values ALL_NEW
```
# Condition and filter expressions, operators, and functions in DynamoDB
To manipulate data in an DynamoDB table, you use the `PutItem`, `UpdateItem`, and `DeleteItem` operations. For these data manipulation operations, you can specify a condition expression to determine which items should be modified. If the condition expression evaluates to true, the operation succeeds. Otherwise, the operation fails.
This section covers the built-in functions and keywords for writing filter expressions and condition expressions in Amazon DynamoDB. For more detailed information on functions and programming with DynamoDB, see [Programming with DynamoDB and the AWS SDKs](Programming.md) and the [DynamoDB API Reference](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/).
**Topics**
+ [Syntax for filter and condition expressions](#Expressions.OperatorsAndFunctions.Syntax)
+ [Making comparisons](#Expressions.OperatorsAndFunctions.Comparators)
+ [Functions](#Expressions.OperatorsAndFunctions.Functions)
+ [Logical evaluations](#Expressions.OperatorsAndFunctions.LogicalEvaluations)
+ [Parentheses](#Expressions.OperatorsAndFunctions.Parentheses)
+ [Precedence in conditions](#Expressions.OperatorsAndFunctions.Precedence)
## Syntax for filter and condition expressions
In the following syntax summary, an *operand* can be the following:
+ A top-level attribute name, such as `Id`, `Title`, `Description`, or `ProductCategory`
+ A document path that references a nested attribute
```
condition-expression ::=
operand comparator operand
| operand BETWEEN operand AND operand
| operand IN ( operand (',' operand (, ...) ))
| function
| condition AND condition
| condition OR condition
| NOT condition
| ( condition )
comparator ::=
=
| <>
| <
| <=
| >
| >=
function ::=
attribute_exists (path)
| attribute_not_exists (path)
| attribute_type (path, type)
| begins_with (path, substr)
| contains (path, operand)
| size (path)
```
## Making comparisons
Use these comparators to compare an operand against a single value:
+ `a = b` – True if *a* is equal to *b*.
+ `a <> b` – True if *a* is not equal to *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*.
Use the `BETWEEN` and `IN` keywords to compare an operand against a range of values or an enumerated list of values:
+ `a BETWEEN b AND c` – True if *a* is greater than or equal to *b*, and less than or equal to *c*.
+ `a IN (b, c, d) ` – True if *a* is equal to any value in the list—for example, any of *b*, *c*, or *d*. The list can contain up to 100 values, separated by commas.
## Functions
Use the following functions to determine whether an attribute exists in an item, or to evaluate the value of an attribute. These function names are case sensitive. For a nested attribute, you must provide its full document path.
****
| Function | Description |
| --- | --- |
| `attribute_exists (path)` | True if the item contains the attribute specified by `path`. Example: Check whether an item in the `Product` table has a side view picture. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) |
| `attribute_not_exists (path)` | True if the attribute specified by `path` does not exist in the item. Example: Check whether an item has a `Manufacturer` attribute. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) |
| `attribute_type (path, type)` | True if the attribute at the specified path is of a particular data type. The `type` parameter must be one of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) You must use an expression attribute value for the `type` parameter. Example: Check whether the `QuantityOnHand` attribute is of type List. In this example, `:v_sub` is a placeholder for the string `L`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) You must use an expression attribute value for the `type` parameter. |
| `begins_with (path, substr)` | True if the attribute specified by `path` begins with a particular substring. Example: Check whether the first few characters of the front view picture URL are `http://`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) The expression attribute value `:v_sub` is a placeholder for `http://`. |
| `contains (path, operand)` | True if the attribute specified by `path` is one of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) If the attribute specified by `path` is a `String`, the `operand` must be a `String`. If the attribute specified by `path` is a `Set`, the `operand` must be the set's element type. The path and the operand must be distinct. That is, `contains (a, a)` returns an error. Example: Check whether the `Brand` attribute contains the substring `Company`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) The expression attribute value `:v_sub` is a placeholder for `Company`. Example: Check whether the product is available in red. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) The expression attribute value `:v_sub` is a placeholder for `Red`. |
| `size (path)` | Returns a number that represents an attribute's size. The following are valid data types for use with `size`. If the attribute is of type `String`, `size` returns the length of the string. Example: Check whether the string `Brand` is less than or equal to 20 characters. The expression attribute value `:v_sub` is a placeholder for `20`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) If the attribute is of type `Binary`, `size` returns the number of bytes in the attribute value. Example: Suppose that the `ProductCatalog` item has a binary attribute named `VideoClip` that contains a short video of the product in use. The following expression checks whether `VideoClip` exceeds 64,000 bytes. The expression attribute value `:v_sub` is a placeholder for `64000`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) If the attribute is a `Set` data type, `size` returns the number of elements in the set. Example: Check whether the product is available in more than one color. The expression attribute value `:v_sub` is a placeholder for `1`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) If the attribute is of type `List` or `Map`, `size` returns the number of child elements. Example: Check whether the number of `OneStar` reviews has exceeded a certain threshold. The expression attribute value `:v_sub` is a placeholder for `3`. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html) |
## Logical evaluations
Use the `AND`, `OR`, and `NOT` keywords to perform logical evaluations. In the following list, *a* and *b* represent conditions to be evaluated.
+ `a AND b` – True if *a* and *b* are both true.
+ `a OR b` – True if either *a* or *b* (or both) are true.
+ `NOT a` – True if *a* is false. False if *a* is true.
The following is a code example of AND in an operation.
`dynamodb-local (*)> select * from exprtest where a > 3 and a < 5;`
## Parentheses
Use parentheses to change the precedence of a logical evaluation. For example, suppose that conditions *a* and *b* are true, and that condition *c* is false. The following expression evaluates to true:
+ `a OR b AND c`
However, if you enclose a condition in parentheses, it is evaluated first. For example, the following evaluates to false:
+ `(a OR b) AND c`
**Note**
You can nest parentheses in an expression. The innermost ones are evaluated first.
The following is a code example with parentheses in a logical evaluation.
`dynamodb-local (*)> select * from exprtest where attribute_type(b, string) or ( a = 5 and c = “coffee”);`
## Precedence in conditions
DynamoDB evaluates conditions from left to right using the following precedence rules:
+ `= <> < <= > >=`
+ `IN`
+ `BETWEEN`
+ `attribute_exists attribute_not_exists begins_with contains`
+ Parentheses
+ `NOT`
+ `AND`
+ `OR`
# DynamoDB condition expression CLI example
The following are some AWS Command Line Interface (AWS CLI) examples of using condition expressions. These examples are based on the `ProductCatalog` table, which was introduced in [Referring to item attributes when using expressions in DynamoDB](Expressions.Attributes.md). The partition key for this table is `Id`; there is no sort key. The following `PutItem` operation creates a sample `ProductCatalog` item that the examples refer to.
```
aws dynamodb put-item \
--table-name ProductCatalog \
--item file://item.json
```
The arguments for `--item` are stored in the `item.json` file. (For simplicity, only a few item attributes are used.)
```
{
"Id": {"N": "456" },
"ProductCategory": {"S": "Sporting Goods" },
"Price": {"N": "650" }
}
```
**Topics**
+ [Conditional put](#Expressions.ConditionExpressions.PreventingOverwrites)
+ [Conditional deletes](#Expressions.ConditionExpressions.AdvancedComparisons)
+ [Conditional updates](#Expressions.ConditionExpressions.SimpleComparisons)
+ [Conditional expression examples](#Expressions.ConditionExpressions.ConditionalExamples)
## Conditional put
The `PutItem` operation overwrites an item with the same primary key (if it exists). If you want to avoid this, use a condition expression. This allows the write to proceed only if the item in question does not already have the same primary key.
The following example uses `attribute_not_exists()` to check whether the primary key exists in the table before attempting the write operation.
**Note**
If your primary key consists of both a partition key(pk) and a sort key(sk), the parameter will check whether `attribute_not_exists(pk)` AND `attribute_not_exists(sk)` evaluate to true or false as an entire statement before attempting the write operation.
```
aws dynamodb put-item \
--table-name ProductCatalog \
--item file://item.json \
--condition-expression "attribute_not_exists(Id)"
```
If the condition expression evaluates to false, DynamoDB returns the following error message: The conditional request failed.
**Note**
For more information about `attribute_not_exists` and other functions, see [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md).
## Conditional deletes
To perform a conditional delete, you use a `DeleteItem` operation with a condition expression. The condition expression must evaluate to true in order for the operation to succeed; otherwise, the operation fails.
Consider the item defined above.
Suppose that you wanted to delete the item, but only under the following conditions:
+ The `ProductCategory` is either "Sporting Goods" or "Gardening Supplies."
+ The `Price` is between 500 and 600.
The following example tries to delete the item.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"456"}}' \
--condition-expression "(ProductCategory IN (:cat1, :cat2)) and (Price between :lo and :hi)" \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":cat1": {"S": "Sporting Goods"},
":cat2": {"S": "Gardening Supplies"},
":lo": {"N": "500"},
":hi": {"N": "600"}
}
```
**Note**
In the condition expression, the `:` (colon character) indicates an *expression attribute value*—a placeholder for an actual value. For more information, see [Using expression attribute values in DynamoDB](Expressions.ExpressionAttributeValues.md).
For more information about `IN`, `AND`, and other keywords, see [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md).
In this example, the `ProductCategory` comparison evaluates to true, but the `Price` comparison evaluates to false. This causes the condition expression to evaluate to false and the `DeleteItem` operation to fail.
## Conditional updates
To perform a conditional update, you use an `UpdateItem` operation with a condition expression. The condition expression must evaluate to true in order for the operation to succeed; otherwise, the operation fails.
**Note**
`UpdateItem` also supports *update expressions*, where you specify the modifications you want to make to an item. For more information, see [Using update expressions in DynamoDB](Expressions.UpdateExpressions.md).
Suppose that you started with the item defined above.
The following example performs an `UpdateItem` operation. It tries to reduce the `Price` of a product by 75—but the condition expression prevents the update if the current `Price` is less than or equal to 500.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--update-expression "SET Price = Price - :discount" \
--condition-expression "Price > :limit" \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":discount": { "N": "75"},
":limit": {"N": "500"}
}
```
If the starting `Price` is 650, the `UpdateItem` operation reduces the `Price` to 575. If you run the `UpdateItem` operation again, the `Price` is reduced to 500. If you run it a third time, the condition expression evaluates to false, and the update fails.
**Note**
In the condition expression, the `:` (colon character) indicates an *expression attribute value*—a placeholder for an actual value. For more information, see [Using expression attribute values in DynamoDB](Expressions.ExpressionAttributeValues.md).
For more information about "*>*" and other operators, see [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md).
## Conditional expression examples
For more information about the functions used in the following examples, see [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md). If you want to know more about how to specify different attribute types in an expression, see [Referring to item attributes when using expressions in DynamoDB](Expressions.Attributes.md).
### Checking for attributes in an item
You can check for the existence (or nonexistence) of any attribute. If the condition expression evaluates to true, the operation succeeds; otherwise, it fails.
The following example uses `attribute_not_exists` to delete a product only if it does not have a `Price` attribute.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "attribute_not_exists(Price)"
```
DynamoDB also provides an `attribute_exists` function. The following example deletes a product only if it has received poor reviews.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "attribute_exists(ProductReviews.OneStar)"
```
### Checking for attribute type
You can check the data type of an attribute value by using the `attribute_type` function. If the condition expression evaluates to true, the operation succeeds; otherwise, it fails.
The following example uses `attribute_type` to delete a product only if it has a `Color` attribute of type String Set.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "attribute_type(Color, :v_sub)" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the expression-attribute-values.json file.
```
{
":v_sub":{"S":"SS"}
}
```
### Checking string starting value
You can check if a String attribute value begins with a particular substring by using the `begins_with` function. If the condition expression evaluates to true, the operation succeeds; otherwise, it fails.
The following example uses `begins_with` to delete a product only if the `FrontView` element of the `Pictures` map starts with a specific value.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "begins_with(Pictures.FrontView, :v_sub)" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the expression-attribute-values.json file.
```
{
":v_sub":{"S":"http://"}
}
```
### Checking for an element in a set
You can check for an element in a set or look for a substring within a string by using the `contains` function. If the condition expression evaluates to true, the operation succeeds; otherwise, it fails.
The following example uses `contains` to delete a product only if the `Color` String Set has an element with a specific value.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "contains(Color, :v_sub)" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the expression-attribute-values.json file.
```
{
":v_sub":{"S":"Red"}
}
```
### Checking the size of an attribute value
You can check for the size of an attribute value by using the `size` function. If the condition expression evaluates to true, the operation succeeds; otherwise, it fails.
The following example uses `size` to delete a product only if the size of the `VideoClip` Binary attribute is greater than `64000` bytes.
```
aws dynamodb delete-item \
--table-name ProductCatalog \
--key '{"Id": {"N": "456"}}' \
--condition-expression "size(VideoClip) > :v_sub" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the expression-attribute-values.json file.
```
{
":v_sub":{"N":"64000"}
}
```