# Working with items and attributes in DynamoDB
In Amazon DynamoDB, an *item* is a collection of attributes. Each attribute has a name and a value. An attribute value can be a scalar, a set, or a document type. For more information, see [Amazon DynamoDB: How it works](HowItWorks.md).
DynamoDB provides four operations for basic create, read, update, and delete (CRUD) functionality. All these operations are atomic.
+ `PutItem` — Create an item.
+ `GetItem` — Read an item.
+ `UpdateItem` — Update an item.
+ `DeleteItem` — Delete an item.
Each of these operations requires that you specify the primary key of the item that you want to work with. For example, to read an item using `GetItem`, you must specify the partition key and sort key (if applicable) for that item.
In addition to the four basic CRUD operations, DynamoDB also provides the following:
+ `BatchGetItem` — Read up to 100 items from one or more tables.
+ `BatchWriteItem` — Create or delete up to 25 items in one or more tables.
These batch operations combine multiple CRUD operations into a single request. In addition, the batch operations read and write items in parallel to minimize response latencies.
This section describes how to use these operations and includes related topics, such as conditional updates and atomic counters. This section also includes example code that uses the AWS SDKs.
**Topics**
+ [
# DynamoDB item sizes and formats
](CapacityUnitCalculations.md)
+ [
## Reading an item
](#WorkingWithItems.ReadingData)
+ [
## Writing an item
](#WorkingWithItems.WritingData)
+ [
## Return values
](#WorkingWithItems.ReturnValues)
+ [
## Batch operations
](#WorkingWithItems.BatchOperations)
+ [
## Atomic counters
](#WorkingWithItems.AtomicCounters)
+ [
## Conditional writes
](#WorkingWithItems.ConditionalUpdate)
+ [
# Using expressions in DynamoDB
](Expressions.md)
+ [
# Using time to live (TTL) in DynamoDB
](TTL.md)
+ [
# Querying tables in DynamoDB
](Query.md)
+ [
# Scanning tables in DynamoDB
](Scan.md)
+ [
# PartiQL - a SQL-compatible query language for Amazon DynamoDB
](ql-reference.md)
+ [
# Working with items: Java
](JavaDocumentAPIItemCRUD.md)
+ [
# Working with items: .NET
](LowLevelDotNetItemCRUD.md)
# DynamoDB item sizes and formats
DynamoDB tables are schemaless, except for the primary key, so the items in a table can all have different attributes, sizes, and data types.
The total size of an item is the sum of the lengths of its attribute names and values, plus any applicable overhead as described below. You can use the following guidelines to estimate attribute sizes:
+ Strings are Unicode with UTF-8 binary encoding. The size of a string is *(number of UTF-8-encoded bytes of attribute name) \$1 (number of UTF-8-encoded bytes)*.
+ Numbers are variable length, with up to 38 significant digits. Leading and trailing zeroes are trimmed. The size of a number is approximately *(number of UTF-8-encoded bytes of attribute name) \$1 (1 byte per two significant digits) \$1 (1 byte)*.
+ A binary value must be encoded in base64 format before it can be sent to DynamoDB, but the value's raw byte length is used for calculating size. The size of a binary attribute is *(number of UTF-8-encoded bytes of attribute name) \$1 (number of raw bytes).*
+ The size of a null attribute or a Boolean attribute is *(number of UTF-8-encoded bytes of attribute name) \$1 (1 byte)*.
+ An attribute of type `List` or `Map` requires 3 bytes of overhead, regardless of its contents. The size of a `List` or `Map` is *(number of UTF-8-encoded bytes of attribute name) \$1 sum (size of nested elements) \$1 (3 bytes) *. The size of an empty `List` or `Map` is *(number of UTF-8-encoded bytes of attribute name) \$1 (3 bytes)*.
+ Each `List` or `Map` element also requires 1 byte of overhead.
**Note**
We recommend that you choose shorter attribute names rather than long ones. This helps you reduce the amount of storage required, but also can lower the amount of RCU/WCUs you use.
For storage billing purposes, each item includes a per-item storage overhead that depends on the features you have enabled.
+ All items in DynamoDB require 100 bytes of storage overhead for indexing.
+ Some DynamoDB features (global tables, transactions, change data capture for Kinesis Data Streams with DynamoDB) require additional storage overhead to account for system-created attributes resulting from enabling those features. For example, global tables requires an additional 48 bytes of storage overhead.
## Reading an item
To read an item from a DynamoDB table, use the `GetItem` operation. You must provide the name of the table, along with the primary key of the item you want.
**Example**
The following AWS CLI example shows how to read an item from the `ProductCatalog` table.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"1"}}'
```
**Note**
With `GetItem`, you must specify the *entire* primary key, not just part of it. For example, if a table has a composite primary key (partition key and sort key), you must supply a value for the partition key and a value for the sort key.
A `GetItem` request performs an eventually consistent read by default. You can use the `ConsistentRead` parameter to request a strongly consistent read instead. (This consumes additional read capacity units, but it returns the most up-to-date version of the item.)
`GetItem` returns all of the item's attributes. You can use a *projection expression* to return only some of the attributes. For more information, see [Using projection expressions in DynamoDB](Expressions.ProjectionExpressions.md).
To return the number of read capacity units consumed by `GetItem`, set the `ReturnConsumedCapacity` parameter to `TOTAL`.
**Example**
The following AWS Command Line Interface (AWS CLI) example shows some of the optional `GetItem` parameters.
```
aws dynamodb get-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"1"}}' \
--consistent-read \
--projection-expression "Description, Price, RelatedItems" \
--return-consumed-capacity TOTAL
```
## Writing an item
To create, update, or delete an item in a DynamoDB table, use one of the following operations:
+ `PutItem`
+ `UpdateItem`
+ `DeleteItem`
For each of these operations, you must specify the entire primary key, not just part of it. For example, if a table has a composite primary key (partition key and sort key), you must provide a value for the partition key and a value for the sort key.
To return the number of write capacity units consumed by any of these operations, set the `ReturnConsumedCapacity` parameter to one of the following:
+ `TOTAL` — Returns the total number of write capacity units consumed.
+ `INDEXES` — Returns the total number of write capacity units consumed, with subtotals for the table and any secondary indexes that were affected by the operation.
+ `NONE` — No write capacity details are returned. (This is the default.)
### PutItem
`PutItem` creates a new item. If an item with the same key already exists in the table, it is replaced with the new item.
**Example**
Write a new item to the `Thread` table. The primary key for `Thread` consists of `ForumName` (partition key) and `Subject` (sort key).
```
aws dynamodb put-item \
--table-name Thread \
--item file://item.json
```
The arguments for `--item` are stored in the `item.json` file.
```
{
"ForumName": {"S": "Amazon DynamoDB"},
"Subject": {"S": "New discussion thread"},
"Message": {"S": "First post in this thread"},
"LastPostedBy": {"S": "fred@example.com"},
"LastPostDateTime": {"S": "201603190422"}
}
```
### UpdateItem
If an item with the specified key does not exist, `UpdateItem` creates a new item. Otherwise, it modifies an existing item's attributes.
You use an *update expression* to specify the attributes that you want to modify and their new values. For more information, see [Using update expressions in DynamoDB](Expressions.UpdateExpressions.md).
Within the update expression, you use expression attribute values as placeholders for the actual values. For more information, see [Using expression attribute values in DynamoDB](Expressions.ExpressionAttributeValues.md).
**Example**
Modify various attributes in the `Thread` item. The optional `ReturnValues` parameter shows the item as it appears after the update. For more information, see [Return values](#WorkingWithItems.ReturnValues).
```
aws dynamodb update-item \
--table-name Thread \
--key file://key.json \
--update-expression "SET Answered = :zero, Replies = :zero, LastPostedBy = :lastpostedby" \
--expression-attribute-values file://expression-attribute-values.json \
--return-values ALL_NEW
```
The arguments for `--key` are stored in the `key.json` file.
```
{
"ForumName": {"S": "Amazon DynamoDB"},
"Subject": {"S": "New discussion thread"}
}
```
The arguments for `--expression-attribute-values` are stored in the `expression-attribute-values.json` file.
```
{
":zero": {"N":"0"},
":lastpostedby": {"S":"barney@example.com"}
}
```
### DeleteItem
`DeleteItem` deletes the item with the specified key.
**Example**
The following AWS CLI example shows how to delete the `Thread` item.
```
aws dynamodb delete-item \
--table-name Thread \
--key file://key.json
```
## Return values
In some cases, you might want DynamoDB to return certain attribute values as they appeared before or after you modified them. The `PutItem`, `UpdateItem`, and `DeleteItem` operations have a `ReturnValues` parameter that you can use to return the attribute values before or after they are modified.
The default value for `ReturnValues` is `NONE`, meaning that DynamoDB does not return any information about attributes that were modified.
The following are the other valid settings for `ReturnValues`, organized by DynamoDB API operation.
### PutItem
+ `ReturnValues`: `ALL_OLD`
+ If you overwrite an existing item, `ALL_OLD` returns the entire item as it appeared before the overwrite.
+ If you write a nonexistent item, `ALL_OLD` has no effect.
### UpdateItem
The most common usage for `UpdateItem` is to update an existing item. However, `UpdateItem` actually performs an *upsert*, meaning that it automatically creates the item if it doesn't already exist.
+ `ReturnValues`: `ALL_OLD`
+ If you update an existing item, `ALL_OLD` returns the entire item as it appeared before the update.
+ If you update a nonexistent item (upsert), `ALL_OLD` has no effect.
+ `ReturnValues`: `ALL_NEW`
+ If you update an existing item, `ALL_NEW` returns the entire item as it appeared after the update.
+ If you update a nonexistent item (upsert), `ALL_NEW` returns the entire item.
+ `ReturnValues`: `UPDATED_OLD`
+ If you update an existing item, `UPDATED_OLD` returns only the updated attributes, as they appeared before the update.
+ If you update a nonexistent item (upsert), `UPDATED_OLD` has no effect.
+ `ReturnValues`: `UPDATED_NEW`
+ If you update an existing item, `UPDATED_NEW` returns only the affected attributes, as they appeared after the update.
+ If you update a nonexistent item (upsert), `UPDATED_NEW` returns only the updated attributes, as they appear after the update.
### DeleteItem
+ `ReturnValues`: `ALL_OLD`
+ If you delete an existing item, `ALL_OLD` returns the entire item as it appeared before you deleted it.
+ If you delete a nonexistent item, `ALL_OLD` doesn't return any data.
## Batch operations
For applications that need to read or write multiple items, DynamoDB provides the `BatchGetItem` and `BatchWriteItem` operations. Using these operations can reduce the number of network round trips from your application to DynamoDB. In addition, DynamoDB performs the individual read or write operations in parallel. Your applications benefit from this parallelism without having to manage concurrency or threading.
The batch operations are essentially wrappers around multiple read or write requests. For example, if a `BatchGetItem` request contains five items, DynamoDB performs five `GetItem` operations on your behalf. Similarly, if a `BatchWriteItem` request contains two put requests and four delete requests, DynamoDB performs two `PutItem` and four `DeleteItem` requests.
In general, a batch operation does not fail unless *all* the requests in the batch fail. For example, suppose that you perform a `BatchGetItem` operation, but one of the individual `GetItem` requests in the batch fails. In this case, `BatchGetItem` returns the keys and data from the `GetItem` request that failed. The other `GetItem` requests in the batch are not affected.
### BatchGetItem
A single `BatchGetItem` operation can contain up to 100 individual `GetItem` requests and can retrieve up to 16 MB of data. In addition, a `BatchGetItem` operation can retrieve items from multiple tables.
**Example**
Retrieve two items from the `Thread` table, using a projection expression to return only some of the attributes.
```
aws dynamodb batch-get-item \
--request-items file://request-items.json
```
The arguments for `--request-items` are stored in the `request-items.json` file.
```
{
"Thread": {
"Keys": [
{
"ForumName":{"S": "Amazon DynamoDB"},
"Subject":{"S": "DynamoDB Thread 1"}
},
{
"ForumName":{"S": "Amazon S3"},
"Subject":{"S": "S3 Thread 1"}
}
],
"ProjectionExpression":"ForumName, Subject, LastPostedDateTime, Replies"
}
}
```
### BatchWriteItem
The `BatchWriteItem` operation can contain up to 25 individual `PutItem` and `DeleteItem` requests and can write up to 16 MB of data. (The maximum size of an individual item is 400 KB.) In addition, a `BatchWriteItem` operation can put or delete items in multiple tables.
**Note**
`BatchWriteItem` does not support `UpdateItem` requests.
**Example**
Write two items to the `ProductCatalog` table.
```
aws dynamodb batch-write-item \
--request-items file://request-items.json
```
The arguments for `--request-items` are stored in the `request-items.json` file.
```
{
"ProductCatalog": [
{
"PutRequest": {
"Item": {
"Id": { "N": "601" },
"Description": { "S": "Snowboard" },
"QuantityOnHand": { "N": "5" },
"Price": { "N": "100" }
}
}
},
{
"PutRequest": {
"Item": {
"Id": { "N": "602" },
"Description": { "S": "Snow shovel" }
}
}
}
]
}
```
## Atomic counters
You can use the `UpdateItem` operation to implement an *atomic counter*—a numeric attribute that is incremented, unconditionally, without interfering with other write requests. (All write requests are applied in the order in which they were received.) With an atomic counter, the updates are not idempotent. In other words, the numeric value increments or decrements each time you call `UpdateItem`. If the increment value used to update the atomic counter is positive, then it can cause overcounting. If the increment value is negative, then it can cause undercounting.
You might use an atomic counter to track the number of visitors to a website. In this case, your application would increment a numeric value, regardless of its current value. If an `UpdateItem` operation fails, the application could simply retry the operation. This would risk updating the counter twice, but you could probably tolerate a slight overcounting or undercounting of website visitors.
An atomic counter would not be appropriate where overcounting or undercounting can't be tolerated (for example, in a banking application). In this case, it is safer to use a conditional update instead of an atomic counter.
For more information, see [Incrementing and decrementing numeric attributes](Expressions.UpdateExpressions.md#Expressions.UpdateExpressions.SET.IncrementAndDecrement).
**Example**
The following AWS CLI example increments the `Price` of a product by 5. For this example, the item was known to exist before the counter is updated. Because `UpdateItem` is not idempotent, the `Price` increases every time you run this code.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id": { "N": "601" }}' \
--update-expression "SET Price = Price + :incr" \
--expression-attribute-values '{":incr":{"N":"5"}}' \
--return-values UPDATED_NEW
```
## Conditional writes
By default, the DynamoDB write operations (`PutItem`, `DeleteItem`) are *unconditional*: Each operation overwrites an existing item that has the specified primary key.
DynamoDB optionally supports conditional writes for these operations. A conditional write succeeds only if the item attributes meet one or more expected conditions. Otherwise, it returns an error.
Conditional writes check their conditions against the most recently updated version of the item. Note that if the item did not previously exist or if the most recent successful operation against that item was a delete, then the conditional write will find no previous item.
Conditional writes are helpful in many situations. For example, you might want a `PutItem` operation to succeed only if there is not already an item with the same primary key. Or you could prevent an `UpdateItem` operation from modifying an item if one of its attributes has a certain value.
Conditional writes are helpful in cases where multiple users attempt to modify the same item. Consider the following diagram, in which two users (Alice and Bob) are working with the same item from a DynamoDB table.
![\[Users Alice and Bob attempt to modify an item with Id 1, demonstrating the need for conditional writes.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/update-no-condition.png)
Suppose that Alice uses the AWS CLI to update the `Price` attribute to 8.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"1"}}' \
--update-expression "SET Price = :newval" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the file `expression-attribute-values.json`:
```
{
":newval":{"N":"8"}
}
```
Now suppose that Bob issues a similar `UpdateItem` request later, but changes the `Price` to 12. For Bob, the `--expression-attribute-values` parameter looks like the following.
```
{
":newval":{"N":"12"}
}
```
Bob's request succeeds, but Alice's earlier update is lost.
To request a conditional `PutItem`, `DeleteItem`, or `UpdateItem`, you specify a condition expression. A *condition expression* is a string containing attribute names, conditional operators, and built-in functions. The entire expression must evaluate to true. Otherwise, the operation fails.
Now consider the following diagram, showing how conditional writes would prevent Alice's update from being overwritten.
![\[Conditional write preventing user Bob’s update from overwriting user Alice’s change to the same item.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/update-yes-condition.png)
Alice first tries to update `Price` to 8, but only if the current `Price` is 10.
```
aws dynamodb update-item \
--table-name ProductCatalog \
--key '{"Id":{"N":"1"}}' \
--update-expression "SET Price = :newval" \
--condition-expression "Price = :currval" \
--expression-attribute-values file://expression-attribute-values.json
```
The arguments for `--expression-attribute-values` are stored in the `expression-attribute-values.json` file.
```
{
":newval":{"N":"8"},
":currval":{"N":"10"}
}
```
Alice's update succeeds because the condition evaluates to true.
Next, Bob attempts to update the `Price` to 12, but only if the current `Price` is 10. For Bob, the `--expression-attribute-values` parameter looks like the following.
```
{
":newval":{"N":"12"},
":currval":{"N":"10"}
}
```
Because Alice has previously changed the `Price` to 8, the condition expression evaluates to false, and Bob's update fails.
For more information, see [DynamoDB condition expression CLI example](Expressions.ConditionExpressions.md).
### Conditional write idempotence
Conditional writes can be *idempotent* if the conditional check is on the same attribute that is being updated. This means that DynamoDB performs a given write request only if certain attribute values in the item match what you expect them to be at the time of the request.
For example, suppose that you issue an `UpdateItem` request to increase the `Price` of an item by 3, but only if the `Price` is currently 20. After you send the request, but before you get the results back, a network error occurs, and you don't know whether the request was successful. Because this conditional write is idempotent, you can retry the same `UpdateItem` request, and DynamoDB updates the item only if the `Price` is currently 20.
### Capacity units consumed by conditional writes
If a `ConditionExpression` evaluates to false during a conditional write, DynamoDB still consumes write capacity from the table. The amount consumed is dependent on the size of the existing item (or a minimum of 1). For example, if an existing item is 300kb and the new item you are trying to create or update is 310kb, the write capacity units consumed will be the 300 if the condition fails, and 310 if the condition succeeds. If this is a new item (no existing item), then the write capacity units consumed will be 1 if the condition fails and 310 if the condition succeeds.
**Note**
Write operations consume *write* capacity units only. They never consume *read* capacity units.
A failed conditional write returns a `ConditionalCheckFailedException`. When this occurs, you don't receive any information in the response about the write capacity that was consumed. .
To return the number of write capacity units consumed during a conditional write, you use the `ReturnConsumedCapacity` parameter:
+ `TOTAL` — Returns the total number of write capacity units consumed.
+ `INDEXES` — Returns the total number of write capacity units consumed, with subtotals for the table and any secondary indexes that were affected by the operation.
+ `NONE` — No write capacity details are returned. (This is the default.)
**Note**
Unlike a global secondary index, a local secondary index shares its provisioned throughput capacity with its table. Read and write activity on a local secondary index consumes provisioned throughput capacity from the table.
# 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"}
}
```
# Using time to live (TTL) in DynamoDB
Time To Live (TTL) for DynamoDB is a cost-effective method for deleting items that are no longer relevant. TTL allows you to define a per-item expiration timestamp that indicates when an item is no longer needed. DynamoDB automatically deletes expired items within a few days of their expiration time, without consuming write throughput.
To use TTL, first enable it on a table and then define a specific attribute to store the TTL expiration timestamp. The timestamp must be stored as a [Number](HowItWorks.NamingRulesDataTypes.md#HowItWorks.DataTypes) data type in [Unix epoch time format](https://en.wikipedia.org/wiki/Unix_time) at the seconds granularity. Items with a TTL attribute that is not a Number type are ignored by the TTL process. Each time an item is created or updated, you can compute the expiration time and save it in the TTL attribute.
Items with valid, expired TTL attributes may be deleted by the system at any time, typically within a few days of their expiration. You can still update the expired items that are pending deletion, including changing or removing their TTL attributes. While updating an expired item, we recommended that you use a condition expression to make sure the item has not been subsequently deleted. Use filter expressions to remove expired items from [Scan](Scan.md#Scan.FilterExpression) and [Query](Query.FilterExpression.md) results.
Deleted items work similarly to those deleted through typical delete operations. Once deleted, items go into DynamoDB Streams as service deletions instead of user deletes, and are removed from local secondary indexes and global secondary indexes just like other delete operations.
If you are using [Global Tables version 2019.11.21 (Current)](GlobalTables.md) of global tables and you also use the TTL feature, DynamoDB replicates TTL deletes to all replica tables. The initial TTL delete does not consume Write Capacity Units (WCU) in the region in which the TTL expiry occurs. However, the replicated TTL delete to the replica table(s) consumes a replicated Write Capacity Unit when using provisioned capacity, or Replicated Write Unit when using on-demand capacity mode, in each of the replica regions and applicable charges will apply.
For more information about TTL, see these topics:
**Topics**
+ [
# Enable time to live (TTL) in DynamoDB
](time-to-live-ttl-how-to.md)
+ [
# Computing time to live (TTL) in DynamoDB
](time-to-live-ttl-before-you-start.md)
+ [
# Working with expired items and time to live (TTL)
](ttl-expired-items.md)
# Enable time to live (TTL) in DynamoDB
**Note**
To assist in debugging and verification of proper operation of the TTL feature, the values provided for the item TTL are logged in plaintext in DynamoDB diagnostic logs.
You can enable TTL in the Amazon DynamoDB Console, the AWS Command Line Interface (AWS CLI), or using the [Amazon DynamoDB API Reference](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/) with any of the supposed AWS SDKs. It takes approximately one hour to enable TTL across all partitions.
## Enable DynamoDB TTL using the AWS console
1. Sign in to the AWS Management Console and open the DynamoDB console at [https://console.aws.amazon.com/dynamodb/](https://console.aws.amazon.com/dynamodb/).
1. Choose **Tables**, and then choose the table that you want to modify.
1. In the **Additional settings** tab, in the **Time to Live (TTL)** section, choose **Turn on** to enable TTL.
1. When enabling TTL on a table, DynamoDB requires you to identify a specific attribute name that the service will look for when determining if an item is eligible for expiration. The TTL attribute name, shown below, is case sensitive and must match the attribute defined in your read and write operations. A mismatch will cause expired items to go undeleted. Renaming the TTL attribute requires you to disable TTL and then re-enable it with the new attribute going forward. TTL will continue to process deletions for approximately 30 minutes once it is disabled. TTL must be reconfigured on restored tables.
![\[Case-sensitive TTL attribute name that DynamoDB uses to determine an item's eligiblity for expiration.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/EnableTTL-Settings.png)
1. (Optional) You can perform a test by simulating the date and time of the expiration and matching a few items. This provides you with a sample list of items and confirms that there are items containing the TTL attribute name provided along with the expiration time.
After TTL is enabled, the TTL attribute is marked **TTL** when you view items on the DynamoDB console. You can view the date and time that an item expires by hovering your pointer over the attribute.
## Enable DynamoDB TTL using the API
------
#### [ Python ]
You can enable TTL with code, using the [UpdateTimeToLive](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/dynamodb/client/update_time_to_live.html) operation.
```
import boto3
def enable_ttl(table_name, ttl_attribute_name):
"""
Enables TTL on DynamoDB table for a given attribute name
on success, returns a status code of 200
on error, throws an exception
:param table_name: Name of the DynamoDB table
:param ttl_attribute_name: The name of the TTL attribute being provided to the table.
"""
try:
dynamodb = boto3.client('dynamodb')
# Enable TTL on an existing DynamoDB table
response = dynamodb.update_time_to_live(
TableName=table_name,
TimeToLiveSpecification={
'Enabled': True,
'AttributeName': ttl_attribute_name
}
)
# In the returned response, check for a successful status code.
if response['ResponseMetadata']['HTTPStatusCode'] == 200:
print("TTL has been enabled successfully.")
else:
print(f"Failed to enable TTL, status code {response['ResponseMetadata']['HTTPStatusCode']}")
except Exception as ex:
print("Couldn't enable TTL in table %s. Here's why: %s" % (table_name, ex))
raise
# your values
enable_ttl('your-table-name', 'expirationDate')
```
You can confirm TTL is enabled by using the [DescribeTimeToLive](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/dynamodb/client/describe_time_to_live.html) operation, which describes the TTL status on a table. The `TimeToLive` status is either `ENABLED` or `DISABLED`.
```
# create a DynamoDB client
dynamodb = boto3.client('dynamodb')
# set the table name
table_name = 'YourTable'
# describe TTL
response = dynamodb.describe_time_to_live(TableName=table_name)
```
------
#### [ JavaScript ]
You can enable TTL with code, using the [UpdateTimeToLiveCommand](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/Package/-aws-sdk-client-dynamodb/Class/UpdateTimeToLiveCommand/) operation.
```
import { DynamoDBClient, UpdateTimeToLiveCommand } from "@aws-sdk/client-dynamodb";
const enableTTL = async (tableName, ttlAttribute) => {
const client = new DynamoDBClient({});
const params = {
TableName: tableName,
TimeToLiveSpecification: {
Enabled: true,
AttributeName: ttlAttribute
}
};
try {
const response = await client.send(new UpdateTimeToLiveCommand(params));
if (response.$metadata.httpStatusCode === 200) {
console.log(`TTL enabled successfully for table ${tableName}, using attribute name ${ttlAttribute}.`);
} else {
console.log(`Failed to enable TTL for table ${tableName}, response object: ${response}`);
}
return response;
} catch (e) {
console.error(`Error enabling TTL: ${e}`);
throw e;
}
};
// call with your own values
enableTTL('ExampleTable', 'exampleTtlAttribute');
```
------
## Enable Time to Live using the AWS CLI
1. Enable TTL on the `TTLExample` table.
```
aws dynamodb update-time-to-live --table-name TTLExample --time-to-live-specification "Enabled=true, AttributeName=ttl"
```
1. Describe TTL on the `TTLExample` table.
```
aws dynamodb describe-time-to-live --table-name TTLExample
{
"TimeToLiveDescription": {
"AttributeName": "ttl",
"TimeToLiveStatus": "ENABLED"
}
}
```
1. Add an item to the `TTLExample` table with the Time to Live attribute set using the BASH shell and the AWS CLI.
```
EXP=`date -d '+5 days' +%s`
aws dynamodb put-item --table-name "TTLExample" --item '{"id": {"N": "1"}, "ttl": {"N": "'$EXP'"}}'
```
This example starts with the current date and adds 5 days to it to create an expiration time. Then, it converts the expiration time to epoch time format to finally add an item to the "`TTLExample`" table.
**Note**
One way to set expiration values for Time to Live is to calculate the number of seconds to add to the expiration time. For example, 5 days is 432,000 seconds. However, it is often preferable to start with a date and work from there.
It is fairly simple to get the current time in epoch time format, as in the following examples.
+ Linux Terminal: `date +%s`
+ Python: `import time; int(time.time())`
+ Java: `System.currentTimeMillis() / 1000L`
+ JavaScript: `Math.floor(Date.now() / 1000)`
## Enable DynamoDB TTL using CloudFormation
```
AWSTemplateFormatVersion: "2010-09-09"
Resources:
TTLExampleTable:
Type: AWS::DynamoDB::Table
Description: "A DynamoDB table with TTL Specification enabled"
Properties:
AttributeDefinitions:
- AttributeName: "Album"
AttributeType: "S"
- AttributeName: "Artist"
AttributeType: "S"
KeySchema:
- AttributeName: "Album"
KeyType: "HASH"
- AttributeName: "Artist"
KeyType: "RANGE"
ProvisionedThroughput:
ReadCapacityUnits: "5"
WriteCapacityUnits: "5"
TimeToLiveSpecification:
AttributeName: "TTLExampleAttribute"
Enabled: true
```
Additional details on using TTL within your CloudFormation templates can be found [here](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-dynamodb-table-timetolivespecification.html).
# Computing time to live (TTL) in DynamoDB
A common way to implement TTL is to set an expiration time for items based on when they were created or last updated. This can be done by adding time to the `createdAt` and `updatedAt` timestamps. For example, the TTL for newly created items can be set to `createdAt` \$1 90 days. When the item is updated the TTL can be recalculated to `updatedAt` \$1 90 days.
The computed expiration time must be in epoch format, in seconds. To be considered for expiry and deletion, the TTL can't be more than five years in the past. If you use any other format, the TTL processes ignore the item. If you set the expiration time to sometime in the future when you want the item to expire, the item expires after that time. For example, say that you set the expiration time to 1724241326 (which is Monday, August 21, 2024 11:55:26 (UTC)). The item expires after the specified time. There is no minimum TTL duration. You can set the expiration time to any future time, such as 5 minutes from the current time. However, DynamoDB typically deletes expired items within 48 hours after their expiration time, not immediately when the item expires.
**Topics**
+ [
## Create an item and set the Time to Live
](#time-to-live-ttl-before-you-start-create)
+ [
## Update an item and refresh the Time to Live
](#time-to-live-ttl-before-you-start-update)
## Create an item and set the Time to Live
The following example demonstrates how to calculate the expiration time when creating a new item, using `expireAt` as the TTL attribute name. An assignment statement obtains the current time as a variable. In the example, the expiration time is calculated as 90 days from the current time. The time is then converted to epoch format and saved as an integer data type in the TTL attribute.
The following code examples show how to create an item with TTL.
------
#### [ Java ]
**SDK for Java 2.x**
```
package com.amazon.samplelib.ttl;
import com.amazon.samplelib.CodeSampleUtils;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
import software.amazon.awssdk.services.dynamodb.model.PutItemRequest;
import software.amazon.awssdk.services.dynamodb.model.PutItemResponse;
import software.amazon.awssdk.services.dynamodb.model.ResourceNotFoundException;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;
/**
* Creates an item in a DynamoDB table with TTL attributes.
* This class demonstrates how to add TTL expiration timestamps to DynamoDB items.
*/
public class CreateTTL {
private static final String USAGE =
"""
Usage:
Where:
tableName - The Amazon DynamoDB table being queried.
primaryKey - The name of the primary key. Also known as the hash or partition key.
sortKey - The name of the sort key. Also known as the range attribute.
region (optional) - The AWS region that the Amazon DynamoDB table is located in. (Default: us-east-1)
""";
private static final int DAYS_TO_EXPIRE = 90;
private static final int SECONDS_PER_DAY = 24 * 60 * 60;
private static final String PRIMARY_KEY_ATTR = "primaryKey";
private static final String SORT_KEY_ATTR = "sortKey";
private static final String CREATION_DATE_ATTR = "creationDate";
private static final String EXPIRE_AT_ATTR = "expireAt";
private static final String SUCCESS_MESSAGE = "%s PutItem operation with TTL successful.";
private static final String TABLE_NOT_FOUND_ERROR = "Error: The Amazon DynamoDB table \"%s\" can't be found.";
private final DynamoDbClient dynamoDbClient;
/**
* Constructs a CreateTTL instance with the specified DynamoDB client.
*
* @param dynamoDbClient The DynamoDB client to use
*/
public CreateTTL(final DynamoDbClient dynamoDbClient) {
this.dynamoDbClient = dynamoDbClient;
}
/**
* Constructs a CreateTTL with a default DynamoDB client.
*/
public CreateTTL() {
this.dynamoDbClient = null;
}
/**
* Main method to demonstrate creating an item with TTL.
*
* @param args Command line arguments
*/
public static void main(final String[] args) {
try {
int result = new CreateTTL().processArgs(args);
System.exit(result);
} catch (Exception e) {
System.err.println(e.getMessage());
System.exit(1);
}
}
/**
* Process command line arguments and create an item with TTL.
*
* @param args Command line arguments
* @return 0 if successful, non-zero otherwise
* @throws ResourceNotFoundException If the table doesn't exist
* @throws DynamoDbException If an error occurs during the operation
* @throws IllegalArgumentException If arguments are invalid
*/
public int processArgs(final String[] args) {
// Argument validation (remove or replace this line when reusing this code)
CodeSampleUtils.validateArgs(args, new int[] {3, 4}, USAGE);
final String tableName = args[0];
final String primaryKey = args[1];
final String sortKey = args[2];
final Region region = Optional.ofNullable(args.length > 3 ? args[3] : null)
.map(Region::of)
.orElse(Region.US_EAST_1);
try (DynamoDbClient ddb = dynamoDbClient != null
? dynamoDbClient
: DynamoDbClient.builder().region(region).build()) {
final CreateTTL createTTL = new CreateTTL(ddb);
createTTL.createItemWithTTL(tableName, primaryKey, sortKey);
return 0;
} catch (Exception e) {
throw e;
}
}
/**
* Creates an item in the specified table with TTL attributes.
*
* @param tableName The name of the table
* @param primaryKeyValue The value for the primary key
* @param sortKeyValue The value for the sort key
* @return The response from the PutItem operation
* @throws ResourceNotFoundException If the table doesn't exist
* @throws DynamoDbException If an error occurs during the operation
*/
public PutItemResponse createItemWithTTL(
final String tableName, final String primaryKeyValue, final String sortKeyValue) {
// Get current time in epoch second format
final long createDate = System.currentTimeMillis() / 1000;
// Calculate expiration time 90 days from now in epoch second format
final long expireDate = createDate + (DAYS_TO_EXPIRE * SECONDS_PER_DAY);
final Map itemMap = new HashMap<>();
itemMap.put(
PRIMARY_KEY_ATTR, AttributeValue.builder().s(primaryKeyValue).build());
itemMap.put(SORT_KEY_ATTR, AttributeValue.builder().s(sortKeyValue).build());
itemMap.put(
CREATION_DATE_ATTR,
AttributeValue.builder().n(String.valueOf(createDate)).build());
itemMap.put(
EXPIRE_AT_ATTR,
AttributeValue.builder().n(String.valueOf(expireDate)).build());
final PutItemRequest request =
PutItemRequest.builder().tableName(tableName).item(itemMap).build();
try {
final PutItemResponse response = dynamoDbClient.putItem(request);
System.out.println(String.format(SUCCESS_MESSAGE, tableName));
return response;
} catch (ResourceNotFoundException e) {
System.err.format(TABLE_NOT_FOUND_ERROR, tableName);
throw e;
} catch (DynamoDbException e) {
System.err.println(e.getMessage());
throw e;
}
}
}
```
+ For API details, see [PutItem](https://docs.aws.amazon.com/goto/SdkForJavaV2/dynamodb-2012-08-10/PutItem) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
```
import { DynamoDBClient, PutItemCommand } from "@aws-sdk/client-dynamodb";
export function createDynamoDBItem(table_name, region, partition_key, sort_key) {
const client = new DynamoDBClient({
region: region,
endpoint: `https://dynamodb.${region}.amazonaws.com`
});
// Get the current time in epoch second format
const current_time = Math.floor(new Date().getTime() / 1000);
// Calculate the expireAt time (90 days from now) in epoch second format
const expire_at = Math.floor((new Date().getTime() + 90 * 24 * 60 * 60 * 1000) / 1000);
// Create DynamoDB item
const item = {
'partitionKey': {'S': partition_key},
'sortKey': {'S': sort_key},
'createdAt': {'N': current_time.toString()},
'expireAt': {'N': expire_at.toString()}
};
const putItemCommand = new PutItemCommand({
TableName: table_name,
Item: item,
ProvisionedThroughput: {
ReadCapacityUnits: 1,
WriteCapacityUnits: 1,
},
});
client.send(putItemCommand, function(err, data) {
if (err) {
console.log("Exception encountered when creating item %s, here's what happened: ", data, err);
throw err;
} else {
console.log("Item created successfully: %s.", data);
return data;
}
});
}
// Example usage (commented out for testing)
// createDynamoDBItem('your-table-name', 'us-east-1', 'your-partition-key-value', 'your-sort-key-value');
```
+ For API details, see [PutItem](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/PutItemCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
```
from datetime import datetime, timedelta
import boto3
def create_dynamodb_item(table_name, region, primary_key, sort_key):
"""
Creates a DynamoDB item with an attached expiry attribute.
:param table_name: Table name for the boto3 resource to target when creating an item
:param region: string representing the AWS region. Example: `us-east-1`
:param primary_key: one attribute known as the partition key.
:param sort_key: Also known as a range attribute.
:return: Void (nothing)
"""
try:
dynamodb = boto3.resource("dynamodb", region_name=region)
table = dynamodb.Table(table_name)
# Get the current time in epoch second format
current_time = int(datetime.now().timestamp())
# Calculate the expiration time (90 days from now) in epoch second format
expiration_time = int((datetime.now() + timedelta(days=90)).timestamp())
item = {
"primaryKey": primary_key,
"sortKey": sort_key,
"creationDate": current_time,
"expireAt": expiration_time,
}
response = table.put_item(Item=item)
print("Item created successfully.")
return response
except Exception as e:
print(f"Error creating item: {e}")
raise e
# Use your own values
create_dynamodb_item(
"your-table-name", "us-west-2", "your-partition-key-value", "your-sort-key-value"
)
```
+ For API details, see [PutItem](https://docs.aws.amazon.com/goto/boto3/dynamodb-2012-08-10/PutItem) in *AWS SDK for Python (Boto3) API Reference*.
------
## Update an item and refresh the Time to Live
This example is a continuation of the one from the [previous section](#time-to-live-ttl-before-you-start-create). The expiration time can be recomputed if the item is updated. The following example recomputes the `expireAt` timestamp to be 90 days from the current time.
The following code examples show how to update an item's TTL.
------
#### [ Java ]
**SDK for Java 2.x**
Update TTL on an existing DynamoDB item in a table.
```
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
import software.amazon.awssdk.services.dynamodb.model.ResourceNotFoundException;
import software.amazon.awssdk.services.dynamodb.model.UpdateItemRequest;
import software.amazon.awssdk.services.dynamodb.model.UpdateItemResponse;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;
public UpdateItemResponse updateItemWithTTL(
final String tableName, final String primaryKeyValue, final String sortKeyValue) {
// Get current time in epoch second format
final long currentTime = System.currentTimeMillis() / 1000;
// Calculate expiration time 90 days from now in epoch second format
final long expireDate = currentTime + (DAYS_TO_EXPIRE * SECONDS_PER_DAY);
// Create the key map for the item to update
final Map keyMap = new HashMap<>();
keyMap.put(PRIMARY_KEY_ATTR, AttributeValue.builder().s(primaryKeyValue).build());
keyMap.put(SORT_KEY_ATTR, AttributeValue.builder().s(sortKeyValue).build());
// Create the expression attribute values
final Map expressionAttributeValues = new HashMap<>();
expressionAttributeValues.put(
":c", AttributeValue.builder().n(String.valueOf(currentTime)).build());
expressionAttributeValues.put(
":e", AttributeValue.builder().n(String.valueOf(expireDate)).build());
final UpdateItemRequest request = UpdateItemRequest.builder()
.tableName(tableName)
.key(keyMap)
.updateExpression(UPDATE_EXPRESSION)
.expressionAttributeValues(expressionAttributeValues)
.build();
try {
final UpdateItemResponse response = dynamoDbClient.updateItem(request);
System.out.println(String.format(SUCCESS_MESSAGE, tableName));
return response;
} catch (ResourceNotFoundException e) {
System.err.format(TABLE_NOT_FOUND_ERROR, tableName);
throw e;
} catch (DynamoDbException e) {
System.err.println(e.getMessage());
throw e;
}
}
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/goto/SdkForJavaV2/dynamodb-2012-08-10/UpdateItem) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
```
import { DynamoDBClient, UpdateItemCommand } from "@aws-sdk/client-dynamodb";
import { marshall, unmarshall } from "@aws-sdk/util-dynamodb";
export const updateItem = async (tableName, partitionKey, sortKey, region = 'us-east-1') => {
const client = new DynamoDBClient({
region: region,
endpoint: `https://dynamodb.${region}.amazonaws.com`
});
const currentTime = Math.floor(Date.now() / 1000);
const expireAt = Math.floor((Date.now() + 90 * 24 * 60 * 60 * 1000) / 1000);
const params = {
TableName: tableName,
Key: marshall({
partitionKey: partitionKey,
sortKey: sortKey
}),
UpdateExpression: "SET updatedAt = :c, expireAt = :e",
ExpressionAttributeValues: marshall({
":c": currentTime,
":e": expireAt
}),
};
try {
const data = await client.send(new UpdateItemCommand(params));
const responseData = unmarshall(data.Attributes);
console.log("Item updated successfully: %s", responseData);
return responseData;
} catch (err) {
console.error("Error updating item:", err);
throw err;
}
}
// Example usage (commented out for testing)
// updateItem('your-table-name', 'your-partition-key-value', 'your-sort-key-value');
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/UpdateItemCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
```
from datetime import datetime, timedelta
import boto3
def update_dynamodb_item(table_name, region, primary_key, sort_key):
"""
Update an existing DynamoDB item with a TTL.
:param table_name: Name of the DynamoDB table
:param region: AWS Region of the table - example `us-east-1`
:param primary_key: one attribute known as the partition key.
:param sort_key: Also known as a range attribute.
:return: Void (nothing)
"""
try:
# Create the DynamoDB resource.
dynamodb = boto3.resource("dynamodb", region_name=region)
table = dynamodb.Table(table_name)
# Get the current time in epoch second format
current_time = int(datetime.now().timestamp())
# Calculate the expireAt time (90 days from now) in epoch second format
expire_at = int((datetime.now() + timedelta(days=90)).timestamp())
table.update_item(
Key={"partitionKey": primary_key, "sortKey": sort_key},
UpdateExpression="set updatedAt=:c, expireAt=:e",
ExpressionAttributeValues={":c": current_time, ":e": expire_at},
)
print("Item updated successfully.")
except Exception as e:
print(f"Error updating item: {e}")
# Replace with your own values
update_dynamodb_item(
"your-table-name", "us-west-2", "your-partition-key-value", "your-sort-key-value"
)
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/goto/boto3/dynamodb-2012-08-10/UpdateItem) in *AWS SDK for Python (Boto3) API Reference*.
------
The TTL examples discussed in this introduction demonstrate a method to ensure only recently updated items are kept in a table. Updated items have their lifespan extended, whereas items not updated post-creation expire and are deleted at no cost, reducing storage and maintaining clean tables.
# Working with expired items and time to live (TTL)
Expired items that are pending deletion can be filtered from read and write operations. This is useful in scenarios when expired data is no longer valid and should not be used. If they are not filtered, they’ll continue to show in read and write operations until they are deleted by the background process.
**Note**
These items still count towards storage and read costs until they are deleted.
TTL deletions can be identified in DynamoDB Streams, but only in the Region where the deletion occurred. TTL deletions that are replicated to global table regions are not identifiable in DynamoDB streams in the regions the deletion is replicated to.
## Filter expired items from read operations
For read operations such as [Scan](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Scan.html) and [Query](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html), a filter expression can filter out expired items that are pending deletion. As shown in the following code snippet, the filter expression can filter out items where the TTL time is equal to or less than the current time. For example, the Python SDK code includes an assignment statement that obtains the current time as a variable (`now`), and converts it into `int` for epoch time format.
The following code examples show how to query for TTL items.
------
#### [ Java ]
**SDK for Java 2.x**
Query Filtered Expression to gather TTL items in a DynamoDB table using AWS SDK for Java 2.x.
```
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
import software.amazon.awssdk.services.dynamodb.model.QueryRequest;
import software.amazon.awssdk.services.dynamodb.model.QueryResponse;
import software.amazon.awssdk.services.dynamodb.model.ResourceNotFoundException;
import java.util.Map;
import java.util.Optional;
final QueryRequest request = QueryRequest.builder()
.tableName(tableName)
.keyConditionExpression(KEY_CONDITION_EXPRESSION)
.filterExpression(FILTER_EXPRESSION)
.expressionAttributeNames(expressionAttributeNames)
.expressionAttributeValues(expressionAttributeValues)
.build();
try (DynamoDbClient ddb = dynamoDbClient != null
? dynamoDbClient
: DynamoDbClient.builder().region(region).build()) {
final QueryResponse response = ddb.query(request);
System.out.println("Query successful. Found " + response.count() + " items that have not expired yet.");
// Print each item
response.items().forEach(item -> {
System.out.println("Item: " + item);
});
return 0;
} catch (ResourceNotFoundException e) {
System.err.format(TABLE_NOT_FOUND_ERROR, tableName);
throw e;
} catch (DynamoDbException e) {
System.err.println(e.getMessage());
throw e;
}
```
+ For API details, see [Query](https://docs.aws.amazon.com/goto/SdkForJavaV2/dynamodb-2012-08-10/Query) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
Query Filtered Expression to gather TTL items in a DynamoDB table using AWS SDK for JavaScript.
```
import { DynamoDBClient, QueryCommand } from "@aws-sdk/client-dynamodb";
import { marshall, unmarshall } from "@aws-sdk/util-dynamodb";
export const queryFiltered = async (tableName, primaryKey, region = 'us-east-1') => {
const client = new DynamoDBClient({
region: region,
endpoint: `https://dynamodb.${region}.amazonaws.com`
});
const currentTime = Math.floor(Date.now() / 1000);
const params = {
TableName: tableName,
KeyConditionExpression: "#pk = :pk",
FilterExpression: "#ea > :ea",
ExpressionAttributeNames: {
"#pk": "primaryKey",
"#ea": "expireAt"
},
ExpressionAttributeValues: marshall({
":pk": primaryKey,
":ea": currentTime
})
};
try {
const { Items } = await client.send(new QueryCommand(params));
Items.forEach(item => {
console.log(unmarshall(item))
});
return Items;
} catch (err) {
console.error(`Error querying items: ${err}`);
throw err;
}
}
// Example usage (commented out for testing)
// queryFiltered('your-table-name', 'your-partition-key-value');
```
+ For API details, see [Query](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/QueryCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
Query Filtered Expression to gather TTL items in a DynamoDB table using AWS SDK for Python (Boto3).
```
from datetime import datetime
import boto3
def query_dynamodb_items(table_name, partition_key):
"""
:param table_name: Name of the DynamoDB table
:param partition_key:
:return:
"""
try:
# Initialize a DynamoDB resource
dynamodb = boto3.resource("dynamodb", region_name="us-east-1")
# Specify your table
table = dynamodb.Table(table_name)
# Get the current time in epoch format
current_time = int(datetime.now().timestamp())
# Perform the query operation with a filter expression to exclude expired items
# response = table.query(
# KeyConditionExpression=boto3.dynamodb.conditions.Key('partitionKey').eq(partition_key),
# FilterExpression=boto3.dynamodb.conditions.Attr('expireAt').gt(current_time)
# )
response = table.query(
KeyConditionExpression=dynamodb.conditions.Key("partitionKey").eq(partition_key),
FilterExpression=dynamodb.conditions.Attr("expireAt").gt(current_time),
)
# Print the items that are not expired
for item in response["Items"]:
print(item)
except Exception as e:
print(f"Error querying items: {e}")
# Call the function with your values
query_dynamodb_items("Music", "your-partition-key-value")
```
+ For API details, see [Query](https://docs.aws.amazon.com/goto/boto3/dynamodb-2012-08-10/Query) in *AWS SDK for Python (Boto3) API Reference*.
------
## Conditionally write to expired items
A condition expression can be used to avoid writes against expired items. The code snippet below is a conditional update that checks whether the expiration time is greater than the current time. If true, the write operation will continue.
The following code examples show how to conditionally update an item's TTL.
------
#### [ Java ]
**SDK for Java 2.x**
Update TTL on on an existing DynamoDB Item in a table, with a condition.
```
package com.amazon.samplelib.ttl;
import com.amazon.samplelib.CodeSampleUtils;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.ConditionalCheckFailedException;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
import software.amazon.awssdk.services.dynamodb.model.ResourceNotFoundException;
import software.amazon.awssdk.services.dynamodb.model.UpdateItemRequest;
import software.amazon.awssdk.services.dynamodb.model.UpdateItemResponse;
import java.util.Map;
import java.util.Optional;
/**
* Updates an item in a DynamoDB table with TTL attributes using a conditional expression.
* This class demonstrates how to conditionally update TTL expiration timestamps.
*/
public class UpdateTTLConditional {
private static final String USAGE =
"""
Usage:
Where:
tableName - The Amazon DynamoDB table being queried.
primaryKey - The name of the primary key. Also known as the hash or partition key.
sortKey - The name of the sort key. Also known as the range attribute.
region (optional) - The AWS region that the Amazon DynamoDB table is located in. (Default: us-east-1)
""";
private static final int DAYS_TO_EXPIRE = 90;
private static final int SECONDS_PER_DAY = 24 * 60 * 60;
private static final String PRIMARY_KEY_ATTR = "primaryKey";
private static final String SORT_KEY_ATTR = "sortKey";
private static final String UPDATED_AT_ATTR = "updatedAt";
private static final String EXPIRE_AT_ATTR = "expireAt";
private static final String UPDATE_EXPRESSION = "SET " + UPDATED_AT_ATTR + "=:c, " + EXPIRE_AT_ATTR + "=:e";
private static final String CONDITION_EXPRESSION = "attribute_exists(" + PRIMARY_KEY_ATTR + ")";
private static final String SUCCESS_MESSAGE = "%s UpdateItem operation with TTL successful.";
private static final String CONDITION_FAILED_MESSAGE = "Condition check failed. Item does not exist.";
private static final String TABLE_NOT_FOUND_ERROR = "Error: The Amazon DynamoDB table \"%s\" can't be found.";
private final DynamoDbClient dynamoDbClient;
/**
* Constructs an UpdateTTLConditional with a default DynamoDB client.
*/
public UpdateTTLConditional() {
this.dynamoDbClient = null;
}
/**
* Constructs an UpdateTTLConditional with the specified DynamoDB client.
*
* @param dynamoDbClient The DynamoDB client to use
*/
public UpdateTTLConditional(final DynamoDbClient dynamoDbClient) {
this.dynamoDbClient = dynamoDbClient;
}
/**
* Main method to demonstrate conditionally updating an item with TTL.
*
* @param args Command line arguments
*/
public static void main(final String[] args) {
try {
int result = new UpdateTTLConditional().processArgs(args);
System.exit(result);
} catch (Exception e) {
System.err.println(e.getMessage());
System.exit(1);
}
}
/**
* Process command line arguments and conditionally update an item with TTL.
*
* @param args Command line arguments
* @return 0 if successful, non-zero otherwise
* @throws ResourceNotFoundException If the table doesn't exist
* @throws DynamoDbException If an error occurs during the operation
* @throws IllegalArgumentException If arguments are invalid
*/
public int processArgs(final String[] args) {
// Argument validation (remove or replace this line when reusing this code)
CodeSampleUtils.validateArgs(args, new int[] {3, 4}, USAGE);
final String tableName = args[0];
final String primaryKey = args[1];
final String sortKey = args[2];
final Region region = Optional.ofNullable(args.length > 3 ? args[3] : null)
.map(Region::of)
.orElse(Region.US_EAST_1);
// Get current time in epoch second format
final long currentTime = System.currentTimeMillis() / 1000;
// Calculate expiration time 90 days from now in epoch second format
final long expireDate = currentTime + (DAYS_TO_EXPIRE * SECONDS_PER_DAY);
// Create the key map for the item to update
final Map keyMap = Map.of(
PRIMARY_KEY_ATTR, AttributeValue.builder().s(primaryKey).build(),
SORT_KEY_ATTR, AttributeValue.builder().s(sortKey).build());
// Create the expression attribute values
final Map expressionAttributeValues = Map.of(
":c", AttributeValue.builder().n(String.valueOf(currentTime)).build(),
":e", AttributeValue.builder().n(String.valueOf(expireDate)).build());
final UpdateItemRequest request = UpdateItemRequest.builder()
.tableName(tableName)
.key(keyMap)
.updateExpression(UPDATE_EXPRESSION)
.conditionExpression(CONDITION_EXPRESSION)
.expressionAttributeValues(expressionAttributeValues)
.build();
try (DynamoDbClient ddb = dynamoDbClient != null
? dynamoDbClient
: DynamoDbClient.builder().region(region).build()) {
final UpdateItemResponse response = ddb.updateItem(request);
System.out.println(String.format(SUCCESS_MESSAGE, tableName));
return 0;
} catch (ConditionalCheckFailedException e) {
System.err.println(CONDITION_FAILED_MESSAGE);
throw e;
} catch (ResourceNotFoundException e) {
System.err.format(TABLE_NOT_FOUND_ERROR, tableName);
throw e;
} catch (DynamoDbException e) {
System.err.println(e.getMessage());
throw e;
}
}
}
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/goto/SdkForJavaV2/dynamodb-2012-08-10/UpdateItem) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
Update TTL on on an existing DynamoDB Item in a table, with a condition.
```
import { DynamoDBClient, UpdateItemCommand } from "@aws-sdk/client-dynamodb";
import { marshall, unmarshall } from "@aws-sdk/util-dynamodb";
export const updateItemConditional = async (tableName, partitionKey, sortKey, region = 'us-east-1', newAttribute = 'default-value') => {
const client = new DynamoDBClient({
region: region,
endpoint: `https://dynamodb.${region}.amazonaws.com`
});
const currentTime = Math.floor(Date.now() / 1000);
const params = {
TableName: tableName,
Key: marshall({
artist: partitionKey,
album: sortKey
}),
UpdateExpression: "SET newAttribute = :newAttribute",
ConditionExpression: "expireAt > :expiration",
ExpressionAttributeValues: marshall({
':newAttribute': newAttribute,
':expiration': currentTime
}),
ReturnValues: "ALL_NEW"
};
try {
const response = await client.send(new UpdateItemCommand(params));
const responseData = unmarshall(response.Attributes);
console.log("Item updated successfully: ", responseData);
return responseData;
} catch (error) {
if (error.name === "ConditionalCheckFailedException") {
console.log("Condition check failed: Item's 'expireAt' is expired.");
} else {
console.error("Error updating item: ", error);
}
throw error;
}
};
// Example usage (commented out for testing)
// updateItemConditional('your-table-name', 'your-partition-key-value', 'your-sort-key-value');
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/UpdateItemCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
Update TTL on on an existing DynamoDB Item in a table, with a condition.
```
from datetime import datetime, timedelta
import boto3
from botocore.exceptions import ClientError
def update_dynamodb_item_ttl(table_name, region, primary_key, sort_key, ttl_attribute):
"""
Updates an existing record in a DynamoDB table with a new or updated TTL attribute.
:param table_name: Name of the DynamoDB table
:param region: AWS Region of the table - example `us-east-1`
:param primary_key: one attribute known as the partition key.
:param sort_key: Also known as a range attribute.
:param ttl_attribute: name of the TTL attribute in the target DynamoDB table
:return:
"""
try:
dynamodb = boto3.resource("dynamodb", region_name=region)
table = dynamodb.Table(table_name)
# Generate updated TTL in epoch second format
updated_expiration_time = int((datetime.now() + timedelta(days=90)).timestamp())
# Define the update expression for adding/updating a new attribute
update_expression = "SET newAttribute = :val1"
# Define the condition expression for checking if 'expireAt' is not expired
condition_expression = "expireAt > :val2"
# Define the expression attribute values
expression_attribute_values = {":val1": ttl_attribute, ":val2": updated_expiration_time}
response = table.update_item(
Key={"primaryKey": primary_key, "sortKey": sort_key},
UpdateExpression=update_expression,
ConditionExpression=condition_expression,
ExpressionAttributeValues=expression_attribute_values,
)
print("Item updated successfully.")
return response["ResponseMetadata"]["HTTPStatusCode"] # Ideally a 200 OK
except ClientError as e:
if e.response["Error"]["Code"] == "ConditionalCheckFailedException":
print("Condition check failed: Item's 'expireAt' is expired.")
else:
print(f"Error updating item: {e}")
except Exception as e:
print(f"Error updating item: {e}")
# replace with your values
update_dynamodb_item_ttl(
"your-table-name",
"us-east-1",
"your-partition-key-value",
"your-sort-key-value",
"your-ttl-attribute-value",
)
```
+ For API details, see [UpdateItem](https://docs.aws.amazon.com/goto/boto3/dynamodb-2012-08-10/UpdateItem) in *AWS SDK for Python (Boto3) API Reference*.
------
## Identifying deleted items in DynamoDB Streams
The streams record contains a user identity field `Records[].userIdentity`. Items that are deleted by the TTL process have the following fields:
```
Records[].userIdentity.type
"Service"
Records[].userIdentity.principalId
"dynamodb.amazonaws.com"
```
The following JSON shows the relevant portion of a single streams record:
```
"Records": [
{
...
"userIdentity": {
"type": "Service",
"principalId": "dynamodb.amazonaws.com"
}
...
}
]
```
# Querying tables in DynamoDB
You can use the `Query` API operation in Amazon DynamoDB to find items based on primary key values.
You must provide the name of the partition key attribute and a single value for that attribute. `Query` returns all items with that partition key value. Optionally, you can provide a sort key attribute and use a comparison operator to refine the search results.
For more information on how to use `Query`, such as the request syntax, response parameters, and additional examples, see [Query](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html) in the *Amazon DynamoDB API Reference*.
**Topics**
+ [
# Key condition expressions for the Query operation in DynamoDB
](Query.KeyConditionExpressions.md)
+ [
# Filter expressions for the Query operation in DynamoDB
](Query.FilterExpression.md)
+ [
# Paginating table query results in DynamoDB
](Query.Pagination.md)
+ [
# Other aspects of working with the Query operation in DynamoDB
](Query.Other.md)
# Key condition expressions for the Query operation in DynamoDB
You can use any attribute name in a key condition expression, provided that the first character is `a-z` or `A-Z` and the rest of the characters (starting from the second character, if present) are `a-z`, `A-Z`, or `0-9`. In addition, the attribute name must not be a DynamoDB reserved word. (For a complete list of these, see [Reserved words in DynamoDB](ReservedWords.md).) If an attribute name does not meet these requirements, you must define an expression attribute name as a placeholder. For more information, see [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md).
For items with a given partition key value, DynamoDB stores these items close together, in sorted order by sort key value. In a `Query` operation, DynamoDB retrieves the items in sorted order, and then processes the items using `KeyConditionExpression` and any `FilterExpression` that might be present. Only then are the `Query` results sent back to the client.
A `Query` operation always returns a result set. If no matching items are found, the result set is empty.
`Query` results are always sorted by the sort key value. If the data type of the sort key is `Number`, the results are returned in numeric order. Otherwise, the results are returned in order of UTF-8 bytes. By default, the sort order is ascending. To reverse the order, set the `ScanIndexForward` parameter to `false`.
A single `Query` operation can retrieve a maximum of 1 MB of data. This limit applies before any `FilterExpression` or `ProjectionExpression` is applied to the results. If `LastEvaluatedKey` is present in the response and is non-null, you must paginate the result set (see [Paginating table query results in DynamoDB](Query.Pagination.md)).
## Key condition expression examples
To specify the search criteria, you use a *key condition expression*—a string that determines the items to be read from the table or index.
You must specify the partition key name and value as an equality condition. You cannot use a non-key attribute in a key condition expression.
You can optionally provide a second condition for the sort key (if present). The sort key condition must use one of 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*.
The following function is also supported:
+ `begins_with (a, substr)`— true if the value of attribute `a` begins with a particular substring.
The following AWS Command Line Interface (AWS CLI) examples demonstrate the use of key condition expressions. These expressions use placeholders (such as `:name` and `:sub`) instead of actual values. For more information, see [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md) and [Using expression attribute values in DynamoDB](Expressions.ExpressionAttributeValues.md).
**Example**
Query the `Thread` table for a particular `ForumName` (partition key). All of the items with that `ForumName` value are read by the query because the sort key (`Subject`) is not included in `KeyConditionExpression`.
```
aws dynamodb query \
--table-name Thread \
--key-condition-expression "ForumName = :name" \
--expression-attribute-values '{":name":{"S":"Amazon DynamoDB"}}'
```
**Example**
Query the `Thread` table for a particular `ForumName` (partition key), but this time return only the items with a given `Subject` (sort key).
```
aws dynamodb query \
--table-name Thread \
--key-condition-expression "ForumName = :name and Subject = :sub" \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":name":{"S":"Amazon DynamoDB"},
":sub":{"S":"DynamoDB Thread 1"}
}
```
**Example**
Query the `Reply` table for a particular `Id` (partition key), but return only those items whose `ReplyDateTime` (sort key) begins with certain characters.
```
aws dynamodb query \
--table-name Reply \
--key-condition-expression "Id = :id and begins_with(ReplyDateTime, :dt)" \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":id":{"S":"Amazon DynamoDB#DynamoDB Thread 1"},
":dt":{"S":"2015-09"}
}
```
# Filter expressions for the Query operation in DynamoDB
If you need to further refine the `Query` results, you can optionally provide a filter expression. A *filter expression* determines which items within the `Query` results should be returned to you. All of the other results are discarded.
A filter expression is applied after a `Query` finishes, but before the results are returned. Therefore, a `Query` consumes the same amount of read capacity, regardless of whether a filter expression is present.
A `Query` operation can retrieve a maximum of 1 MB of data. This limit applies before the filter expression is evaluated.
A filter expression cannot contain partition key or sort key attributes. You need to specify those attributes in the key condition expression, not the filter expression.
The syntax for a filter expression is similar to that of a key condition expression. Filter expressions can use the same comparators, functions, and logical operators as a key condition expression. In addition, filter expressions can use the not-equals operator (`<>`), the `OR` operator, the `CONTAINS` operator, the `IN` operator, the `BEGINS_WITH` operator, the `BETWEEN` operator, the `EXISTS` operator, and the `SIZE` operator. For more information, see [Key condition expressions for the Query operation in DynamoDB](Query.KeyConditionExpressions.md) and [Syntax for filter and condition expressions](Expressions.OperatorsAndFunctions.md#Expressions.OperatorsAndFunctions.Syntax).
**Example**
The following AWS CLI example queries the `Thread` table for a particular `ForumName` (partition key) and `Subject` (sort key). Of the items that are found, only the most popular discussion threads are returned—in other words, only those threads with more than a certain number of `Views`.
```
aws dynamodb query \
--table-name Thread \
--key-condition-expression "ForumName = :fn and Subject begins_with :sub" \
--filter-expression "#v >= :num" \
--expression-attribute-names '{"#v": "Views"}' \
--expression-attribute-values file://values.json
```
The arguments for `--expression-attribute-values` are stored in the `values.json` file.
```
{
":fn":{"S":"Amazon DynamoDB"},
":sub":{"S":"DynamoDB Thread 1"},
":num":{"N":"3"}
}
```
Note that `Views` is a reserved word in DynamoDB (see [Reserved words in DynamoDB](ReservedWords.md)), so this example uses `#v` as a placeholder. For more information, see [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md).
**Note**
A filter expression removes items from the `Query` result set. If possible, avoid using `Query` where you expect to retrieve a large number of items but also need to discard most of those items.
# Paginating table query results in DynamoDB
DynamoDB *paginates* the results from `Query` operations. With pagination, the `Query` results are divided into "pages" of data that are 1 MB in size (or less). An application can process the first page of results, then the second page, and so on.
A single `Query` only returns a result set that fits within the 1 MB size limit. To determine whether there are more results, and to retrieve them one page at a time, applications should do the following:
1. Examine the low-level `Query` result:
+ If the result contains a `LastEvaluatedKey` element and it's non-null, proceed to step 2.
+ If there is *not* a `LastEvaluatedKey` in the result, there are no more items to be retrieved.
1. Construct a `Query` with the same `KeyConditionExpression`. However, this time, take the `LastEvaluatedKey` value from step 1 and use it as the `ExclusiveStartKey` parameter in the new `Query` request.
1. Run the new `Query` request.
1. Go to step 1.
In other words, the `LastEvaluatedKey` from a `Query` response should be used as the `ExclusiveStartKey` for the next `Query` request. If there is not a `LastEvaluatedKey` element in a `Query` response, then you have retrieved the final page of results. If `LastEvaluatedKey` is not empty, it does not necessarily mean that there is more data in the result set. The only way to know when you have reached the end of the result set is when `LastEvaluatedKey` is empty.
You can use the AWS CLI to view this behavior. The AWS CLI sends low-level `Query` requests to DynamoDB repeatedly, until `LastEvaluatedKey` is no longer present in the results. Consider the following AWS CLI example that retrieves movie titles from a particular year.
```
aws dynamodb query --table-name Movies \
--projection-expression "title" \
--key-condition-expression "#y = :yyyy" \
--expression-attribute-names '{"#y":"year"}' \
--expression-attribute-values '{":yyyy":{"N":"1993"}}' \
--page-size 5 \
--debug
```
Ordinarily, the AWS CLI handles pagination automatically. However, in this example, the AWS CLI `--page-size` parameter limits the number of items per page. The `--debug` parameter prints low-level information about requests and responses.
If you run the example, the first response from DynamoDB looks similar to the following.
```
2017-07-07 11:13:15,603 - MainThread - botocore.parsers - DEBUG - Response body:
b'{"Count":5,"Items":[{"title":{"S":"A Bronx Tale"}},
{"title":{"S":"A Perfect World"}},{"title":{"S":"Addams Family Values"}},
{"title":{"S":"Alive"}},{"title":{"S":"Benny & Joon"}}],
"LastEvaluatedKey":{"year":{"N":"1993"},"title":{"S":"Benny & Joon"}},
"ScannedCount":5}'
```
The `LastEvaluatedKey` in the response indicates that not all of the items have been retrieved. The AWS CLI then issues another `Query` request to DynamoDB. This request and response pattern continues, until the final response.
```
2017-07-07 11:13:16,291 - MainThread - botocore.parsers - DEBUG - Response body:
b'{"Count":1,"Items":[{"title":{"S":"What\'s Eating Gilbert Grape"}}],"ScannedCount":1}'
```
The absence of `LastEvaluatedKey` indicates that there are no more items to retrieve.
**Note**
The AWS SDKs handle the low-level DynamoDB responses (including the presence or absence of `LastEvaluatedKey`) and provide various abstractions for paginating `Query` results. For example, the SDK for Java document interface provides `java.util.Iterator` support so that you can walk through the results one at a time.
For code examples in various programming languages, see the [Amazon DynamoDB Getting Started Guide](https://docs.aws.amazon.com/amazondynamodb/latest/gettingstartedguide/) and the AWS SDK documentation for your language.
You can also reduce page size by limiting the number of items in the result set, with the `Limit` parameter of the `Query` operation.
For more information about querying with DynamoDB, see [Querying tables in DynamoDB](Query.md).
# Other aspects of working with the Query operation in DynamoDB
This section covers additional aspects of the DynamoDB Query operation, including limiting result size, counting scanned vs. returned items, monitoring read capacity consumption, and controlling read consistency.
## Limiting the number of items in the result set
With the `Query` operation, you can limit the number of items that it reads. To do this, set the `Limit` parameter to the maximum number of items that you want.
For example, suppose that you `Query` a table, with a `Limit` value of `6`, and without a filter expression. The `Query` result contains the first six items from the table that match the key condition expression from the request.
Now suppose that you add a filter expression to the `Query`. In this case, DynamoDB reads up to six items, and then returns only those that match the filter expression. The final `Query` result contains six items or fewer, even if more items would have matched the filter expression if DynamoDB had kept reading more items.
## Counting the items in the results
In addition to the items that match your criteria, the `Query` response contains the following elements:
+ `ScannedCount` — The number of items that matched the key condition expression *before* a filter expression (if present) was applied.
+ `Count` — The number of items that remain *after* a filter expression (if present) was applied.
**Note**
If you don't use a filter expression, `ScannedCount` and `Count` have the same value.
If the size of the `Query` result set is larger than 1 MB, `ScannedCount` and `Count` represent only a partial count of the total items. You need to perform multiple `Query` operations to retrieve all the results (see [Paginating table query results in DynamoDB](Query.Pagination.md)).
Each `Query` response contains the `ScannedCount` and `Count` for the items that were processed by that particular `Query` request. To obtain grand totals for all of the `Query` requests, you could keep a running tally of both `ScannedCount` and `Count`.
## Capacity units consumed by query
You can `Query` any table or secondary index, as long as you provide the name of the partition key attribute and a single value for that attribute. `Query` returns all items with that partition key value. Optionally, you can provide a sort key attribute and use a comparison operator to refine the search results. `Query` API operations consume read capacity units, as follows.
****
| If you `Query` a... | DynamoDB consumes read capacity units from... |
| --- | --- |
| Table | The table's provisioned read capacity. |
| Global secondary index | The index's provisioned read capacity. |
| Local secondary index | The base table's provisioned read capacity. |
By default, a `Query` operation does not return any data on how much read capacity it consumes. However, you can specify the `ReturnConsumedCapacity` parameter in a `Query` request to obtain this information. The following are the valid settings for `ReturnConsumedCapacity`:
+ `NONE` — No consumed capacity data is returned. (This is the default.)
+ `TOTAL` — The response includes the aggregate number of read capacity units consumed.
+ `INDEXES` — The response shows the aggregate number of read capacity units consumed, together with the consumed capacity for each table and index that was accessed.
DynamoDB calculates the number of read capacity units consumed based on the number of items and the size of those items, not on the amount of data that is returned to an application. For this reason, the number of capacity units consumed is the same whether you request all of the attributes (the default behavior) or just some of them (using a projection expression). The number is also the same whether or not you use a filter expression. `Query` consumes a minimum read capacity unit to perform one strongly consistent read per second, or two eventually consistent reads per second for an item up to 4 KB. If you need to read an item that is larger than 4 KB, DynamoDB needs additional read request units. Empty tables and very large tables which have a sparse amount of partition keys might see some additional RCUs charged beyond the amount of data queried. This covers the cost of serving the `Query` request, even if no data exists.
## Read consistency for query
A `Query` operation performs eventually consistent reads, by default. This means that the `Query` results might not reflect changes due to recently completed `PutItem` or `UpdateItem` operations. For more information, see [DynamoDB read consistency](HowItWorks.ReadConsistency.md).
If you require strongly consistent reads, set the `ConsistentRead` parameter to `true` in the `Query` request.
# Scanning tables in DynamoDB
A `Scan` operation in Amazon DynamoDB reads every item in a table or a secondary index. By default, a `Scan` operation returns all of the data attributes for every item in the table or index. You can use the `ProjectionExpression` parameter so that `Scan` only returns some of the attributes, rather than all of them.
`Scan` always returns a result set. If no matching items are found, the result set is empty.
A single `Scan` request can retrieve a maximum of 1 MB of data. Optionally, DynamoDB can apply a filter expression to this data, narrowing the results before they are returned to the user.
**Topics**
+ [
## Filter expressions for scan
](#Scan.FilterExpression)
+ [
## Limiting the number of items in the result set
](#Scan.Limit)
+ [
## Paginating the results
](#Scan.Pagination)
+ [
## Counting the items in the results
](#Scan.Count)
+ [
## Capacity units consumed by scan
](#Scan.CapacityUnits)
+ [
## Read consistency for scan
](#Scan.ReadConsistency)
+ [
## Parallel scan
](#Scan.ParallelScan)
## Filter expressions for scan
If you need to further refine the `Scan` results, you can optionally provide a filter expression. A *filter expression* determines which items within the `Scan` results should be returned to you. All of the other results are discarded.
A filter expression is applied after a `Scan` finishes but before the results are returned. Therefore, a `Scan` consumes the same amount of read capacity, regardless of whether a filter expression is present.
A `Scan` operation can retrieve a maximum of 1 MB of data. This limit applies before the filter expression is evaluated.
With `Scan`, you can specify any attributes in a filter expression—including partition key and sort key attributes.
The syntax for a filter expression is identical to that of a condition expression. Filter expressions can use the same comparators, functions, and logical operators as a condition expression. See [Condition and filter expressions, operators, and functions in DynamoDB](Expressions.OperatorsAndFunctions.md) for more information about logical operators.
**Example**
The following AWS Command Line Interface (AWS CLI) example scans the `Thread` table and returns only the items that were last posted to by a particular user.
```
aws dynamodb scan \
--table-name Thread \
--filter-expression "LastPostedBy = :name" \
--expression-attribute-values '{":name":{"S":"User A"}}'
```
## Limiting the number of items in the result set
The `Scan` operation enables you to limit the number of items that it returns in the result. To do this, set the `Limit` parameter to the maximum number of items that you want the `Scan` operation to return, prior to filter expression evaluation.
For example, suppose that you `Scan` a table with a `Limit` value of `6` and without a filter expression. The `Scan` result contains the first six items from the table.
Now suppose that you add a filter expression to the `Scan`. In this case, DynamoDB applies the filter expression to the six items that were returned, discarding those that do not match. The final `Scan` result contains six items or fewer, depending on the number of items that were filtered.
## Paginating the results
DynamoDB *paginates* the results from `Scan` operations. With pagination, the `Scan` results are divided into "pages" of data that are 1 MB in size (or less). An application can process the first page of results, then the second page, and so on.
A single `Scan` only returns a result set that fits within the 1 MB size limit.
To determine whether there are more results and to retrieve them one page at a time, applications should do the following:
1. Examine the low-level `Scan` result:
+ If the result contains a `LastEvaluatedKey` element, proceed to step 2.
+ If there is *not* a `LastEvaluatedKey` in the result, then there are no more items to be retrieved.
1. Construct a new `Scan` request, with the same parameters as the previous one. However, this time, take the `LastEvaluatedKey` value from step 1 and use it as the `ExclusiveStartKey` parameter in the new `Scan` request.
1. Run the new `Scan` request.
1. Go to step 1.
In other words, the `LastEvaluatedKey` from a `Scan` response should be used as the `ExclusiveStartKey` for the next `Scan` request. If there is not a `LastEvaluatedKey` element in a `Scan` response, you have retrieved the final page of results. (The absence of `LastEvaluatedKey` is the only way to know that you have reached the end of the result set.)
You can use the AWS CLI to view this behavior. The AWS CLI sends low-level `Scan` requests to DynamoDB, repeatedly, until `LastEvaluatedKey` is no longer present in the results. Consider the following AWS CLI example that scans the entire `Movies` table but returns only the movies from a particular genre.
```
aws dynamodb scan \
--table-name Movies \
--projection-expression "title" \
--filter-expression 'contains(info.genres,:gen)' \
--expression-attribute-values '{":gen":{"S":"Sci-Fi"}}' \
--page-size 100 \
--debug
```
Ordinarily, the AWS CLI handles pagination automatically. However, in this example, the AWS CLI `--page-size` parameter limits the number of items per page. The `--debug` parameter prints low-level information about requests and responses.
**Note**
Your pagination results will also differ based on the input parameters you pass.
Using `aws dynamodb scan --table-name Prices --max-items 1` returns a `NextToken`
Using `aws dynamodb scan --table-name Prices --limit 1` returns a `LastEvaluatedKey`.
Also be aware that using `--starting-token` in particular requires the `NextToken` value.
If you run the example, the first response from DynamoDB looks similar to the following.
```
2017-07-07 12:19:14,389 - MainThread - botocore.parsers - DEBUG - Response body:
b'{"Count":7,"Items":[{"title":{"S":"Monster on the Campus"}},{"title":{"S":"+1"}},
{"title":{"S":"100 Degrees Below Zero"}},{"title":{"S":"About Time"}},{"title":{"S":"After Earth"}},
{"title":{"S":"Age of Dinosaurs"}},{"title":{"S":"Cloudy with a Chance of Meatballs 2"}}],
"LastEvaluatedKey":{"year":{"N":"2013"},"title":{"S":"Curse of Chucky"}},"ScannedCount":100}'
```
The `LastEvaluatedKey` in the response indicates that not all of the items have been retrieved. The AWS CLI then issues another `Scan` request to DynamoDB. This request and response pattern continues, until the final response.
```
2017-07-07 12:19:17,830 - MainThread - botocore.parsers - DEBUG - Response body:
b'{"Count":1,"Items":[{"title":{"S":"WarGames"}}],"ScannedCount":6}'
```
The absence of `LastEvaluatedKey` indicates that there are no more items to retrieve.
**Note**
The AWS SDKs handle the low-level DynamoDB responses (including the presence or absence of `LastEvaluatedKey`) and provide various abstractions for paginating `Scan` results. For example, the SDK for Java document interface provides `java.util.Iterator` support so that you can walk through the results one at a time.
For code examples in various programming languages, see the [Amazon DynamoDB Getting Started Guide](https://docs.aws.amazon.com/amazondynamodb/latest/gettingstartedguide/) and the AWS SDK documentation for your language.
## Counting the items in the results
In addition to the items that match your criteria, the `Scan` response contains the following elements:
+ `ScannedCount` — The number of items evaluated, before any `ScanFilter` is applied. A high `ScannedCount` value with few, or no, `Count` results indicates an inefficient `Scan` operation. If you did not use a filter in the request, `ScannedCount` is the same as `Count`.
+ `Count` — The number of items that remain, *after* a filter expression (if present) was applied.
**Note**
If you do not use a filter expression, `ScannedCount` and `Count` have the same value.
If the size of the `Scan` result set is larger than 1 MB, `ScannedCount` and `Count` represent only a partial count of the total items. You need to perform multiple `Scan` operations to retrieve all the results (see [Paginating the results](#Scan.Pagination)).
Each `Scan` response contains the `ScannedCount` and `Count` for the items that were processed by that particular `Scan` request. To get grand totals for all of the `Scan` requests, you could keep a running tally of both `ScannedCount` and `Count`.
## Capacity units consumed by scan
You can `Scan` any table or secondary index. `Scan` operations consume read capacity units, as follows.
****
| If you `Scan` a... | DynamoDB consumes read capacity units from... |
| --- | --- |
| Table | The table's provisioned read capacity. |
| Global secondary index | The index's provisioned read capacity. |
| Local secondary index | The base table's provisioned read capacity. |
**Note**
Cross-account access for secondary index scan operations is currently not supported with [resource-based policies](access-control-resource-based.md).
By default, a `Scan` operation does not return any data on how much read capacity it consumes. However, you can specify the `ReturnConsumedCapacity` parameter in a `Scan` request to obtain this information. The following are the valid settings for `ReturnConsumedCapacity`:
+ `NONE` — No consumed capacity data is returned. (This is the default.)
+ `TOTAL` — The response includes the aggregate number of read capacity units consumed.
+ `INDEXES` — The response shows the aggregate number of read capacity units consumed, together with the consumed capacity for each table and index that was accessed.
DynamoDB calculates the number of read capacity units consumed based on the number of items and the size of those items, not on the amount of data that is returned to an application. For this reason, the number of capacity units consumed is the same whether you request all of the attributes (the default behavior) or just some of them (using a projection expression). The number is also the same whether or not you use a filter expression. `Scan` consumes a minimum read capacity unit to perform one strongly consistent read per second, or two eventually consistent reads per second for an item up to 4 KB. If you need to read an item that is larger than 4 KB, DynamoDB needs additional read request units. Empty tables and very large tables which have a sparse amount of partition keys might see some additional RCUs charged beyond the amount of data scanned. This covers the cost of serving the `Scan` request, even if no data exists.
## Read consistency for scan
A `Scan` operation performs eventually consistent reads, by default. This means that the `Scan` results might not reflect changes due to recently completed `PutItem` or `UpdateItem` operations. For more information, see [DynamoDB read consistency](HowItWorks.ReadConsistency.md).
If you require strongly consistent reads, as of the time that the `Scan` begins, set the `ConsistentRead` parameter to `true` in the `Scan` request. This ensures that all of the write operations that completed before the `Scan` began are included in the `Scan` response.
Setting `ConsistentRead` to `true` can be useful in table backup or replication scenarios, in conjunction with [DynamoDB Streams](./Streams.html). You first use `Scan` with `ConsistentRead` set to true to obtain a consistent copy of the data in the table. During the `Scan`, DynamoDB Streams records any additional write activity that occurs on the table. After the `Scan` is complete, you can apply the write activity from the stream to the table.
**Note**
A `Scan` operation with `ConsistentRead` set to `true` consumes twice as many read capacity units as compared to leaving `ConsistentRead` at its default value (`false`).
## Parallel scan
By default, the `Scan` operation processes data sequentially. Amazon DynamoDB returns data to the application in 1 MB increments, and an application performs additional `Scan` operations to retrieve the next 1 MB of data.
The larger the table or index being scanned, the more time the `Scan` takes to complete. In addition, a sequential `Scan` might not always be able to fully use the provisioned read throughput capacity: Even though DynamoDB distributes a large table's data across multiple physical partitions, a `Scan` operation can only read one partition at a time. For this reason, the throughput of a `Scan` is constrained by the maximum throughput of a single partition.
To address these issues, the `Scan` operation can logically divide a table or secondary index into multiple *segments*, with multiple application workers scanning the segments in parallel. Each worker can be a thread (in programming languages that support multithreading) or an operating system process. To perform a parallel scan, each worker issues its own `Scan` request with the following parameters:
+ `Segment` — A segment to be scanned by a particular worker. Each worker should use a different value for `Segment`.
+ `TotalSegments` — The total number of segments for the parallel scan. This value must be the same as the number of workers that your application will use.
The following diagram shows how a multithreaded application performs a parallel `Scan` with three degrees of parallelism.
![\[A multithreaded application that performs a parallel scan by dividing a table into three segments.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/ParallelScan.png)
In this diagram, the application spawns three threads and assigns each thread a number. (Segments are zero-based, so the first number is always 0.) Each thread issues a `Scan` request, setting `Segment` to its designated number and setting `TotalSegments` to 3. Each thread scans its designated segment, retrieving data 1 MB at a time, and returns the data to the application's main thread.
DynamoDB assigns items to *segments* by applying a hash function to each item's partition key. For a given `TotalSegments` value, all items with the same partition key are always assigned to the same `Segment`. This means that in a table where *Item 1*, *Item 2*, and *Item 3* all share `pk="account#123"` (but have different sort keys), these items will be processed by the same worker, regardless of the sort key values or the size of the *item collection*.
Because *segment* assignment is based solely on the partition key hash, segments can be unevenly distributed. Some segments might contain no items, while others might contain many partition keys with large item collections. As a result, increasing the total number of segments does not guarantee faster scan performance, particularly when partition keys are not uniformly distributed across the keyspace.
The values for `Segment` and `TotalSegments` apply to individual `Scan` requests, and you can use different values at any time. You might need to experiment with these values, and the number of workers you use, until your application achieves its best performance.
**Note**
A parallel scan with a large number of workers can easily consume all of the provisioned throughput for the table or index being scanned. It is best to avoid such scans if the table or index is also incurring heavy read or write activity from other applications.
To control the amount of data returned per request, use the `Limit` parameter. This can help prevent situations where one worker consumes all of the provisioned throughput, at the expense of all other workers.
# PartiQL - a SQL-compatible query language for Amazon DynamoDB
Amazon DynamoDB supports [PartiQL](https://partiql.org/), a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB. Using PartiQL, you can easily interact with DynamoDB tables and run ad hoc queries using the AWS Management Console, NoSQL Workbench, AWS Command Line Interface, and DynamoDB APIs for PartiQL.
PartiQL operations provide the same availability, latency, and performance as the other DynamoDB data plane operations.
The following sections describe the DynamoDB implementation of PartiQL.
**Topics**
+ [
## What is PartiQL?
](#ql-reference.what-is)
+ [
## PartiQL in Amazon DynamoDB
](#ql-reference.what-is)
+ [Getting started](ql-gettingstarted.md)
+ [Data types](ql-reference.data-types.md)
+ [Statements](ql-reference.statements.md)
+ [Functions](ql-functions.md)
+ [Operators](ql-operators.md)
+ [Transactions](ql-reference.multiplestatements.transactions.md)
+ [Batch operations](ql-reference.multiplestatements.batching.md)
+ [IAM policies](ql-iam.md)
## What is PartiQL?
*PartiQL* provides SQL-compatible query access across multiple data stores containing structured data, semistructured data, and nested data. It is widely used within Amazon and is now available as part of many AWS services, including DynamoDB.
For the PartiQL specification and a tutorial on the core query language, see the [PartiQL documentation](https://partiql.org/docs.html).
**Note**
Amazon DynamoDB supports a *subset* of the [PartiQL](https://partiql.org/) query language.
Amazon DynamoDB does not support the [Amazon ion](http://amzn.github.io/ion-docs/) data format or Amazon Ion literals.
## PartiQL in Amazon DynamoDB
To run PartiQL queries in DynamoDB, you can use:
+ The DynamoDB console
+ The NoSQL Workbench
+ The AWS Command Line Interface (AWS CLI)
+ The DynamoDB APIs
For information about using these methods to access DynamoDB, see [Accessing DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AccessingDynamoDB.html).
# Getting started with PartiQL for DynamoDB
This section describes how to use PartiQL for DynamoDB from the Amazon DynamoDB console, the AWS Command Line Interface (AWS CLI), and DynamoDB APIs.
In the following examples, the DynamoDB table that is defined in the [Getting started with DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStartedDynamoDB.html) tutorial is a pre-requisite.
For information about using the DynamoDB console, AWS Command Line Interface, or DynamoDB APIs to access DynamoDB, see [Accessing DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AccessingDynamoDB.html).
To [download](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.settingup.html) and use the [NoSQL workbench](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.html) to build [PartiQL for DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) statements choose **PartiQL operations** at the top right corner of the NoSQL Workbench for DynamoDB [Operation builder](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.querybuilder.operationbuilder.html).
------
#### [ Console ]
![\[PartiQL editor interface that shows the result of running the Query operation on the Music table.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/partiqlgettingstarted.png)
1. Sign in to the AWS Management Console and open the DynamoDB console at [https://console.aws.amazon.com/dynamodb/](https://console.aws.amazon.com/dynamodb/).
1. In the navigation pane on the left side of the console, choose **PartiQL editor**.
1. Choose the **Music** table.
1. Choose **Query table**. This action generates a query that will not result in a full table scan.
1. Replace `partitionKeyValue` with the string value `Acme Band`. Replace `sortKeyValue` with the string value `Happy Day`.
1. Choose the **Run** button.
1. You can view the results of the query by choosing the **Table view** or the **JSON view** buttons.
------
#### [ NoSQL workbench ]
![\[NoSQL workbench interface. It shows a PartiQL SELECT statement that you can run on the Music table.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/workbench/partiql.single.png)
1. Choose **PartiQL statement**.
1. Enter the following PartiQL [SELECT statement](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html)
```
SELECT *
FROM Music
WHERE Artist=? and SongTitle=?
```
1. To specify a value for the `Artist` and `SongTitle` parameters:
1. Choose **Optional request parameters**.
1. Choose **Add new parameters**.
1. Choose the attribute type **string** and value `Acme Band`.
1. Repeat steps b and c, and choose type **string** and value `PartiQL Rocks`.
1. If you want to generate code, choose **Generate code**.
Select your desired language from the displayed tabs. You can now copy this code and use it in your application.
1. If you want the operation to be run immediately, choose **Run**.
------
#### [ AWS CLI ]
1. Create an item in the `Music` table using the INSERT PartiQL statement.
```
aws dynamodb execute-statement --statement "INSERT INTO Music \
VALUE \
{'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}"
```
1. Retrieve an item from the Music table using the SELECT PartiQL statement.
```
aws dynamodb execute-statement --statement "SELECT * FROM Music \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
1. Update an item in the `Music` table using the UPDATE PartiQL statement.
```
aws dynamodb execute-statement --statement "UPDATE Music \
SET AwardsWon=1 \
SET AwardDetail={'Grammys':[2020, 2018]} \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
Add a list value for an item in the `Music` table.
```
aws dynamodb execute-statement --statement "UPDATE Music \
SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
Remove a list value for an item in the `Music` table.
```
aws dynamodb execute-statement --statement "UPDATE Music \
REMOVE AwardDetail.Grammys[2] \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
Add a new map member for an item in the `Music` table.
```
aws dynamodb execute-statement --statement "UPDATE Music \
SET AwardDetail.BillBoard=[2020] \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
Add a new string set attribute for an item in the `Music` table.
```
aws dynamodb execute-statement --statement "UPDATE Music \
SET BandMembers =<<'member1', 'member2'>> \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
Update a string set attribute for an item in the `Music` table.
```
aws dynamodb execute-statement --statement "UPDATE Music \
SET BandMembers =set_add(BandMembers, <<'newmember'>>) \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
1. Delete an item from the `Music` table using the DELETE PartiQL statement.
```
aws dynamodb execute-statement --statement "DELETE FROM Music \
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
```
------
#### [ Java ]
```
import java.util.ArrayList;
import java.util.List;
import com.amazonaws.AmazonClientException;
import com.amazonaws.AmazonServiceException;
import software.amazon.dynamodb.AmazonDynamoDB;
import software.amazon.dynamodb.AmazonDynamoDBClientBuilder;
import software.amazon.dynamodb.model.AttributeValue;
import software.amazon.dynamodb.model.ConditionalCheckFailedException;
import software.amazon.dynamodb.model.ExecuteStatementRequest;
import software.amazon.dynamodb.model.ExecuteStatementResult;
import software.amazon.dynamodb.model.InternalServerErrorException;
import software.amazon.dynamodb.model.ItemCollectionSizeLimitExceededException;
import software.amazon.dynamodb.model.ProvisionedThroughputExceededException;
import software.amazon.dynamodb.model.RequestLimitExceededException;
import software.amazon.dynamodb.model.ResourceNotFoundException;
import software.amazon.dynamodb.model.TransactionConflictException;
public class DynamoDBPartiQGettingStarted {
public static void main(String[] args) {
// Create the DynamoDB Client with the region you want
AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-1");
try {
// Create ExecuteStatementRequest
ExecuteStatementRequest executeStatementRequest = new ExecuteStatementRequest();
List parameters= getPartiQLParameters();
//Create an item in the Music table using the INSERT PartiQL statement
processResults(executeStatementRequest(dynamoDB, "INSERT INTO Music value {'Artist':?,'SongTitle':?}", parameters));
//Retrieve an item from the Music table using the SELECT PartiQL statement.
processResults(executeStatementRequest(dynamoDB, "SELECT * FROM Music where Artist=? and SongTitle=?", parameters));
//Update an item in the Music table using the UPDATE PartiQL statement.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist=? and SongTitle=?", parameters));
//Add a list value for an item in the Music table.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) where Artist=? and SongTitle=?", parameters));
//Remove a list value for an item in the Music table.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music REMOVE AwardDetail.Grammys[2] where Artist=? and SongTitle=?", parameters));
//Add a new map member for an item in the Music table.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music set AwardDetail.BillBoard=[2020] where Artist=? and SongTitle=?", parameters));
//Add a new string set attribute for an item in the Music table.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET BandMembers =<<'member1', 'member2'>> where Artist=? and SongTitle=?", parameters));
//update a string set attribute for an item in the Music table.
processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET BandMembers =set_add(BandMembers, <<'newmember'>>) where Artist=? and SongTitle=?", parameters));
//Retrieve an item from the Music table using the SELECT PartiQL statement.
processResults(executeStatementRequest(dynamoDB, "SELECT * FROM Music where Artist=? and SongTitle=?", parameters));
//delete an item from the Music Table
processResults(executeStatementRequest(dynamoDB, "DELETE FROM Music where Artist=? and SongTitle=?", parameters));
} catch (Exception e) {
handleExecuteStatementErrors(e);
}
}
private static AmazonDynamoDB createDynamoDbClient(String region) {
return AmazonDynamoDBClientBuilder.standard().withRegion(region).build();
}
private static List getPartiQLParameters() {
List parameters = new ArrayList();
parameters.add(new AttributeValue("Acme Band"));
parameters.add(new AttributeValue("PartiQL Rocks"));
return parameters;
}
private static ExecuteStatementResult executeStatementRequest(AmazonDynamoDB client, String statement, List parameters ) {
ExecuteStatementRequest request = new ExecuteStatementRequest();
request.setStatement(statement);
request.setParameters(parameters);
return client.executeStatement(request);
}
private static void processResults(ExecuteStatementResult executeStatementResult) {
System.out.println("ExecuteStatement successful: "+ executeStatementResult.toString());
}
// Handles errors during ExecuteStatement execution. Use recommendations in error messages below to add error handling specific to
// your application use-case.
private static void handleExecuteStatementErrors(Exception exception) {
try {
throw exception;
} catch (ConditionalCheckFailedException ccfe) {
System.out.println("Condition check specified in the operation failed, review and update the condition " +
"check before retrying. Error: " + ccfe.getErrorMessage());
} catch (TransactionConflictException tce) {
System.out.println("Operation was rejected because there is an ongoing transaction for the item, generally " +
"safe to retry with exponential back-off. Error: " + tce.getErrorMessage());
} catch (ItemCollectionSizeLimitExceededException icslee) {
System.out.println("An item collection is too large, you\'re using Local Secondary Index and exceeded " +
"size limit of items per partition key. Consider using Global Secondary Index instead. Error: " + icslee.getErrorMessage());
} catch (Exception e) {
handleCommonErrors(e);
}
}
private static void handleCommonErrors(Exception exception) {
try {
throw exception;
} catch (InternalServerErrorException isee) {
System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage());
} catch (RequestLimitExceededException rlee) {
System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " +
"retrying. Error: " + rlee.getErrorMessage());
} catch (ProvisionedThroughputExceededException ptee) {
System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " +
"Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " +
ptee.getErrorMessage());
} catch (ResourceNotFoundException rnfe) {
System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage());
} catch (AmazonServiceException ase) {
System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " +
"service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " +
"configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage());
} catch (AmazonClientException ace) {
System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " +
"service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+
"Error: " + ace.getMessage());
} catch (Exception e) {
System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage());
}
}
}
```
------
## Using parameterized statements
Instead of embedding values directly in a PartiQL statement string, you can use question mark (`?`) placeholders and supply the values separately in the `Parameters` field. Each `?` is replaced by the corresponding parameter value, in the order they are provided.
Using parameterized statements is a best practice because it separates the statement structure from the data values, making statements easier to read and reuse. It also avoids the need to manually format and escape attribute values in the statement string.
Parameterized statements are supported in `ExecuteStatement`, `BatchExecuteStatement`, and `ExecuteTransaction` operations.
The following examples retrieve an item from the `Music` table using parameterized values for the partition key and sort key.
------
#### [ AWS CLI parameterized ]
```
aws dynamodb execute-statement \
--statement "SELECT * FROM \"Music\" WHERE Artist=? AND SongTitle=?" \
--parameters '[{"S": "Acme Band"}, {"S": "PartiQL Rocks"}]'
```
------
#### [ Java parameterized ]
```
List parameters = new ArrayList<>();
parameters.add(new AttributeValue("Acme Band"));
parameters.add(new AttributeValue("PartiQL Rocks"));
ExecuteStatementRequest request = new ExecuteStatementRequest()
.withStatement("SELECT * FROM Music WHERE Artist=? AND SongTitle=?")
.withParameters(parameters);
ExecuteStatementResult result = dynamoDB.executeStatement(request);
```
------
#### [ Python parameterized ]
```
response = dynamodb_client.execute_statement(
Statement="SELECT * FROM Music WHERE Artist=? AND SongTitle=?",
Parameters=[
{'S': 'Acme Band'},
{'S': 'PartiQL Rocks'}
]
)
```
------
**Note**
The Java example in the preceding getting started section uses parameterized statements throughout. The `getPartiQLParameters()` method builds the parameter list, and each statement uses `?` placeholders instead of inline values.
# PartiQL data types for DynamoDB
The following table lists the data types you can use with PartiQL for DynamoDB.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.data-types.html)
## Examples
The following statement demonstrates how to insert the following data types: `String`, `Number`, `Map`, `List`, `Number Set` and `String Set`.
```
INSERT INTO TypesTable value {'primarykey':'1',
'NumberType':1,
'MapType' : {'entryname1': 'value', 'entryname2': 4},
'ListType': [1,'stringval'],
'NumberSetType':<<1,34,32,4.5>>,
'StringSetType':<<'stringval','stringval2'>>
}
```
The following statement demonstrates how to insert new elements into the `Map`, `List`, `Number Set` and `String Set` types and change the value of a `Number` type.
```
UPDATE TypesTable
SET NumberType=NumberType + 100
SET MapType.NewMapEntry=[2020, 'stringvalue', 2.4]
SET ListType = LIST_APPEND(ListType, [4, <<'string1', 'string2'>>])
SET NumberSetType= SET_ADD(NumberSetType, <<345, 48.4>>)
SET StringSetType = SET_ADD(StringSetType, <<'stringsetvalue1', 'stringsetvalue2'>>)
WHERE primarykey='1'
```
The following statement demonstrates how to remove elements from the `Map`, `List`, `Number Set` and `String Set` types and change the value of a `Number` type.
```
UPDATE TypesTable
SET NumberType=NumberType - 1
REMOVE ListType[1]
REMOVE MapType.NewMapEntry
SET NumberSetType = SET_DELETE( NumberSetType, <<345>>)
SET StringSetType = SET_DELETE( StringSetType, <<'stringsetvalue1'>>)
WHERE primarykey='1'
```
For more information, see [DynamoDB data types](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.NamingRulesDataTypes.html#HowItWorks.DataTypes).
# PartiQL statements for DynamoDB
Amazon DynamoDB supports the following PartiQL statements.
**Note**
DynamoDB does not support all PartiQL statements.
This reference provides basic syntax and usage examples of PartiQL statements that you manually run using the AWS CLI or APIs.
*Data manipulation language* (DML) is the set of PartiQL statements that you use to manage data in DynamoDB tables. You use DML statements to add, modify, or delete data in a table.
The following DML and query language statements are supported:
+ [PartiQL select statements for DynamoDB](ql-reference.select.md)
+ [PartiQL update statements for DynamoDB](ql-reference.update.md)
+ [PartiQL insert statements for DynamoDB](ql-reference.insert.md)
+ [PartiQL delete statements for DynamoDB](ql-reference.delete.md)
[Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) and [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md) are also supported by PartiQL for DynamoDB.
# PartiQL select statements for DynamoDB
Use the `SELECT` statement to retrieve data from a table in Amazon DynamoDB.
Using the `SELECT` statement can result in a full table scan if an equality or IN condition with a partition key is not provided in the WHERE clause. A scan operation examines every item for the requested values and can use up the provisioned throughput for a large table or index in a single operation.
If you want to avoid full table scan in PartiQL, you can:
+ Author your `SELECT` statements to not result in full table scans by making sure your [WHERE clause condition](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html#ql-reference.select.parameters) is configured accordingly.
+ Disable full table scans using the IAM policy specified at [Example: Allow select statements and deny full table scan statements in PartiQL for DynamoDB](ql-iam.md#access-policy-ql-iam-example6), in the DynamoDB developer guide.
For more information see [Best practices for querying and scanning data](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-query-scan.html), in the DynamoDB developer guide.
**Topics**
+ [
## Syntax
](#ql-reference.select.syntax)
+ [
## Parameters
](#ql-reference.select.parameters)
+ [
## Examples
](#ql-reference.select.examples)
## Syntax
```
SELECT expression [, ...]
FROM table[.index]
[ WHERE condition ] [ [ORDER BY key [DESC|ASC] , ...]
```
## Parameters
***expression***
(Required) A projection formed from the `*` wildcard or a projection list of one or more attribute names or document paths from the result set. An expression can consist of calls to [Use PartiQL functions with DynamoDB](ql-functions.md) or fields that are modified by [PartiQL arithmetic, comparison, and logical operators for DynamoDB](ql-operators.md).
***table***
(Required) The table name to query.
***index***
(Optional) The name of the index to query.
You must add double quotation marks to the table name and index name when querying an index.
```
SELECT *
FROM "TableName"."IndexName"
```
***condition***
(Optional) The selection criteria for the query.
To ensure that a `SELECT` statement does not result in a full table scan, the `WHERE` clause condition must specify a partition key. Use the equality or IN operator.
For example, if you have an `Orders` table with an `OrderID` partition key and other non-key attributes, including an `Address`, the following statements would not result in a full table scan:
```
SELECT *
FROM "Orders"
WHERE OrderID = 100
SELECT *
FROM "Orders"
WHERE OrderID = 100 and Address='some address'
SELECT *
FROM "Orders"
WHERE OrderID = 100 or OrderID = 200
SELECT *
FROM "Orders"
WHERE OrderID IN [100, 300, 234]
```
The following `SELECT` statements, however, will result in a full table scan:
```
SELECT *
FROM "Orders"
WHERE OrderID > 1
SELECT *
FROM "Orders"
WHERE Address='some address'
SELECT *
FROM "Orders"
WHERE OrderID = 100 OR Address='some address'
```
***key***
(Optional) A hash key or a sort key to use to order returned results. The default order is ascending (`ASC`) specify `DESC` if you want the results retuned in descending order.
**Note**
If you omit the `WHERE` clause, then all of the items in the table are retrieved.
## Examples
The following query returns one item, if one exists, from the `Orders` table by specifying the partition key, `OrderID`, and using the equality operator.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID = 1
```
The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the OR operator.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID = 1 OR OrderID = 2
```
The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the IN operator. The returned results are in descending order, based on the `OrderID` key attribute value.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID IN [1, 2, 3] ORDER BY OrderID DESC
```
The following query shows a full table scan that returns all items from the `Orders` table that have a `Total` greater than 500, where `Total` is a non-key attribute.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total > 500
```
The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the IN operator and a non-key attribute `Total`.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total IN [500, 600]
```
The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the BETWEEN operator and a non-key attribute `Total`.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total BETWEEN 500 AND 600
```
The following query returns the first date a firestick device was used to watch by specifying the partition key `CustomerID` and sort key `MovieID` in the WHERE clause condition and using document paths in the SELECT clause.
```
SELECT Devices.FireStick.DateWatched[0]
FROM WatchList
WHERE CustomerID= 'C1' AND MovieID= 'M1'
```
The following query shows a full table scan that returns the list of items where a firestick device was first used after 12/24/19 using document paths in the WHERE clause condition.
```
SELECT Devices
FROM WatchList
WHERE Devices.FireStick.DateWatched[0] >= '12/24/19'
```
# PartiQL update statements for DynamoDB
Use the `UPDATE` statement to modify the value of one or more attributes within an item in an Amazon DynamoDB table.
**Note**
You can only update one item at a time; you cannot issue a single DynamoDB PartiQL statement that updates multiple items. For information on updating multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [
## Syntax
](#ql-reference.update.syntax)
+ [
## Parameters
](#ql-reference.update.parameters)
+ [
## Return value
](#ql-reference.update.return)
+ [
## Examples
](#ql-reference.update.examples)
## Syntax
```
UPDATE table
[SET | REMOVE] path [= data] […]
WHERE condition [RETURNING returnvalues]
::= [ALL OLD | MODIFIED OLD | ALL NEW | MODIFIED NEW] *
```
## Parameters
***table***
(Required) The table containing the data to be modified.
***path***
(Required) An attribute name or document path to be created or modified.
***data***
(Required) An attribute value or the result of an operation.
The supported operations to use with SET:
+ LIST\$1APPEND: adds a value to a list type.
+ SET\$1ADD: adds a value to a number or string set.
+ SET\$1DELETE: removes a value from a number or string set.
***condition***
(Required) The selection criteria for the item to be modified. This condition must resolve to a single primary key value.
***returnvalues***
(Optional) Use `returnvalues` if you want to get the item attributes as they appear before or after they are updated. The valid values are:
+ `ALL OLD *`- Returns all of the attributes of the item, as they appeared before the update operation.
+ `MODIFIED OLD *`- Returns only the updated attributes, as they appeared before the update operation.
+ `ALL NEW *`- Returns all of the attributes of the item, as they appear after the update operation.
+ `MODIFIED NEW *`- Returns only the updated attributes, as they appear after the `UpdateItem` operation.
## Return value
This statement does not return a value unless `returnvalues` parameter is specified.
**Note**
If the WHERE clause of the UPDATE statement does not evaluate to true for any item in the DynamoDB table, `ConditionalCheckFailedException` is returned.
## Examples
Update an attribute value in an existing item. If the attribute does not exist, it is created.
The following query updates an item in the `"Music"` table by adding an attribute of type number (`AwardsWon`) and an attribute of type map (`AwardDetail`).
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
You can add `RETURNING ALL OLD *` to return the attributes as they appeared before the `Update` operation.
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
RETURNING ALL OLD *
```
This returns the following:
```
{
"Items": [
{
"Artist": {
"S": "Acme Band"
},
"SongTitle": {
"S": "PartiQL Rocks"
}
}
]
}
```
You can add `RETURNING ALL NEW *` to return the attributes as they appeared after the `Update` operation.
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
RETURNING ALL NEW *
```
This returns the following:
```
{
"Items": [
{
"AwardDetail": {
"M": {
"Grammys": {
"L": [
{
"N": "2020"
},
{
"N": "2018"
}
]
}
}
},
"AwardsWon": {
"N": "1"
}
}
]
}
```
The following query updates an item in the `"Music"` table by appending to a list `AwardDetail.Grammys`.
```
UPDATE "Music"
SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016])
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by removing from a list `AwardDetail.Grammys`.
```
UPDATE "Music"
REMOVE AwardDetail.Grammys[2]
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding `BillBoard` to the map `AwardDetail`.
```
UPDATE "Music"
SET AwardDetail.BillBoard=[2020]
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding the string set attribute `BandMembers`.
```
UPDATE "Music"
SET BandMembers =<<'member1', 'member2'>>
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding `newbandmember` to the string set `BandMembers`.
```
UPDATE "Music"
SET BandMembers =set_add(BandMembers, <<'newbandmember'>>)
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
# PartiQL delete statements for DynamoDB
Use the `DELETE` statement to delete an existing item from your Amazon DynamoDB table.
**Note**
You can only delete one item at a time. You cannot issue a single DynamoDB PartiQL statement that deletes multiple items. For information on deleting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [
## Syntax
](#ql-reference.delete.syntax)
+ [
## Parameters
](#ql-reference.delete.parameters)
+ [
## Return value
](#ql-reference.delete.return)
+ [
## Examples
](#ql-reference.delete.examples)
## Syntax
```
DELETE FROM table
WHERE condition [RETURNING returnvalues]
::= ALL OLD *
```
## Parameters
***table***
(Required) The DynamoDB table containing the item to be deleted.
***condition***
(Required) The selection criteria for the item to be deleted; this condition must resolve to a single primary key value.
***returnvalues***
(Optional) Use `returnvalues` if you want to get the item attributes as they appeared before they were deleted. The valid values are:
+ `ALL OLD *`- The content of the old item is returned.
## Return value
This statement does not return a value unless `returnvalues` parameter is specified.
**Note**
If the DynamoDB table does not have any item with the same primary key as that of the item for which the DELETE is issued, SUCCESS is returned with 0 items deleted. If the table has an item with same primary key, but the condition in the WHERE clause of the DELETE statement evaluates to false, `ConditionalCheckFailedException` is returned.
## Examples
The following query deletes an item in the `"Music"` table.
```
DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks'
```
You can add the parameter `RETURNING ALL OLD *` to return the data that was deleted.
```
DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks' RETURNING ALL OLD *
```
The `Delete` statement now returns the following:
```
{
"Items": [
{
"Artist": {
"S": "Acme Band"
},
"SongTitle": {
"S": "PartiQL Rocks"
}
}
]
}
```
# PartiQL insert statements for DynamoDB
Use the `INSERT` statement to add an item to a table in Amazon DynamoDB.
**Note**
You can only insert one item at a time; you cannot issue a single DynamoDB PartiQL statement that inserts multiple items. For information on inserting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [
## Syntax
](#ql-reference.insert.syntax)
+ [
## Parameters
](#ql-reference.insert.parameters)
+ [
## Return value
](#ql-reference.insert.return)
+ [
## Examples
](#ql-reference.insert.examples)
## Syntax
Insert a single item.
```
INSERT INTO table VALUE item
```
## Parameters
***table***
(Required) The table where you want to insert the data. The table must already exist.
***item***
(Required) A valid DynamoDB item represented as a [PartiQL tuple](https://partiql.org/docs.html). You must specify only *one* item and each attribute name in the item is case-sensitive and can be denoted with *single* quotation marks (`'...'`) in PartiQL.
String values are also denoted with *single* quotation marks (`'...'`) in PartiQL.
## Return value
This statement does not return any values.
**Note**
If the DynamoDB table already has an item with the same primary key as the primary key of the item being inserted, `DuplicateItemException` is returned.
## Examples
```
INSERT INTO "Music" value {'Artist' : 'Acme Band','SongTitle' : 'PartiQL Rocks'}
```
# Use PartiQL functions with DynamoDB
PartiQL in Amazon DynamoDB supports the following built-in variants of SQL standard functions.
**Note**
Any SQL functions that are not included in this list are not currently supported in DynamoDB.
## Aggregate functions
+ [Using the SIZE function with PartiQL for amazon DynamoDB](ql-functions.size.md)
## Conditional functions
+ [Using the EXISTS function with PartiQL for DynamoDB](ql-functions.exists.md)
+ [Using the ATTRIBUTE\$1TYPE function with PartiQL for DynamoDB](ql-functions.attribute_type.md)
+ [Using the BEGINS\$1WITH function with PartiQL for DynamoDB](ql-functions.beginswith.md)
+ [Using the CONTAINS function with PartiQL for DynamoDB](ql-functions.contains.md)
+ [Using the MISSING function with PartiQL for DynamoDB](ql-functions.missing.md)
# Using the EXISTS function with PartiQL for DynamoDB
You can use EXISTS to perform the same function as `ConditionCheck` does in the [TransactWriteItems](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transaction-apis.html#transaction-apis-txwriteitems) API. The EXISTS function can only be used in transactions.
Given a value, returns `TRUE` if the value is a non-empty collection. Otherwise, returns `FALSE`.
**Note**
This function can only be used in transactional operations.
## Syntax
```
EXISTS ( statement )
```
## Arguments
*statement*
(Required) The SELECT statement that the function evaluates.
The SELECT statement must specify a full primary key and one other condition.
## Return type
`bool`
## Examples
```
EXISTS(
SELECT * FROM "Music"
WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks')
```
# Using the BEGINS\$1WITH function with PartiQL for DynamoDB
Returns `TRUE` if the attribute specified begins with a particular substring.
## Syntax
```
begins_with(path, value )
```
## Arguments
*path*
(Required) The attribute name or document path to use.
*value*
(Required) The string to search for.
## Return type
`bool`
## Examples
```
SELECT * FROM "Orders" WHERE "OrderID"=1 AND begins_with("Address", '7834 24th')
```
# Using the MISSING function with PartiQL for DynamoDB
Returns `TRUE` if the item does not contain the attribute specified. Only equality and inequality operators can be used with this function.
## Syntax
```
attributename IS | IS NOT MISSING
```
## Arguments
*attributename*
(Required) The attribute name to look for.
## Return type
`bool`
## Examples
```
SELECT * FROM Music WHERE "Awards" is MISSING
```
# Using the ATTRIBUTE\$1TYPE function with PartiQL for DynamoDB
Returns `TRUE` if the attribute at the specified path is of a particular data type.
## Syntax
```
attribute_type( attributename, type )
```
## Arguments
*attributename*
(Required) The attribute name to use.
*type*
(Required) The attribute type to check for. For a list of valid values, see DynamoDB [attribute\$1type](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions).
## Return type
`bool`
## Examples
```
SELECT * FROM "Music" WHERE attribute_type("Artist", 'S')
```
# Using the CONTAINS function with PartiQL for DynamoDB
Returns `TRUE` if the attribute specified by the path is one of the following:
+ A String that contains a particular substring.
+ A Set that contains a particular element within the set.
For more information, see the DynamoDB [contains](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function.
## Syntax
```
contains( path, substring )
```
## Arguments
*path*
(Required) The attribute name or document path to use.
*substring*
(Required) The attribute substring or set member to check for. For more information, see the DynamoDB [contains](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function.
## Return type
`bool`
## Examples
```
SELECT * FROM "Orders" WHERE "OrderID"=1 AND contains("Address", 'Kirkland')
```
# Using the SIZE function with PartiQL for amazon DynamoDB
Returns a number representing an attribute's size in bytes. The following are valid data types for use with size. For more information, see the DynamoDB [size](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function.
## Syntax
```
size( path)
```
## Arguments
*path*
(Required) The attribute name or document path.
For supported types, see DynamoDB [size](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function.
## Return type
`int`
## Examples
```
SELECT * FROM "Orders" WHERE "OrderID"=1 AND size("Image") >300
```
# PartiQL arithmetic, comparison, and logical operators for DynamoDB
PartiQL in Amazon DynamoDB supports the following [SQL standard operators](https://www.w3schools.com/sql/sql_operators.asp).
**Note**
Any SQL operators that are not included in this list are not currently supported in DynamoDB.
## Arithmetic operators
****
| Operator | Description |
| --- | --- |
| \$1 | Add |
| - | Subtract |
## Comparison operators
****
| Operator | Description |
| --- | --- |
| = | Equal to |
| <> | Not Equal to |
| \$1= | Not Equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
## Logical operators
****
| Operator | Description |
| --- | --- |
| AND | TRUE if all the conditions separated by AND are TRUE |
| BETWEEN | `TRUE` if the operand is within the range of comparisons. This operator is inclusive of the lower and upper bound of the operands on which you apply it. |
| IN | `TRUE` if the operand is equal to one of a list of expressions (at max 50 hash attribute values or at max 100 non-key attribute values). Results are returned in pages of up to 10 items. If the `IN` list contains more values, you must use the `NextToken` returned in the response to retrieve subsequent pages. |
| IS | TRUE if the operand is a given, PartiQL data type, including NULL or MISSING |
| NOT | Reverses the value of a given Boolean expression |
| OR | TRUE if any of the conditions separated by OR are TRUE |
For more information about using logical operators, see [Making comparisons](Expressions.OperatorsAndFunctions.md#Expressions.OperatorsAndFunctions.Comparators) and [Logical evaluations](Expressions.OperatorsAndFunctions.md#Expressions.OperatorsAndFunctions.LogicalEvaluations).
# Performing transactions with PartiQL for DynamoDB
This section describes how to use transactions with PartiQL for DynamoDB. PartiQL transactions are limited to 100 total statements (actions).
For more information on DynamoDB transactions, see [Managing complex workflows with DynamoDB transactions](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transactions.html).
**Note**
The entire transaction must consist of either read statements or write statements. You can't mix both in one transaction. The EXISTS function is an exception. You can use it to check the condition of specific attributes of the item in a similar manner to `ConditionCheck` in the [TransactWriteItems](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transaction-apis.html#transaction-apis-txwriteitems) API operation.
**Topics**
+ [
## Syntax
](#ql-reference.multiplestatements.transactions.syntax)
+ [
## Parameters
](#ql-reference.multiplestatements.transactions.parameters)
+ [
## Return values
](#ql-reference.multiplestatements.transactions.return)
+ [
## Examples
](#ql-reference.multiplestatements.transactions.examples)
## Syntax
```
[
{
"Statement":" statement ",
"Parameters":[
{
" parametertype " : " parametervalue "
}, ...]
} , ...
]
```
## Parameters
***statement***
(Required) A PartiQL for DynamoDB supported statement.
The entire transaction must consist of either read statements or write statements. You can't mix both in one transaction.
***parametertype***
(Optional) A DynamoDB type, if parameters were used when specifying the PartiQL statement.
***parametervalue***
(Optional) A parameter value if parameters were used when specifying the PartiQL statement.
## Return values
This statement doesn't return any values for Write operations (INSERT, UPDATE, or DELETE). However, it returns different values for Read operations (SELECT) based on the conditions specified in the WHERE clause.
**Note**
If any of the singleton INSERT, UPDATE, or DELETE operations return an error, the transactions are canceled with the `TransactionCanceledException` exception, and the cancellation reason code includes the errors from the individual singleton operations.
## Examples
The following example runs multiple statements as a transaction.
------
#### [ AWS CLI ]
1. Save the following JSON code to a file called partiql.json.
```
[
{
"Statement": "EXISTS(SELECT * FROM \"Music\" where Artist='No One You Know' and SongTitle='Call Me Today' and Awards is MISSING)"
},
{
"Statement": "INSERT INTO Music value {'Artist':?,'SongTitle':'?'}",
"Parameters": [{\"S\": \"Acme Band\"}, {\"S\": \"Best Song\"}]
},
{
"Statement": "UPDATE \"Music\" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist='Acme Band' and SongTitle='PartiQL Rocks'"
}
]
```
1. Run the following command in a command prompt.
```
aws dynamodb execute-transaction --transact-statements file://partiql.json
```
------
#### [ Java ]
```
public class DynamoDBPartiqlTransaction {
public static void main(String[] args) {
// Create the DynamoDB Client with the region you want
AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-2");
try {
// Create ExecuteTransactionRequest
ExecuteTransactionRequest executeTransactionRequest = createExecuteTransactionRequest();
ExecuteTransactionResult executeTransactionResult = dynamoDB.executeTransaction(executeTransactionRequest);
System.out.println("ExecuteTransaction successful.");
// Handle executeTransactionResult
} catch (Exception e) {
handleExecuteTransactionErrors(e);
}
}
private static AmazonDynamoDB createDynamoDbClient(String region) {
return AmazonDynamoDBClientBuilder.standard().withRegion(region).build();
}
private static ExecuteTransactionRequest createExecuteTransactionRequest() {
ExecuteTransactionRequest request = new ExecuteTransactionRequest();
// Create statements
List statements = getPartiQLTransactionStatements();
request.setTransactStatements(statements);
return request;
}
private static List getPartiQLTransactionStatements() {
List statements = new ArrayList();
statements.add(new ParameterizedStatement()
.withStatement("EXISTS(SELECT * FROM "Music" where Artist='No One You Know' and SongTitle='Call Me Today' and Awards is MISSING)"));
statements.add(new ParameterizedStatement()
.withStatement("INSERT INTO "Music" value {'Artist':'?','SongTitle':'?'}")
.withParameters(new AttributeValue("Acme Band"),new AttributeValue("Best Song")));
statements.add(new ParameterizedStatement()
.withStatement("UPDATE "Music" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist='Acme Band' and SongTitle='PartiQL Rocks'"));
return statements;
}
// Handles errors during ExecuteTransaction execution. Use recommendations in error messages below to add error handling specific to
// your application use-case.
private static void handleExecuteTransactionErrors(Exception exception) {
try {
throw exception;
} catch (TransactionCanceledException tce) {
System.out.println("Transaction Cancelled, implies a client issue, fix before retrying. Error: " + tce.getErrorMessage());
} catch (TransactionInProgressException tipe) {
System.out.println("The transaction with the given request token is already in progress, consider changing " +
"retry strategy for this type of error. Error: " + tipe.getErrorMessage());
} catch (IdempotentParameterMismatchException ipme) {
System.out.println("Request rejected because it was retried with a different payload but with a request token that was already used, " +
"change request token for this payload to be accepted. Error: " + ipme.getErrorMessage());
} catch (Exception e) {
handleCommonErrors(e);
}
}
private static void handleCommonErrors(Exception exception) {
try {
throw exception;
} catch (InternalServerErrorException isee) {
System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage());
} catch (RequestLimitExceededException rlee) {
System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " +
"retrying. Error: " + rlee.getErrorMessage());
} catch (ProvisionedThroughputExceededException ptee) {
System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " +
"Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " +
ptee.getErrorMessage());
} catch (ResourceNotFoundException rnfe) {
System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage());
} catch (AmazonServiceException ase) {
System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " +
"service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " +
"configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage());
} catch (AmazonClientException ace) {
System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " +
"service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+
"Error: " + ace.getMessage());
} catch (Exception e) {
System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage());
}
}
}
```
------
The following example shows the different return values when DynamoDB reads items with different conditions specified in the WHERE clause.
------
#### [ AWS CLI ]
1. Save the following JSON code to a file called partiql.json.
```
[
// Item exists and projected attribute exists
{
"Statement": "SELECT * FROM "Music" WHERE Artist='No One You Know' and SongTitle='Call Me Today'"
},
// Item exists but projected attributes do not exist
{
"Statement": "SELECT non_existent_projected_attribute FROM "Music" WHERE Artist='No One You Know' and SongTitle='Call Me Today'"
},
// Item does not exist
{
"Statement": "SELECT * FROM "Music" WHERE Artist='No One I Know' and SongTitle='Call You Today'"
}
]
```
1. following command in a command prompt.
```
aws dynamodb execute-transaction --transact-statements file://partiql.json
```
1. The following response is returned:
```
{
"Responses": [
// Item exists and projected attribute exists
{
"Item": {
"Artist":{
"S": "No One You Know"
},
"SongTitle":{
"S": "Call Me Today"
}
}
},
// Item exists but projected attributes do not exist
{
"Item": {}
},
// Item does not exist
{}
]
}
```
------
# Running batch operations with PartiQL for DynamoDB
This section describes how to use batch statements with PartiQL for DynamoDB.
**Note**
The entire batch must consist of either read statements or write statements; you cannot mix both in one batch.
`BatchExecuteStatement` and `BatchWriteItem` can perform no more than 25 statements per batch.
`BatchExecuteStatement` makes use of `BatchGetItem` which takes a list of primary keys in separate statements.
**Topics**
+ [
## Syntax
](#ql-reference.multiplestatements.batching.syntax)
+ [
## Parameters
](#ql-reference.multiplestatements.batching.parameters)
+ [
## Examples
](#ql-reference.multiplestatements.batching.examples)
## Syntax
```
[
{
"Statement": "SELECT pk FROM ProblemSet WHERE pk = 'p#9StkWHYTxm7x2AqSXcrfu7' AND sk = 'info'"
},
{
"Statement": "SELECT pk FROM ProblemSet WHERE pk = 'p#isC2ChceGbxHgESc4szoTE' AND sk = 'info'"
}
]
```
```
[
{
"Statement":" statement ",
"Parameters":[
{
" parametertype " : " parametervalue "
}, ...]
} , ...
]
```
## Parameters
***statement***
(Required) A PartiQL for DynamoDB supported statement.
+ The entire batch must consist of either read statements or write statements; you cannot mix both in one batch.
+ `BatchExecuteStatement` and `BatchWriteItem` can perform no more than 25 statements per batch.
***parametertype***
(Optional) A DynamoDB type, if parameters were used when specifying the PartiQL statement.
***parametervalue***
(Optional) A parameter value if parameters were used when specifying the PartiQL statement.
## Examples
------
#### [ AWS CLI ]
1. Save the following json to a file called partiql.json
```
[
{
"Statement": "INSERT INTO Music VALUE {'Artist':?,'SongTitle':?}",
"Parameters": [{"S": "Acme Band"}, {"S": "Best Song"}]
},
{
"Statement": "UPDATE Music SET AwardsWon=1, AwardDetail={'Grammys':[2020, 2018]} WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
}
]
```
1. Run the following command in a command prompt.
```
aws dynamodb batch-execute-statement --statements file://partiql.json
```
------
#### [ Java ]
```
public class DynamoDBPartiqlBatch {
public static void main(String[] args) {
// Create the DynamoDB Client with the region you want
AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-2");
try {
// Create BatchExecuteStatementRequest
BatchExecuteStatementRequest batchExecuteStatementRequest = createBatchExecuteStatementRequest();
BatchExecuteStatementResult batchExecuteStatementResult = dynamoDB.batchExecuteStatement(batchExecuteStatementRequest);
System.out.println("BatchExecuteStatement successful.");
// Handle batchExecuteStatementResult
} catch (Exception e) {
handleBatchExecuteStatementErrors(e);
}
}
private static AmazonDynamoDB createDynamoDbClient(String region) {
return AmazonDynamoDBClientBuilder.standard().withRegion(region).build();
}
private static BatchExecuteStatementRequest createBatchExecuteStatementRequest() {
BatchExecuteStatementRequest request = new BatchExecuteStatementRequest();
// Create statements
List statements = getPartiQLBatchStatements();
request.setStatements(statements);
return request;
}
private static List getPartiQLBatchStatements() {
List statements = new ArrayList();
statements.add(new BatchStatementRequest()
.withStatement("INSERT INTO Music value {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}"));
statements.add(new BatchStatementRequest()
.withStatement("UPDATE Music set AwardDetail.BillBoard=[2020] where Artist='Acme Band' and SongTitle='PartiQL Rocks'"));
return statements;
}
// Handles errors during BatchExecuteStatement execution. Use recommendations in error messages below to add error handling specific to
// your application use-case.
private static void handleBatchExecuteStatementErrors(Exception exception) {
try {
throw exception;
} catch (Exception e) {
// There are no API specific errors to handle for BatchExecuteStatement, common DynamoDB API errors are handled below
handleCommonErrors(e);
}
}
private static void handleCommonErrors(Exception exception) {
try {
throw exception;
} catch (InternalServerErrorException isee) {
System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage());
} catch (RequestLimitExceededException rlee) {
System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " +
"retrying. Error: " + rlee.getErrorMessage());
} catch (ProvisionedThroughputExceededException ptee) {
System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " +
"Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " +
ptee.getErrorMessage());
} catch (ResourceNotFoundException rnfe) {
System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage());
} catch (AmazonServiceException ase) {
System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " +
"service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " +
"configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage());
} catch (AmazonClientException ace) {
System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " +
"service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+
"Error: " + ace.getMessage());
} catch (Exception e) {
System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage());
}
}
}
```
------
# IAM security policies with PartiQL for DynamoDB
The following permissions are required:
+ To read items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLSelect` permission on the table or index.
+ To insert items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLInsert` permission on the table or index.
+ To update items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLUpdate` permission on the table or index.
+ To delete items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLDelete` permission on the table or index.
## Example: Allow all PartiQL for DynamoDB statements (Select/Insert/Update/Delete) on a table
The following IAM policy grants permissions to run all PartiQL for DynamoDB statements on a table.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLInsert",
"dynamodb:PartiQLUpdate",
"dynamodb:PartiQLDelete",
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music"
]
}
]
}
```
------
## Example: Allow PartiQL for DynamoDB select statements on a table
The following IAM policy grants permissions to run the `select` statement on a specific table.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music"
]
}
]
}
```
------
## Example: Allow PartiQL for DynamoDB insert statements on an index
The following IAM policy grants permissions to run the `insert` statement on a specific index.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLInsert"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music/index/index1"
]
}
]
}
```
------
## Example: Allow PartiQL for DynamoDB transactional statements only on a table
The following IAM policy grants permissions to run only transactional statements on a specific table.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLInsert",
"dynamodb:PartiQLUpdate",
"dynamodb:PartiQLDelete",
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music"
],
"Condition":{
"StringEquals":{
"dynamodb:EnclosingOperation":[
"ExecuteTransaction"
]
}
}
}
]
}
```
------
## Example: Allow PartiQL for DynamoDB non-transactional reads and writes and block PartiQL transactional reads and writes transactional statements on a table.
The following IAM policy grants permissions to run PartiQL for DynamoDB non-transactional reads and writes while blocking PartiQL for DynamoDB transactional reads and writes.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Deny",
"Action":[
"dynamodb:PartiQLInsert",
"dynamodb:PartiQLUpdate",
"dynamodb:PartiQLDelete",
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music"
],
"Condition":{
"StringEquals":{
"dynamodb:EnclosingOperation":[
"ExecuteTransaction"
]
}
}
},
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLInsert",
"dynamodb:PartiQLUpdate",
"dynamodb:PartiQLDelete",
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/Music"
]
}
]
}
```
------
## Example: Allow select statements and deny full table scan statements in PartiQL for DynamoDB
The following IAM policy grants permissions to run the `select` statement on a specific table while blocking `select` statements that result in a full table scan.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Deny",
"Action":[
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/WatchList"
],
"Condition":{
"Bool":{
"dynamodb:FullTableScan":[
"true"
]
}
}
},
{
"Effect":"Allow",
"Action":[
"dynamodb:PartiQLSelect"
],
"Resource":[
"arn:aws:dynamodb:us-west-2:123456789012:table/WatchList"
]
}
]
}
```
------
# Working with items: Java
You can use the AWS SDK for Java Document API to perform typical create, read, update, and delete (CRUD) operations on Amazon DynamoDB items in a table.
**Note**
The SDK for Java also provides an object persistence model, allowing you to map your client-side classes to DynamoDB tables. This approach can reduce the amount of code that you have to write. For more information, see [Java 1.x: DynamoDBMapper](DynamoDBMapper.md).
This section contains Java examples to perform several Java Document API item actions and several complete working examples.
**Topics**
+ [
## Putting an item
](#PutDocumentAPIJava)
+ [
## Getting an item
](#JavaDocumentAPIGetItem)
+ [
## Batch write: Putting and deleting multiple items
](#BatchWriteDocumentAPIJava)
+ [
## Batch get: Getting multiple items
](#JavaDocumentAPIBatchGetItem)
+ [
## Updating an item
](#JavaDocumentAPIItemUpdate)
+ [
## Deleting an item
](#DeleteMidLevelJava)
+ [
# Example: CRUD operations using the AWS SDK for Java document API
](JavaDocumentAPICRUDExample.md)
+ [
# Example: Batch operations using AWS SDK for Java document API
](batch-operation-document-api-java.md)
+ [
# Example: Handling binary type attributes using the AWS SDK for Java document API
](JavaDocumentAPIBinaryTypeExample.md)
## Putting an item
The `putItem` method stores an item in a table. If the item exists, it replaces the entire item. Instead of replacing the entire item, if you want to update only specific attributes, you can use the `updateItem` method. For more information, see [Updating an item](#JavaDocumentAPIItemUpdate).
------
#### [ Java v2 ]
```
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
import software.amazon.awssdk.services.dynamodb.model.PutItemRequest;
import software.amazon.awssdk.services.dynamodb.model.PutItemResponse;
import software.amazon.awssdk.services.dynamodb.model.ResourceNotFoundException;
import java.util.HashMap;
/**
* Before running this Java V2 code example, set up your development
* environment, including your credentials.
*
* For more information, see the following documentation topic:
*
* https://docs.aws.amazon.com/sdk-for-java/latest/developer-guide/get-started.html
*
* To place items into an Amazon DynamoDB table using the AWS SDK for Java V2,
* its better practice to use the
* Enhanced Client. See the EnhancedPutItem example.
*/
public class PutItem {
public static void main(String[] args) {
final String usage = """
Usage:
Where:
tableName - The Amazon DynamoDB table in which an item is placed (for example, Music3).
key - The key used in the Amazon DynamoDB table (for example, Artist).
keyval - The key value that represents the item to get (for example, Famous Band).
albumTitle - The Album title (for example, AlbumTitle).
AlbumTitleValue - The name of the album (for example, Songs About Life ).
Awards - The awards column (for example, Awards).
AwardVal - The value of the awards (for example, 10).
SongTitle - The song title (for example, SongTitle).
SongTitleVal - The value of the song title (for example, Happy Day).
**Warning** This program will place an item that you specify into a table!
""";
if (args.length != 9) {
System.out.println(usage);
System.exit(1);
}
String tableName = args[0];
String key = args[1];
String keyVal = args[2];
String albumTitle = args[3];
String albumTitleValue = args[4];
String awards = args[5];
String awardVal = args[6];
String songTitle = args[7];
String songTitleVal = args[8];
Region region = Region.US_EAST_1;
DynamoDbClient ddb = DynamoDbClient.builder()
.region(region)
.build();
putItemInTable(ddb, tableName, key, keyVal, albumTitle, albumTitleValue, awards, awardVal, songTitle,
songTitleVal);
System.out.println("Done!");
ddb.close();
}
public static void putItemInTable(DynamoDbClient ddb,
String tableName,
String key,
String keyVal,
String albumTitle,
String albumTitleValue,
String awards,
String awardVal,
String songTitle,
String songTitleVal) {
HashMap itemValues = new HashMap<>();
itemValues.put(key, AttributeValue.builder().s(keyVal).build());
itemValues.put(songTitle, AttributeValue.builder().s(songTitleVal).build());
itemValues.put(albumTitle, AttributeValue.builder().s(albumTitleValue).build());
itemValues.put(awards, AttributeValue.builder().s(awardVal).build());
PutItemRequest request = PutItemRequest.builder()
.tableName(tableName)
.item(itemValues)
.build();
try {
PutItemResponse response = ddb.putItem(request);
System.out.println(tableName + " was successfully updated. The request id is "
+ response.responseMetadata().requestId());
} catch (ResourceNotFoundException e) {
System.err.format("Error: The Amazon DynamoDB table \"%s\" can't be found.\n", tableName);
System.err.println("Be sure that it exists and that you've typed its name correctly!");
System.exit(1);
} catch (DynamoDbException e) {
System.err.println(e.getMessage());
System.exit(1);
}
}
}
```
------
#### [ Java v1 ]
Follow these steps:
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `Table` class to represent the table you want to work with.
1. Create an instance of the `Item` class to represent the new item. You must specify the new item's primary key and its attributes.
1. Call the `putItem` method of the `Table` object, using the `Item` that you created in the preceding step.
The following Java code example demonstrates the preceding tasks. The code writes a new item to the `ProductCatalog` table.
**Example**
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("ProductCatalog");
// Build a list of related items
List relatedItems = new ArrayList();
relatedItems.add(341);
relatedItems.add(472);
relatedItems.add(649);
//Build a map of product pictures
Map pictures = new HashMap();
pictures.put("FrontView", "http://example.com/products/123_front.jpg");
pictures.put("RearView", "http://example.com/products/123_rear.jpg");
pictures.put("SideView", "http://example.com/products/123_left_side.jpg");
//Build a map of product reviews
Map> reviews = new HashMap>();
List fiveStarReviews = new ArrayList();
fiveStarReviews.add("Excellent! Can't recommend it highly enough! Buy it!");
fiveStarReviews.add("Do yourself a favor and buy this");
reviews.put("FiveStar", fiveStarReviews);
List oneStarReviews = new ArrayList();
oneStarReviews.add("Terrible product! Do not buy this.");
reviews.put("OneStar", oneStarReviews);
// Build the item
Item item = new Item()
.withPrimaryKey("Id", 123)
.withString("Title", "Bicycle 123")
.withString("Description", "123 description")
.withString("BicycleType", "Hybrid")
.withString("Brand", "Brand-Company C")
.withNumber("Price", 500)
.withStringSet("Color", new HashSet(Arrays.asList("Red", "Black")))
.withString("ProductCategory", "Bicycle")
.withBoolean("InStock", true)
.withNull("QuantityOnHand")
.withList("RelatedItems", relatedItems)
.withMap("Pictures", pictures)
.withMap("Reviews", reviews);
// Write the item to the table
PutItemOutcome outcome = table.putItem(item);
```
In the preceding example, the item has attributes that are scalars (`String`, `Number`, `Boolean`, `Null`), sets (`String Set`), and document types (`List`, `Map`).
------
### Specifying optional parameters
Along with the required parameters, you can also specify optional parameters to the `putItem` method. For example, the following Java code example uses an optional parameter to specify a condition for uploading the item. If the condition you specify is not met, the AWS SDK for Java throws a `ConditionalCheckFailedException`. The code example specifies the following optional parameters in the `putItem` method:
+ A `ConditionExpression` that defines the conditions for the request. The code defines the condition that the existing item with the same primary key is replaced only if it has an ISBN attribute that equals a specific value.
+ A map for `ExpressionAttributeValues` that is used in the condition. In this case, there is only one substitution required: The placeholder `:val` in the condition expression is replaced at runtime with the actual ISBN value to be checked.
The following example adds a new book item using these optional parameters.
**Example**
```
Item item = new Item()
.withPrimaryKey("Id", 104)
.withString("Title", "Book 104 Title")
.withString("ISBN", "444-4444444444")
.withNumber("Price", 20)
.withStringSet("Authors",
new HashSet(Arrays.asList("Author1", "Author2")));
Map expressionAttributeValues = new HashMap();
expressionAttributeValues.put(":val", "444-4444444444");
PutItemOutcome outcome = table.putItem(
item,
"ISBN = :val", // ConditionExpression parameter
null, // ExpressionAttributeNames parameter - we're not using it for this example
expressionAttributeValues);
```
### PutItem and JSON documents
You can store a JSON document as an attribute in a DynamoDB table. To do this, use the `withJSON` method of `Item`. This method parses the JSON document and maps each element to a native DynamoDB data type.
Suppose that you wanted to store the following JSON document, containing vendors that can fulfill orders for a particular product.
**Example**
```
{
"V01": {
"Name": "Acme Books",
"Offices": [ "Seattle" ]
},
"V02": {
"Name": "New Publishers, Inc.",
"Offices": ["London", "New York"
]
},
"V03": {
"Name": "Better Buy Books",
"Offices": [ "Tokyo", "Los Angeles", "Sydney"
]
}
}
```
You can use the `withJSON` method to store this in the `ProductCatalog` table, in a `Map` attribute named `VendorInfo`. The following Java code example demonstrates how to do this.
```
// Convert the document into a String. Must escape all double-quotes.
String vendorDocument = "{"
+ " \"V01\": {"
+ " \"Name\": \"Acme Books\","
+ " \"Offices\": [ \"Seattle\" ]"
+ " },"
+ " \"V02\": {"
+ " \"Name\": \"New Publishers, Inc.\","
+ " \"Offices\": [ \"London\", \"New York\"" + "]" + "},"
+ " \"V03\": {"
+ " \"Name\": \"Better Buy Books\","
+ "\"Offices\": [ \"Tokyo\", \"Los Angeles\", \"Sydney\""
+ " ]"
+ " }"
+ " }";
Item item = new Item()
.withPrimaryKey("Id", 210)
.withString("Title", "Book 210 Title")
.withString("ISBN", "210-2102102102")
.withNumber("Price", 30)
.withJSON("VendorInfo", vendorDocument);
PutItemOutcome outcome = table.putItem(item);
```
## Getting an item
To retrieve a single item, use the `getItem` method of a `Table` object. Follow these steps:
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `Table` class to represent the table you want to work with.
1. Call the `getItem` method of the `Table` instance. You must specify the primary key of the item that you want to retrieve.
The following Java code example demonstrates the preceding steps. The code gets the item that has the specified partition key.
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("ProductCatalog");
Item item = table.getItem("Id", 210);
```
### Specifying optional parameters
Along with the required parameters, you can also specify optional parameters for the `getItem` method. For example, the following Java code example uses an optional method to retrieve only a specific list of attributes and to specify strongly consistent reads. (To learn more about read consistency, see [DynamoDB read consistency](HowItWorks.ReadConsistency.md).)
You can use a `ProjectionExpression` to retrieve only specific attributes or elements, rather than an entire item. A `ProjectionExpression` can specify top-level or nested attributes using document paths. For more information, see [Using projection expressions in DynamoDB](Expressions.ProjectionExpressions.md).
The parameters of the `getItem` method don't let you specify read consistency. However, you can create a `GetItemSpec`, which provides full access to all of the inputs to the low-level `GetItem` operation. The following code example creates a `GetItemSpec` and uses that spec as input to the `getItem` method.
**Example**
```
GetItemSpec spec = new GetItemSpec()
.withPrimaryKey("Id", 206)
.withProjectionExpression("Id, Title, RelatedItems[0], Reviews.FiveStar")
.withConsistentRead(true);
Item item = table.getItem(spec);
System.out.println(item.toJSONPretty());
```
To print an `Item` in a human-readable format, use the `toJSONPretty` method. The output from the previous example looks like the following.
```
{
"RelatedItems" : [ 341 ],
"Reviews" : {
"FiveStar" : [ "Excellent! Can't recommend it highly enough! Buy it!", "Do yourself a favor and buy this" ]
},
"Id" : 123,
"Title" : "20-Bicycle 123"
}
```
### GetItem and JSON documents
In the [PutItem and JSON documents](#PutItemJavaDocumentAPI.JSON) section, you store a JSON document in a `Map` attribute named `VendorInfo`. You can use the `getItem` method to retrieve the entire document in JSON format. Or you can use document path notation to retrieve only some of the elements in the document. The following Java code example demonstrates these techniques.
```
GetItemSpec spec = new GetItemSpec()
.withPrimaryKey("Id", 210);
System.out.println("All vendor info:");
spec.withProjectionExpression("VendorInfo");
System.out.println(table.getItem(spec).toJSON());
System.out.println("A single vendor:");
spec.withProjectionExpression("VendorInfo.V03");
System.out.println(table.getItem(spec).toJSON());
System.out.println("First office location for this vendor:");
spec.withProjectionExpression("VendorInfo.V03.Offices[0]");
System.out.println(table.getItem(spec).toJSON());
```
The output from the previous example looks like the following.
```
All vendor info:
{"VendorInfo":{"V03":{"Name":"Better Buy Books","Offices":["Tokyo","Los Angeles","Sydney"]},"V02":{"Name":"New Publishers, Inc.","Offices":["London","New York"]},"V01":{"Name":"Acme Books","Offices":["Seattle"]}}}
A single vendor:
{"VendorInfo":{"V03":{"Name":"Better Buy Books","Offices":["Tokyo","Los Angeles","Sydney"]}}}
First office location for a single vendor:
{"VendorInfo":{"V03":{"Offices":["Tokyo"]}}}
```
**Note**
You can use the `toJSON` method to convert any item (or its attributes) to a JSON-formatted string. The following code retrieves several top-level and nested attributes and prints the results as JSON.
```
GetItemSpec spec = new GetItemSpec()
.withPrimaryKey("Id", 210)
.withProjectionExpression("VendorInfo.V01, Title, Price");
Item item = table.getItem(spec);
System.out.println(item.toJSON());
```
The output looks like the following.
```
{"VendorInfo":{"V01":{"Name":"Acme Books","Offices":["Seattle"]}},"Price":30,"Title":"Book 210 Title"}
```
## Batch write: Putting and deleting multiple items
*Batch write* refers to putting and deleting multiple items in a batch. The `batchWriteItem` method enables you to put and delete multiple items from one or more tables in a single call. The following are the steps to put or delete multiple items using the AWS SDK for Java Document API.
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `TableWriteItems` class that describes all the put and delete operations for a table. If you want to write to multiple tables in a single batch write operation, you must create one `TableWriteItems` instance per table.
1. Call the `batchWriteItem` method by providing the `TableWriteItems` objects that you created in the preceding step.
1. Process the response. You should check if there were any unprocessed request items returned in the response. This could happen if you reach the provisioned throughput quota or some other transient error. Also, DynamoDB limits the request size and the number of operations you can specify in a request. If you exceed these limits, DynamoDB rejects the request. For more information, see [Quotas in Amazon DynamoDB](ServiceQuotas.md).
The following Java code example demonstrates the preceding steps. The example performs a `batchWriteItem` operation on two tables: `Forum` and `Thread`. The corresponding `TableWriteItems` objects define the following actions:
+ Put an item in the `Forum` table.
+ Put and delete an item in the `Thread` table.
The code then calls `batchWriteItem` to perform the operation.
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
TableWriteItems forumTableWriteItems = new TableWriteItems("Forum")
.withItemsToPut(
new Item()
.withPrimaryKey("Name", "Amazon RDS")
.withNumber("Threads", 0));
TableWriteItems threadTableWriteItems = new TableWriteItems("Thread")
.withItemsToPut(
new Item()
.withPrimaryKey("ForumName","Amazon RDS","Subject","Amazon RDS Thread 1")
.withHashAndRangeKeysToDelete("ForumName","Some partition key value", "Amazon S3", "Some sort key value");
BatchWriteItemOutcome outcome = dynamoDB.batchWriteItem(forumTableWriteItems, threadTableWriteItems);
// Code for checking unprocessed items is omitted in this example
```
For a working example, see [Example: Batch write operation using the AWS SDK for Java document API](batch-operation-document-api-java.md#JavaDocumentAPIBatchWrite).
## Batch get: Getting multiple items
The `batchGetItem` method enables you to retrieve multiple items from one or more tables. To retrieve a single item, you can use the `getItem` method.
Follow these steps:
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `TableKeysAndAttributes` class that describes a list of primary key values to retrieve from a table. If you want to read from multiple tables in a single batch get operation, you must create one `TableKeysAndAttributes` instance per table.
1. Call the `batchGetItem` method by providing the `TableKeysAndAttributes` objects that you created in the preceding step.
The following Java code example demonstrates the preceding steps. The example retrieves two items from the `Forum` table and three items from the `Thread` table.
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
TableKeysAndAttributes forumTableKeysAndAttributes = new TableKeysAndAttributes(forumTableName);
forumTableKeysAndAttributes.addHashOnlyPrimaryKeys("Name",
"Amazon S3",
"Amazon DynamoDB");
TableKeysAndAttributes threadTableKeysAndAttributes = new TableKeysAndAttributes(threadTableName);
threadTableKeysAndAttributes.addHashAndRangePrimaryKeys("ForumName", "Subject",
"Amazon DynamoDB","DynamoDB Thread 1",
"Amazon DynamoDB","DynamoDB Thread 2",
"Amazon S3","S3 Thread 1");
BatchGetItemOutcome outcome = dynamoDB.batchGetItem(
forumTableKeysAndAttributes, threadTableKeysAndAttributes);
for (String tableName : outcome.getTableItems().keySet()) {
System.out.println("Items in table " + tableName);
List- items = outcome.getTableItems().get(tableName);
for (Item item : items) {
System.out.println(item);
}
}
```
### Specifying optional parameters
Along with the required parameters, you can also specify optional parameters when using `batchGetItem`. For example, you can provide a `ProjectionExpression` with each `TableKeysAndAttributes` you define. This allows you to specify the attributes that you want to retrieve from the table.
The following code example retrieves two items from the `Forum` table. The `withProjectionExpression` parameter specifies that only the `Threads` attribute is to be retrieved.
**Example**
```
TableKeysAndAttributes forumTableKeysAndAttributes = new TableKeysAndAttributes("Forum")
.withProjectionExpression("Threads");
forumTableKeysAndAttributes.addHashOnlyPrimaryKeys("Name",
"Amazon S3",
"Amazon DynamoDB");
BatchGetItemOutcome outcome = dynamoDB.batchGetItem(forumTableKeysAndAttributes);
```
## Updating an item
The `updateItem` method of a `Table` object can update existing attribute values, add new attributes, or delete attributes from an existing item.
The `updateItem` method behaves as follows:
+ If an item does not exist (no item in the table with the specified primary key), `updateItem` adds a new item to the table.
+ If an item exists, `updateItem` performs the update as specified by the `UpdateExpression` parameter.
**Note**
It is also possible to "update" an item using `putItem`. For example, if you call `putItem` to add an item to the table, but there is already an item with the specified primary key, `putItem` replaces the entire item. If there are attributes in the existing item that are not specified in the input, `putItem` removes those attributes from the item.
In general, we recommend that you use `updateItem` whenever you want to modify any item attributes. The `updateItem` method only modifies the item attributes that you specify in the input, and the other attributes in the item remain unchanged.
Follow these steps:
1. Create an instance of the `Table` class to represent the table that you want to work with.
1. Call the `updateTable` method of the `Table` instance. You must specify the primary key of the item that you want to retrieve, along with an `UpdateExpression` that describes the attributes to modify and how to modify them.
The following Java code example demonstrates the preceding tasks. The code updates a book item in the `ProductCatalog` table. It adds a new author to the set of `Authors` and deletes the existing `ISBN` attribute. It also reduces the price by one.
An `ExpressionAttributeValues` map is used in the `UpdateExpression`. The placeholders `:val1` and `:val2` are replaced at runtime with the actual values for `Authors` and `Price`.
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("ProductCatalog");
Map expressionAttributeNames = new HashMap();
expressionAttributeNames.put("#A", "Authors");
expressionAttributeNames.put("#P", "Price");
expressionAttributeNames.put("#I", "ISBN");
Map expressionAttributeValues = new HashMap();
expressionAttributeValues.put(":val1",
new HashSet(Arrays.asList("Author YY","Author ZZ")));
expressionAttributeValues.put(":val2", 1); //Price
UpdateItemOutcome outcome = table.updateItem(
"Id", // key attribute name
101, // key attribute value
"add #A :val1 set #P = #P - :val2 remove #I", // UpdateExpression
expressionAttributeNames,
expressionAttributeValues);
```
### Specifying optional parameters
Along with the required parameters, you can also specify optional parameters for the `updateItem` method, including a condition that must be met in order for the update is to occur. If the condition you specify is not met, the AWS SDK for Java throws a `ConditionalCheckFailedException`. For example, the following Java code example conditionally updates a book item price to 25. It specifies a `ConditionExpression` stating that the price should be updated only if the existing price is 20.
**Example**
```
Table table = dynamoDB.getTable("ProductCatalog");
Map expressionAttributeNames = new HashMap();
expressionAttributeNames.put("#P", "Price");
Map expressionAttributeValues = new HashMap();
expressionAttributeValues.put(":val1", 25); // update Price to 25...
expressionAttributeValues.put(":val2", 20); //...but only if existing Price is 20
UpdateItemOutcome outcome = table.updateItem(
new PrimaryKey("Id",101),
"set #P = :val1", // UpdateExpression
"#P = :val2", // ConditionExpression
expressionAttributeNames,
expressionAttributeValues);
```
### Atomic counter
You can use `updateItem` to implement an atomic counter, where you increment or decrement the value of an existing attribute without interfering with other write requests. To increment an atomic counter, use an `UpdateExpression` with a `set` action to add a numeric value to an existing attribute of type `Number`.
The following example demonstrates this, incrementing the `Quantity` attribute by one. It also demonstrates the use of the `ExpressionAttributeNames` parameter in an `UpdateExpression`.
```
Table table = dynamoDB.getTable("ProductCatalog");
Map expressionAttributeNames = new HashMap();
expressionAttributeNames.put("#p", "PageCount");
Map expressionAttributeValues = new HashMap();
expressionAttributeValues.put(":val", 1);
UpdateItemOutcome outcome = table.updateItem(
"Id", 121,
"set #p = #p + :val",
expressionAttributeNames,
expressionAttributeValues);
```
## Deleting an item
The `deleteItem` method deletes an item from a table. You must provide the primary key of the item that you want to delete.
Follow these steps:
1. Create an instance of the `DynamoDB` client.
1. Call the `deleteItem` method by providing the key of the item you want to delete.
The following Java example demonstrates these tasks.
**Example**
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("ProductCatalog");
DeleteItemOutcome outcome = table.deleteItem("Id", 101);
```
### Specifying optional parameters
You can specify optional parameters for `deleteItem`. For example, the following Java code example specifies a `ConditionExpression`, stating that a book item in `ProductCatalog` can only be deleted if the book is no longer in publication (the `InPublication` attribute is false).
**Example**
```
Map expressionAttributeValues = new HashMap();
expressionAttributeValues.put(":val", false);
DeleteItemOutcome outcome = table.deleteItem("Id",103,
"InPublication = :val",
null, // ExpressionAttributeNames - not used in this example
expressionAttributeValues);
```
# Example: CRUD operations using the AWS SDK for Java document API
The following code example illustrates CRUD operations on an Amazon DynamoDB item. The example creates an item, retrieves it, performs various updates, and finally deletes the item.
**Note**
The SDK for Java also provides an object persistence model, enabling you to map your client-side classes to DynamoDB tables. This approach can reduce the amount of code that you have to write. For more information, see [Java 1.x: DynamoDBMapper](DynamoDBMapper.md).
**Note**
This code example assumes that you have already loaded data into DynamoDB for your account by following the instructions in the [Creating tables and loading data for code examples in DynamoDB](SampleData.md) section.
For step-by-step instructions to run the following example, see [Java code examples](CodeSamples.Java.md).
```
package com.amazonaws.codesamples.document;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.document.DeleteItemOutcome;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.Table;
import com.amazonaws.services.dynamodbv2.document.UpdateItemOutcome;
import com.amazonaws.services.dynamodbv2.document.spec.DeleteItemSpec;
import com.amazonaws.services.dynamodbv2.document.spec.UpdateItemSpec;
import com.amazonaws.services.dynamodbv2.document.utils.NameMap;
import com.amazonaws.services.dynamodbv2.document.utils.ValueMap;
import com.amazonaws.services.dynamodbv2.model.ReturnValue;
public class DocumentAPIItemCRUDExample {
static AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
static DynamoDB dynamoDB = new DynamoDB(client);
static String tableName = "ProductCatalog";
public static void main(String[] args) throws IOException {
createItems();
retrieveItem();
// Perform various updates.
updateMultipleAttributes();
updateAddNewAttribute();
updateExistingAttributeConditionally();
// Delete the item.
deleteItem();
}
private static void createItems() {
Table table = dynamoDB.getTable(tableName);
try {
Item item = new Item().withPrimaryKey("Id", 120).withString("Title", "Book 120 Title")
.withString("ISBN", "120-1111111111")
.withStringSet("Authors", new HashSet(Arrays.asList("Author12", "Author22")))
.withNumber("Price", 20).withString("Dimensions", "8.5x11.0x.75").withNumber("PageCount", 500)
.withBoolean("InPublication", false).withString("ProductCategory", "Book");
table.putItem(item);
item = new Item().withPrimaryKey("Id", 121).withString("Title", "Book 121 Title")
.withString("ISBN", "121-1111111111")
.withStringSet("Authors", new HashSet(Arrays.asList("Author21", "Author 22")))
.withNumber("Price", 20).withString("Dimensions", "8.5x11.0x.75").withNumber("PageCount", 500)
.withBoolean("InPublication", true).withString("ProductCategory", "Book");
table.putItem(item);
} catch (Exception e) {
System.err.println("Create items failed.");
System.err.println(e.getMessage());
}
}
private static void retrieveItem() {
Table table = dynamoDB.getTable(tableName);
try {
Item item = table.getItem("Id", 120, "Id, ISBN, Title, Authors", null);
System.out.println("Printing item after retrieving it....");
System.out.println(item.toJSONPretty());
} catch (Exception e) {
System.err.println("GetItem failed.");
System.err.println(e.getMessage());
}
}
private static void updateAddNewAttribute() {
Table table = dynamoDB.getTable(tableName);
try {
UpdateItemSpec updateItemSpec = new UpdateItemSpec().withPrimaryKey("Id", 121)
.withUpdateExpression("set #na = :val1").withNameMap(new NameMap().with("#na", "NewAttribute"))
.withValueMap(new ValueMap().withString(":val1", "Some value"))
.withReturnValues(ReturnValue.ALL_NEW);
UpdateItemOutcome outcome = table.updateItem(updateItemSpec);
// Check the response.
System.out.println("Printing item after adding new attribute...");
System.out.println(outcome.getItem().toJSONPretty());
} catch (Exception e) {
System.err.println("Failed to add new attribute in " + tableName);
System.err.println(e.getMessage());
}
}
private static void updateMultipleAttributes() {
Table table = dynamoDB.getTable(tableName);
try {
UpdateItemSpec updateItemSpec = new UpdateItemSpec().withPrimaryKey("Id", 120)
.withUpdateExpression("add #a :val1 set #na=:val2")
.withNameMap(new NameMap().with("#a", "Authors").with("#na", "NewAttribute"))
.withValueMap(
new ValueMap().withStringSet(":val1", "Author YY", "Author ZZ").withString(":val2",
"someValue"))
.withReturnValues(ReturnValue.ALL_NEW);
UpdateItemOutcome outcome = table.updateItem(updateItemSpec);
// Check the response.
System.out.println("Printing item after multiple attribute update...");
System.out.println(outcome.getItem().toJSONPretty());
} catch (Exception e) {
System.err.println("Failed to update multiple attributes in " + tableName);
System.err.println(e.getMessage());
}
}
private static void updateExistingAttributeConditionally() {
Table table = dynamoDB.getTable(tableName);
try {
// Specify the desired price (25.00) and also the condition (price =
// 20.00)
UpdateItemSpec updateItemSpec = new UpdateItemSpec().withPrimaryKey("Id", 120)
.withReturnValues(ReturnValue.ALL_NEW).withUpdateExpression("set #p = :val1")
.withConditionExpression("#p = :val2").withNameMap(new NameMap().with("#p", "Price"))
.withValueMap(new ValueMap().withNumber(":val1", 25).withNumber(":val2", 20));
UpdateItemOutcome outcome = table.updateItem(updateItemSpec);
// Check the response.
System.out.println("Printing item after conditional update to new attribute...");
System.out.println(outcome.getItem().toJSONPretty());
} catch (Exception e) {
System.err.println("Error updating item in " + tableName);
System.err.println(e.getMessage());
}
}
private static void deleteItem() {
Table table = dynamoDB.getTable(tableName);
try {
DeleteItemSpec deleteItemSpec = new DeleteItemSpec().withPrimaryKey("Id", 120)
.withConditionExpression("#ip = :val").withNameMap(new NameMap().with("#ip", "InPublication"))
.withValueMap(new ValueMap().withBoolean(":val", false)).withReturnValues(ReturnValue.ALL_OLD);
DeleteItemOutcome outcome = table.deleteItem(deleteItemSpec);
// Check the response.
System.out.println("Printing item that was deleted...");
System.out.println(outcome.getItem().toJSONPretty());
} catch (Exception e) {
System.err.println("Error deleting item in " + tableName);
System.err.println(e.getMessage());
}
}
}
```
# Example: Batch operations using AWS SDK for Java document API
This section provides examples of batch write and batch get operations in Amazon DynamoDB using the AWS SDK for Java Document API.
**Note**
The SDK for Java also provides an object persistence model, enabling you to map your client-side classes to DynamoDB tables. This approach can reduce the amount of code that you have to write. For more information, see [Java 1.x: DynamoDBMapper](DynamoDBMapper.md).
**Topics**
+ [
## Example: Batch write operation using the AWS SDK for Java document API
](#JavaDocumentAPIBatchWrite)
+ [
## Example: Batch get operation using the AWS SDK for Java document API
](#JavaDocumentAPIBatchGet)
## Example: Batch write operation using the AWS SDK for Java document API
The following Java code example uses the `batchWriteItem` method to perform the following put and delete operations:
+ Put one item in the `Forum` table.
+ Put one item and delete one item from the `Thread` table.
You can specify any number of put and delete requests against one or more tables when creating your batch write request. However, `batchWriteItem` limits the size of a batch write request and the number of put and delete operations in a single batch write operation. If your request exceeds these limits, your request is rejected. If your table does not have sufficient provisioned throughput to serve this request, the unprocessed request items are returned in the response.
The following example checks the response to see if it has any unprocessed request items. If it does, it loops back and resends the `batchWriteItem` request with unprocessed items in the request. If you followed the examples in this guide, you should already have created the `Forum` and `Thread` tables. You can also create these tables and upload sample data programmatically. For more information, see [Creating example tables and uploading data using the AWS SDK for Java](AppendixSampleDataCodeJava.md).
For step-by-step instructions for testing the following sample, see [Java code examples](CodeSamples.Java.md).
**Example**
```
package com.amazonaws.codesamples.document;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.document.BatchWriteItemOutcome;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.TableWriteItems;
import com.amazonaws.services.dynamodbv2.model.WriteRequest;
public class DocumentAPIBatchWrite {
static AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
static DynamoDB dynamoDB = new DynamoDB(client);
static String forumTableName = "Forum";
static String threadTableName = "Thread";
public static void main(String[] args) throws IOException {
writeMultipleItemsBatchWrite();
}
private static void writeMultipleItemsBatchWrite() {
try {
// Add a new item to Forum
TableWriteItems forumTableWriteItems = new TableWriteItems(forumTableName) // Forum
.withItemsToPut(new Item().withPrimaryKey("Name", "Amazon RDS").withNumber("Threads", 0));
// Add a new item, and delete an existing item, from Thread
// This table has a partition key and range key, so need to specify
// both of them
TableWriteItems threadTableWriteItems = new TableWriteItems(threadTableName)
.withItemsToPut(
new Item().withPrimaryKey("ForumName", "Amazon RDS", "Subject", "Amazon RDS Thread 1")
.withString("Message", "ElastiCache Thread 1 message")
.withStringSet("Tags", new HashSet(Arrays.asList("cache", "in-memory"))))
.withHashAndRangeKeysToDelete("ForumName", "Subject", "Amazon S3", "S3 Thread 100");
System.out.println("Making the request.");
BatchWriteItemOutcome outcome = dynamoDB.batchWriteItem(forumTableWriteItems, threadTableWriteItems);
do {
// Check for unprocessed keys which could happen if you exceed
// provisioned throughput
Map> unprocessedItems = outcome.getUnprocessedItems();
if (outcome.getUnprocessedItems().size() == 0) {
System.out.println("No unprocessed items found");
} else {
System.out.println("Retrieving the unprocessed items");
outcome = dynamoDB.batchWriteItemUnprocessed(unprocessedItems);
}
} while (outcome.getUnprocessedItems().size() > 0);
} catch (Exception e) {
System.err.println("Failed to retrieve items: ");
e.printStackTrace(System.err);
}
}
}
```
## Example: Batch get operation using the AWS SDK for Java document API
The following Java code example uses the `batchGetItem` method to retrieve multiple items from the `Forum` and the `Thread` tables. The `BatchGetItemRequest` specifies the table names and a list of keys for each item to get. The example processes the response by printing the items retrieved.
**Note**
This code example assumes that you have already loaded data into DynamoDB for your account by following the instructions in the [Creating tables and loading data for code examples in DynamoDB](SampleData.md) section.
For step-by-step instructions to run the following example, see [Java code examples](CodeSamples.Java.md).
**Example**
```
package com.amazonaws.codesamples.document;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.document.BatchGetItemOutcome;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.TableKeysAndAttributes;
import com.amazonaws.services.dynamodbv2.model.KeysAndAttributes;
public class DocumentAPIBatchGet {
static AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
static DynamoDB dynamoDB = new DynamoDB(client);
static String forumTableName = "Forum";
static String threadTableName = "Thread";
public static void main(String[] args) throws IOException {
retrieveMultipleItemsBatchGet();
}
private static void retrieveMultipleItemsBatchGet() {
try {
TableKeysAndAttributes forumTableKeysAndAttributes = new TableKeysAndAttributes(forumTableName);
// Add a partition key
forumTableKeysAndAttributes.addHashOnlyPrimaryKeys("Name", "Amazon S3", "Amazon DynamoDB");
TableKeysAndAttributes threadTableKeysAndAttributes = new TableKeysAndAttributes(threadTableName);
// Add a partition key and a sort key
threadTableKeysAndAttributes.addHashAndRangePrimaryKeys("ForumName", "Subject", "Amazon DynamoDB",
"DynamoDB Thread 1", "Amazon DynamoDB", "DynamoDB Thread 2", "Amazon S3", "S3 Thread 1");
System.out.println("Making the request.");
BatchGetItemOutcome outcome = dynamoDB.batchGetItem(forumTableKeysAndAttributes,
threadTableKeysAndAttributes);
Map unprocessed = null;
do {
for (String tableName : outcome.getTableItems().keySet()) {
System.out.println("Items in table " + tableName);
List
- items = outcome.getTableItems().get(tableName);
for (Item item : items) {
System.out.println(item.toJSONPretty());
}
}
// Check for unprocessed keys which could happen if you exceed
// provisioned
// throughput or reach the limit on response size.
unprocessed = outcome.getUnprocessedKeys();
if (unprocessed.isEmpty()) {
System.out.println("No unprocessed keys found");
} else {
System.out.println("Retrieving the unprocessed keys");
outcome = dynamoDB.batchGetItemUnprocessed(unprocessed);
}
} while (!unprocessed.isEmpty());
} catch (Exception e) {
System.err.println("Failed to retrieve items.");
System.err.println(e.getMessage());
}
}
}
```
# Example: Handling binary type attributes using the AWS SDK for Java document API
The following Java code example illustrates handling binary type attributes. The example adds an item to the `Reply` table. The item includes a binary type attribute (`ExtendedMessage`) that stores compressed data. The example then retrieves the item and prints all the attribute values. For illustration, the example uses the `GZIPOutputStream` class to compress a sample stream and assign it to the `ExtendedMessage` attribute. When the binary attribute is retrieved, it is decompressed using the `GZIPInputStream` class.
**Note**
The SDK for Java also provides an object persistence model, enabling you to map your client-side classes to DynamoDB tables. This approach can reduce the amount of code that you have to write. For more information, see [Java 1.x: DynamoDBMapper](DynamoDBMapper.md).
If you followed the [Creating tables and loading data for code examples in DynamoDB](SampleData.md) section, you should already have created the `Reply` table. You can also create this table programmatically. For more information, see [Creating example tables and uploading data using the AWS SDK for Java](AppendixSampleDataCodeJava.md).
For step-by-step instructions for testing the following sample, see [Java code examples](CodeSamples.Java.md).
**Example**
```
package com.amazonaws.codesamples.document;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.Table;
import com.amazonaws.services.dynamodbv2.document.spec.GetItemSpec;
public class DocumentAPIItemBinaryExample {
static AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
static DynamoDB dynamoDB = new DynamoDB(client);
static String tableName = "Reply";
static SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
public static void main(String[] args) throws IOException {
try {
// Format the primary key values
String threadId = "Amazon DynamoDB#DynamoDB Thread 2";
dateFormatter.setTimeZone(TimeZone.getTimeZone("UTC"));
String replyDateTime = dateFormatter.format(new Date());
// Add a new reply with a binary attribute type
createItem(threadId, replyDateTime);
// Retrieve the reply with a binary attribute type
retrieveItem(threadId, replyDateTime);
// clean up by deleting the item
deleteItem(threadId, replyDateTime);
} catch (Exception e) {
System.err.println("Error running the binary attribute type example: " + e);
e.printStackTrace(System.err);
}
}
public static void createItem(String threadId, String replyDateTime) throws IOException {
Table table = dynamoDB.getTable(tableName);
// Craft a long message
String messageInput = "Long message to be compressed in a lengthy forum reply";
// Compress the long message
ByteBuffer compressedMessage = compressString(messageInput.toString());
table.putItem(new Item().withPrimaryKey("Id", threadId).withString("ReplyDateTime", replyDateTime)
.withString("Message", "Long message follows").withBinary("ExtendedMessage", compressedMessage)
.withString("PostedBy", "User A"));
}
public static void retrieveItem(String threadId, String replyDateTime) throws IOException {
Table table = dynamoDB.getTable(tableName);
GetItemSpec spec = new GetItemSpec().withPrimaryKey("Id", threadId, "ReplyDateTime", replyDateTime)
.withConsistentRead(true);
Item item = table.getItem(spec);
// Uncompress the reply message and print
String uncompressed = uncompressString(ByteBuffer.wrap(item.getBinary("ExtendedMessage")));
System.out.println("Reply message:\n" + " Id: " + item.getString("Id") + "\n" + " ReplyDateTime: "
+ item.getString("ReplyDateTime") + "\n" + " PostedBy: " + item.getString("PostedBy") + "\n"
+ " Message: "
+ item.getString("Message") + "\n" + " ExtendedMessage (uncompressed): " + uncompressed + "\n");
}
public static void deleteItem(String threadId, String replyDateTime) {
Table table = dynamoDB.getTable(tableName);
table.deleteItem("Id", threadId, "ReplyDateTime", replyDateTime);
}
private static ByteBuffer compressString(String input) throws IOException {
// Compress the UTF-8 encoded String into a byte[]
ByteArrayOutputStream baos = new ByteArrayOutputStream();
GZIPOutputStream os = new GZIPOutputStream(baos);
os.write(input.getBytes("UTF-8"));
os.close();
baos.close();
byte[] compressedBytes = baos.toByteArray();
// The following code writes the compressed bytes to a ByteBuffer.
// A simpler way to do this is by simply calling
// ByteBuffer.wrap(compressedBytes);
// However, the longer form below shows the importance of resetting the
// position of the buffer
// back to the beginning of the buffer if you are writing bytes directly
// to it, since the SDK
// will consider only the bytes after the current position when sending
// data to DynamoDB.
// Using the "wrap" method automatically resets the position to zero.
ByteBuffer buffer = ByteBuffer.allocate(compressedBytes.length);
buffer.put(compressedBytes, 0, compressedBytes.length);
buffer.position(0); // Important: reset the position of the ByteBuffer
// to the beginning
return buffer;
}
private static String uncompressString(ByteBuffer input) throws IOException {
byte[] bytes = input.array();
ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
GZIPInputStream is = new GZIPInputStream(bais);
int chunkSize = 1024;
byte[] buffer = new byte[chunkSize];
int length = 0;
while ((length = is.read(buffer, 0, chunkSize)) != -1) {
baos.write(buffer, 0, length);
}
String result = new String(baos.toByteArray(), "UTF-8");
is.close();
baos.close();
bais.close();
return result;
}
}
```
# Working with items: .NET
You can use the AWS SDK for .NET low-level API to perform typical create, read, update, and delete (CRUD) operations on an item in a table. The following are the common steps that you follow to perform data CRUD operations using the .NET low-level API:
1. Create an instance of the `AmazonDynamoDBClient` class (the client).
1. Provide the operation-specific required parameters in a corresponding request object.
For example, use the `PutItemRequest` request object when uploading an item and use the `GetItemRequest` request object when retrieving an existing item.
You can use the request object to provide both the required and optional parameters.
1. Run the appropriate method provided by the client by passing in the request object that you created in the preceding step.
The `AmazonDynamoDBClient` client provides `PutItem`, `GetItem`, `UpdateItem`, and `DeleteItem` methods for the CRUD operations.
**Topics**
+ [
## Putting an item
](#PutItemLowLevelAPIDotNet)
+ [
## Getting an item
](#GetItemLowLevelDotNET)
+ [
## Updating an item
](#UpdateItemLowLevelDotNet)
+ [
## Atomic counter
](#AtomicCounterLowLevelDotNet)
+ [
## Deleting an item
](#DeleteMidLevelDotNet)
+ [
## Batch write: Putting and deleting multiple items
](#BatchWriteLowLevelDotNet)
+ [
## Batch get: Getting multiple items
](#BatchGetLowLevelDotNet)
+ [
# Example: CRUD operations using the AWS SDK for .NET low-level API
](LowLevelDotNetItemsExample.md)
+ [
# Example: Batch operations using the AWS SDK for .NET low-level API
](batch-operation-lowlevel-dotnet.md)
+ [
# Example: Handling binary type attributes using the AWS SDK for .NET low-level API
](LowLevelDotNetBinaryTypeExample.md)
## Putting an item
The `PutItem` method uploads an item to a table. If the item exists, it replaces the entire item.
**Note**
Instead of replacing the entire item, if you want to update only specific attributes, you can use the `UpdateItem` method. For more information, see [Updating an item](#UpdateItemLowLevelDotNet).
The following are the steps to upload an item using the low-level .NET SDK API:
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required parameters by creating an instance of the `PutItemRequest` class.
To put an item, you must provide the table name and the item.
1. Run the `PutItem` method by providing the `PutItemRequest` object that you created in the preceding step.
The following C\$1 example demonstrates the preceding steps. The example uploads an item to the `ProductCatalog` table.
**Example**
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new PutItemRequest
{
TableName = tableName,
Item = new Dictionary()
{
{ "Id", new AttributeValue { N = "201" }},
{ "Title", new AttributeValue { S = "Book 201 Title" }},
{ "ISBN", new AttributeValue { S = "11-11-11-11" }},
{ "Price", new AttributeValue { S = "20.00" }},
{
"Authors",
new AttributeValue
{ SS = new List{"Author1", "Author2"} }
}
}
};
client.PutItem(request);
```
In the preceding example, you upload a book item that has the `Id`, `Title`, `ISBN`, and `Authors` attributes. Note that `Id` is a numeric type attribute, and all other attributes are of the string type. Authors is a `String` set.
### Specifying optional parameters
You can also provide optional parameters using the `PutItemRequest` object as shown in the following C\$1 example. The example specifies the following optional parameters:
+ `ExpressionAttributeNames`, `ExpressionAttributeValues`, and `ConditionExpression` specify that the item can be replaced only if the existing item has the ISBN attribute with a specific value.
+ `ReturnValues` parameter to request the old item in the response.
**Example**
```
var request = new PutItemRequest
{
TableName = tableName,
Item = new Dictionary()
{
{ "Id", new AttributeValue { N = "104" }},
{ "Title", new AttributeValue { S = "Book 104 Title" }},
{ "ISBN", new AttributeValue { S = "444-4444444444" }},
{ "Authors",
new AttributeValue { SS = new List{"Author3"}}}
},
// Optional parameters.
ExpressionAttributeNames = new Dictionary()
{
{"#I", "ISBN"}
},
ExpressionAttributeValues = new Dictionary()
{
{":isbn",new AttributeValue {S = "444-4444444444"}}
},
ConditionExpression = "#I = :isbn"
};
var response = client.PutItem(request);
```
For more information, see [PutItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_PutItem.html).
## Getting an item
The `GetItem` method retrieves an item.
**Note**
To retrieve multiple items, you can use the `BatchGetItem` method. For more information, see [Batch get: Getting multiple items](#BatchGetLowLevelDotNet).
The following are the steps to retrieve an existing item using the low-level AWS SDK for .NET API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required parameters by creating an instance of the `GetItemRequest` class.
To get an item, you must provide the table name and primary key of the item.
1. Run the `GetItem` method by providing the `GetItemRequest` object that you created in the preceding step.
The following C\$1 example demonstrates the preceding steps. The example retrieves an item from the `ProductCatalog` table.
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new GetItemRequest
{
TableName = tableName,
Key = new Dictionary() { { "Id", new AttributeValue { N = "202" } } },
};
var response = client.GetItem(request);
// Check the response.
var result = response.GetItemResult;
var attributeMap = result.Item; // Attribute list in the response.
```
### Specifying optional parameters
You can also provide optional parameters using the `GetItemRequest` object, as shown in the following C\$1 example. The sample specifies the following optional parameters:
+ `ProjectionExpression` parameter to specify the attributes to retrieve.
+ `ConsistentRead` parameter to perform a strongly consistent read. To learn more read consistency, see [DynamoDB read consistency](HowItWorks.ReadConsistency.md).
**Example**
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new GetItemRequest
{
TableName = tableName,
Key = new Dictionary() { { "Id", new AttributeValue { N = "202" } } },
// Optional parameters.
ProjectionExpression = "Id, ISBN, Title, Authors",
ConsistentRead = true
};
var response = client.GetItem(request);
// Check the response.
var result = response.GetItemResult;
var attributeMap = result.Item;
```
For more information, see [GetItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_GetItem.html).
## Updating an item
The `UpdateItem` method updates an existing item if it is present. You can use the `UpdateItem` operation to update existing attribute values, add new attributes, or delete attributes from the existing collection. If the item that has the specified primary key is not found, it adds a new item.
The `UpdateItem` operation uses the following guidelines:
+ If the item does not exist, `UpdateItem` adds a new item using the primary key that is specified in the input.
+ If the item exists, `UpdateItem` applies the updates as follows:
+ Replaces the existing attribute values by the values in the update.
+ If the attribute that you provide in the input does not exist, it adds a new attribute to the item.
+ If the input attribute is null, it deletes the attribute, if it is present.
+ If you use `ADD` for the `Action`, you can add values to an existing set (string or number set), or mathematically add (use a positive number) or subtract (use a negative number) from the existing numeric attribute value.
**Note**
The `PutItem` operation also can perform an update. For more information, see [Putting an item](#PutItemLowLevelAPIDotNet). For example, if you call `PutItem` to upload an item and the primary key exists, the `PutItem` operation replaces the entire item. If there are attributes in the existing item and those attributes are not specified in the input, the `PutItem` operation deletes those attributes. However, `UpdateItem` updates only the specified input attributes. Any other existing attributes of that item remain unchanged.
The following are the steps to update an existing item using the low-level .NET SDK API:
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required parameters by creating an instance of the `UpdateItemRequest` class.
This is the request object in which you describe all the updates, such as add attributes, update existing attributes, or delete attributes. To delete an existing attribute, specify the attribute name with null value.
1. Run the `UpdateItem` method by providing the `UpdateItemRequest` object that you created in the preceding step.
The following C\$1 code example demonstrates the preceding steps. The example updates a book item in the `ProductCatalog` table. It adds a new author to the `Authors` collection, and deletes the existing `ISBN` attribute. It also reduces the price by one.
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new UpdateItemRequest
{
TableName = tableName,
Key = new Dictionary() { { "Id", new AttributeValue { N = "202" } } },
ExpressionAttributeNames = new Dictionary()
{
{"#A", "Authors"},
{"#P", "Price"},
{"#NA", "NewAttribute"},
{"#I", "ISBN"}
},
ExpressionAttributeValues = new Dictionary()
{
{":auth",new AttributeValue { SS = {"Author YY","Author ZZ"}}},
{":p",new AttributeValue {N = "1"}},
{":newattr",new AttributeValue {S = "someValue"}},
},
// This expression does the following:
// 1) Adds two new authors to the list
// 2) Reduces the price
// 3) Adds a new attribute to the item
// 4) Removes the ISBN attribute from the item
UpdateExpression = "ADD #A :auth SET #P = #P - :p, #NA = :newattr REMOVE #I"
};
var response = client.UpdateItem(request);
```
### Specifying optional parameters
You can also provide optional parameters using the `UpdateItemRequest` object, as shown in the following C\$1 example. It specifies the following optional parameters:
+ `ExpressionAttributeValues` and `ConditionExpression` to specify that the price can be updated only if the existing price is 20.00.
+ `ReturnValues` parameter to request the updated item in the response.
**Example**
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new UpdateItemRequest
{
Key = new Dictionary() { { "Id", new AttributeValue { N = "202" } } },
// Update price only if the current price is 20.00.
ExpressionAttributeNames = new Dictionary()
{
{"#P", "Price"}
},
ExpressionAttributeValues = new Dictionary()
{
{":newprice",new AttributeValue {N = "22"}},
{":currprice",new AttributeValue {N = "20"}}
},
UpdateExpression = "SET #P = :newprice",
ConditionExpression = "#P = :currprice",
TableName = tableName,
ReturnValues = "ALL_NEW" // Return all the attributes of the updated item.
};
var response = client.UpdateItem(request);
```
For more information, see [UpdateItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_UpdateItem.html).
## Atomic counter
You can use `updateItem` to implement an atomic counter, where you increment or decrement the value of an existing attribute without interfering with other write requests. To update an atomic counter, use `updateItem` with an attribute of type `Number` in the `UpdateExpression` parameter, and `ADD` as the `Action`.
The following example demonstrates this, incrementing the `Quantity` attribute by one.
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new UpdateItemRequest
{
Key = new Dictionary() { { "Id", new AttributeValue { N = "121" } } },
ExpressionAttributeNames = new Dictionary()
{
{"#Q", "Quantity"}
},
ExpressionAttributeValues = new Dictionary()
{
{":incr",new AttributeValue {N = "1"}}
},
UpdateExpression = "SET #Q = #Q + :incr",
TableName = tableName
};
var response = client.UpdateItem(request);
```
## Deleting an item
The `DeleteItem` method deletes an item from a table.
The following are the steps to delete an item using the low-level .NET SDK API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required parameters by creating an instance of the `DeleteItemRequest` class.
To delete an item, the table name and item's primary key are required.
1. Run the `DeleteItem` method by providing the `DeleteItemRequest` object that you created in the preceding step.
**Example**
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string tableName = "ProductCatalog";
var request = new DeleteItemRequest
{
TableName = tableName,
Key = new Dictionary() { { "Id", new AttributeValue { N = "201" } } },
};
var response = client.DeleteItem(request);
```
### Specifying optional parameters
You can also provide optional parameters using the `DeleteItemRequest` object as shown in the following C\$1 code example. It specifies the following optional parameters:
+ `ExpressionAttributeValues` and `ConditionExpression` to specify that the book item can be deleted only if it is no longer in publication (the InPublication attribute value is false).
+ `ReturnValues` parameter to request the deleted item in the response.
**Example**
```
var request = new DeleteItemRequest
{
TableName = tableName,
Key = new Dictionary() { { "Id", new AttributeValue { N = "201" } } },
// Optional parameters.
ReturnValues = "ALL_OLD",
ExpressionAttributeNames = new Dictionary()
{
{"#IP", "InPublication"}
},
ExpressionAttributeValues = new Dictionary()
{
{":inpub",new AttributeValue {BOOL = false}}
},
ConditionExpression = "#IP = :inpub"
};
var response = client.DeleteItem(request);
```
For more information, see [DeleteItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_DeleteItem.html).
## Batch write: Putting and deleting multiple items
*Batch write* refers to putting and deleting multiple items in a batch. The `BatchWriteItem` method enables you to put and delete multiple items from one or more tables in a single call. The following are the steps to retrieve multiple items using the low-level .NET SDK API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Describe all the put and delete operations by creating an instance of the `BatchWriteItemRequest` class.
1. Run the `BatchWriteItem` method by providing the `BatchWriteItemRequest` object that you created in the preceding step.
1. Process the response. You should check if there were any unprocessed request items returned in the response. This could happen if you reach the provisioned throughput quota or some other transient error. Also, DynamoDB limits the request size and the number of operations you can specify in a request. If you exceed these limits, DynamoDB rejects the request. For more information, see [BatchWriteItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchWriteItem.html).
The following C\$1 code example demonstrates the preceding steps. The example creates a `BatchWriteItemRequest` to perform the following write operations:
+ Put an item in `Forum` table.
+ Put and delete an item from `Thread` table.
The code runs `BatchWriteItem` to perform a batch operation.
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string table1Name = "Forum";
string table2Name = "Thread";
var request = new BatchWriteItemRequest
{
RequestItems = new Dictionary>
{
{
table1Name, new List
{
new WriteRequest
{
PutRequest = new PutRequest
{
Item = new Dictionary
{
{ "Name", new AttributeValue { S = "Amazon S3 forum" } },
{ "Threads", new AttributeValue { N = "0" }}
}
}
}
}
} ,
{
table2Name, new List
{
new WriteRequest
{
PutRequest = new PutRequest
{
Item = new Dictionary
{
{ "ForumName", new AttributeValue { S = "Amazon S3 forum" } },
{ "Subject", new AttributeValue { S = "My sample question" } },
{ "Message", new AttributeValue { S = "Message Text." } },
{ "KeywordTags", new AttributeValue { SS = new List { "Amazon S3", "Bucket" } } }
}
}
},
new WriteRequest
{
DeleteRequest = new DeleteRequest
{
Key = new Dictionary()
{
{ "ForumName", new AttributeValue { S = "Some forum name" } },
{ "Subject", new AttributeValue { S = "Some subject" } }
}
}
}
}
}
}
};
response = client.BatchWriteItem(request);
```
For a working example, see [Example: Batch operations using the AWS SDK for .NET low-level API](batch-operation-lowlevel-dotnet.md).
## Batch get: Getting multiple items
The `BatchGetItem` method enables you to retrieve multiple items from one or more tables.
**Note**
To retrieve a single item, you can use the `GetItem` method.
The following are the steps to retrieve multiple items using the low-level AWS SDK for .NET API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required parameters by creating an instance of the `BatchGetItemRequest` class.
To retrieve multiple items, the table name and a list of primary key values are required.
1. Run the `BatchGetItem` method by providing the `BatchGetItemRequest` object that you created in the preceding step.
1. Process the response. You should check if there were any unprocessed keys, which could happen if you reach the provisioned throughput quota or some other transient error.
The following C\$1 code example demonstrates the preceding steps. The example retrieves items from two tables, `Forum` and `Thread`. The request specifies two items in the `Forum` and three items in the `Thread` table. The response includes items from both of the tables. The code shows how you can process the response.
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string table1Name = "Forum";
string table2Name = "Thread";
var request = new BatchGetItemRequest
{
RequestItems = new Dictionary()
{
{ table1Name,
new KeysAndAttributes
{
Keys = new List>()
{
new Dictionary()
{
{ "Name", new AttributeValue { S = "DynamoDB" } }
},
new Dictionary()
{
{ "Name", new AttributeValue { S = "Amazon S3" } }
}
}
}
},
{
table2Name,
new KeysAndAttributes
{
Keys = new List>()
{
new Dictionary()
{
{ "ForumName", new AttributeValue { S = "DynamoDB" } },
{ "Subject", new AttributeValue { S = "DynamoDB Thread 1" } }
},
new Dictionary()
{
{ "ForumName", new AttributeValue { S = "DynamoDB" } },
{ "Subject", new AttributeValue { S = "DynamoDB Thread 2" } }
},
new Dictionary()
{
{ "ForumName", new AttributeValue { S = "Amazon S3" } },
{ "Subject", new AttributeValue { S = "Amazon S3 Thread 1" } }
}
}
}
}
}
};
var response = client.BatchGetItem(request);
// Check the response.
var result = response.BatchGetItemResult;
var responses = result.Responses; // The attribute list in the response.
var table1Results = responses[table1Name];
Console.WriteLine("Items in table {0}" + table1Name);
foreach (var item1 in table1Results.Items)
{
PrintItem(item1);
}
var table2Results = responses[table2Name];
Console.WriteLine("Items in table {1}" + table2Name);
foreach (var item2 in table2Results.Items)
{
PrintItem(item2);
}
// Any unprocessed keys? could happen if you exceed ProvisionedThroughput or some other error.
Dictionary unprocessedKeys = result.UnprocessedKeys;
foreach (KeyValuePair pair in unprocessedKeys)
{
Console.WriteLine(pair.Key, pair.Value);
}
```
### Specifying optional parameters
You can also provide optional parameters using the `BatchGetItemRequest` object as shown in the following C\$1 code example. The example retrieves two items from the `Forum` table. It specifies the following optional parameter:
+ `ProjectionExpression` parameter to specify the attributes to retrieve.
**Example**
```
AmazonDynamoDBClient client = new AmazonDynamoDBClient();
string table1Name = "Forum";
var request = new BatchGetItemRequest
{
RequestItems = new Dictionary()
{
{ table1Name,
new KeysAndAttributes
{
Keys = new List>()
{
new Dictionary()
{
{ "Name", new AttributeValue { S = "DynamoDB" } }
},
new Dictionary()
{
{ "Name", new AttributeValue { S = "Amazon S3" } }
}
}
},
// Optional - name of an attribute to retrieve.
ProjectionExpression = "Title"
}
}
};
var response = client.BatchGetItem(request);
```
For more information, see [BatchGetItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchGetItem.html).
# Example: CRUD operations using the AWS SDK for .NET low-level API
The following C\$1 code example illustrates CRUD operations on an Amazon DynamoDB item. The example adds an item to the `ProductCatalog` table, retrieves it, performs various updates, and finally deletes the item. If you haven't created this table, you can also create it programmatically. For more information, see [Creating example tables and uploading data using the AWS SDK for .NET](AppendixSampleDataCodeDotNET.md).
For step-by-step instructions for testing the following sample, see [.NET code examples](CodeSamples.DotNet.md).
**Example**
```
using System;
using System.Collections.Generic;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
using Amazon.SecurityToken;
namespace com.amazonaws.codesamples
{
class LowLevelItemCRUDExample
{
private static string tableName = "ProductCatalog";
private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();
static void Main(string[] args)
{
try
{
CreateItem();
RetrieveItem();
// Perform various updates.
UpdateMultipleAttributes();
UpdateExistingAttributeConditionally();
// Delete item.
DeleteItem();
Console.WriteLine("To continue, press Enter");
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine("To continue, press Enter");
Console.ReadLine();
}
}
private static void CreateItem()
{
var request = new PutItemRequest
{
TableName = tableName,
Item = new Dictionary()
{
{ "Id", new AttributeValue {
N = "1000"
}},
{ "Title", new AttributeValue {
S = "Book 201 Title"
}},
{ "ISBN", new AttributeValue {
S = "11-11-11-11"
}},
{ "Authors", new AttributeValue {
SS = new List{"Author1", "Author2" }
}},
{ "Price", new AttributeValue {
N = "20.00"
}},
{ "Dimensions", new AttributeValue {
S = "8.5x11.0x.75"
}},
{ "InPublication", new AttributeValue {
BOOL = false
} }
}
};
client.PutItem(request);
}
private static void RetrieveItem()
{
var request = new GetItemRequest
{
TableName = tableName,
Key = new Dictionary()
{
{ "Id", new AttributeValue {
N = "1000"
} }
},
ProjectionExpression = "Id, ISBN, Title, Authors",
ConsistentRead = true
};
var response = client.GetItem(request);
// Check the response.
var attributeList = response.Item; // attribute list in the response.
Console.WriteLine("\nPrinting item after retrieving it ............");
PrintItem(attributeList);
}
private static void UpdateMultipleAttributes()
{
var request = new UpdateItemRequest
{
Key = new Dictionary()
{
{ "Id", new AttributeValue {
N = "1000"
} }
},
// Perform the following updates:
// 1) Add two new authors to the list
// 1) Set a new attribute
// 2) Remove the ISBN attribute
ExpressionAttributeNames = new Dictionary()
{
{"#A","Authors"},
{"#NA","NewAttribute"},
{"#I","ISBN"}
},
ExpressionAttributeValues = new Dictionary()
{
{":auth",new AttributeValue {
SS = {"Author YY", "Author ZZ"}
}},
{":new",new AttributeValue {
S = "New Value"
}}
},
UpdateExpression = "ADD #A :auth SET #NA = :new REMOVE #I",
TableName = tableName,
ReturnValues = "ALL_NEW" // Give me all attributes of the updated item.
};
var response = client.UpdateItem(request);
// Check the response.
var attributeList = response.Attributes; // attribute list in the response.
// print attributeList.
Console.WriteLine("\nPrinting item after multiple attribute update ............");
PrintItem(attributeList);
}
private static void UpdateExistingAttributeConditionally()
{
var request = new UpdateItemRequest
{
Key = new Dictionary()
{
{ "Id", new AttributeValue {
N = "1000"
} }
},
ExpressionAttributeNames = new Dictionary()
{
{"#P", "Price"}
},
ExpressionAttributeValues = new Dictionary()
{
{":newprice",new AttributeValue {
N = "22.00"
}},
{":currprice",new AttributeValue {
N = "20.00"
}}
},
// This updates price only if current price is 20.00.
UpdateExpression = "SET #P = :newprice",
ConditionExpression = "#P = :currprice",
TableName = tableName,
ReturnValues = "ALL_NEW" // Give me all attributes of the updated item.
};
var response = client.UpdateItem(request);
// Check the response.
var attributeList = response.Attributes; // attribute list in the response.
Console.WriteLine("\nPrinting item after updating price value conditionally ............");
PrintItem(attributeList);
}
private static void DeleteItem()
{
var request = new DeleteItemRequest
{
TableName = tableName,
Key = new Dictionary()
{
{ "Id", new AttributeValue {
N = "1000"
} }
},
// Return the entire item as it appeared before the update.
ReturnValues = "ALL_OLD",
ExpressionAttributeNames = new Dictionary()
{
{"#IP", "InPublication"}
},
ExpressionAttributeValues = new Dictionary()
{
{":inpub",new AttributeValue {
BOOL = false
}}
},
ConditionExpression = "#IP = :inpub"
};
var response = client.DeleteItem(request);
// Check the response.
var attributeList = response.Attributes; // Attribute list in the response.
// Print item.
Console.WriteLine("\nPrinting item that was just deleted ............");
PrintItem(attributeList);
}
private static void PrintItem(Dictionary attributeList)
{
foreach (KeyValuePair kvp in attributeList)
{
string attributeName = kvp.Key;
AttributeValue value = kvp.Value;
Console.WriteLine(
attributeName + " " +
(value.S == null ? "" : "S=[" + value.S + "]") +
(value.N == null ? "" : "N=[" + value.N + "]") +
(value.SS == null ? "" : "SS=[" + string.Join(",", value.SS.ToArray()) + "]") +
(value.NS == null ? "" : "NS=[" + string.Join(",", value.NS.ToArray()) + "]")
);
}
Console.WriteLine("************************************************");
}
}
}
```
# Example: Batch operations using the AWS SDK for .NET low-level API
**Topics**
+ [
## Example: Batch write operation using the AWS SDK for .NET low-level API
](#batch-write-low-level-dotnet)
+ [
## Example: Batch get operation using the AWS SDK for .NET low-level API
](#LowLevelDotNetBatchGet)
This section provides examples of batch operations, *batch write* and *batch get*, that Amazon DynamoDB supports.
## Example: Batch write operation using the AWS SDK for .NET low-level API
The following C\$1 code example uses the `BatchWriteItem` method to perform the following put and delete operations:
+ Put one item in the `Forum` table.
+ Put one item and delete one item from the `Thread` table.
You can specify any number of put and delete requests against one or more tables when creating your batch write request. However, DynamoDB `BatchWriteItem` limits the size of a batch write request and the number of put and delete operations in a single batch write operation. For more information, see [BatchWriteItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchWriteItem.html). If your request exceeds these limits, your request is rejected. If your table does not have sufficient provisioned throughput to serve this request, the unprocessed request items are returned in the response.
The following example checks the response to see if it has any unprocessed request items. If it does, it loops back and resends the `BatchWriteItem` request with unprocessed items in the request. You can also create these sample tables and upload sample data programmatically. For more information, see [Creating example tables and uploading data using the AWS SDK for .NET](AppendixSampleDataCodeDotNET.md).
For step-by-step instructions for testing the following sample, see [.NET code examples](CodeSamples.DotNet.md).
**Example**
```
using System;
using System.Collections.Generic;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
namespace com.amazonaws.codesamples
{
class LowLevelBatchWrite
{
private static string table1Name = "Forum";
private static string table2Name = "Thread";
private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();
static void Main(string[] args)
{
try
{
TestBatchWrite();
}
catch (AmazonServiceException e) { Console.WriteLine(e.Message); }
catch (Exception e) { Console.WriteLine(e.Message); }
Console.WriteLine("To continue, press Enter");
Console.ReadLine();
}
private static void TestBatchWrite()
{
var request = new BatchWriteItemRequest
{
ReturnConsumedCapacity = "TOTAL",
RequestItems = new Dictionary>
{
{
table1Name, new List
{
new WriteRequest
{
PutRequest = new PutRequest
{
Item = new Dictionary
{
{ "Name", new AttributeValue {
S = "S3 forum"
} },
{ "Threads", new AttributeValue {
N = "0"
}}
}
}
}
}
},
{
table2Name, new List
{
new WriteRequest
{
PutRequest = new PutRequest
{
Item = new Dictionary
{
{ "ForumName", new AttributeValue {
S = "S3 forum"
} },
{ "Subject", new AttributeValue {
S = "My sample question"
} },
{ "Message", new AttributeValue {
S = "Message Text."
} },
{ "KeywordTags", new AttributeValue {
SS = new List { "S3", "Bucket" }
} }
}
}
},
new WriteRequest
{
// For the operation to delete an item, if you provide a primary key value
// that does not exist in the table, there is no error, it is just a no-op.
DeleteRequest = new DeleteRequest
{
Key = new Dictionary()
{
{ "ForumName", new AttributeValue {
S = "Some partition key value"
} },
{ "Subject", new AttributeValue {
S = "Some sort key value"
} }
}
}
}
}
}
}
};
CallBatchWriteTillCompletion(request);
}
private static void CallBatchWriteTillCompletion(BatchWriteItemRequest request)
{
BatchWriteItemResponse response;
int callCount = 0;
do
{
Console.WriteLine("Making request");
response = client.BatchWriteItem(request);
callCount++;
// Check the response.
var tableConsumedCapacities = response.ConsumedCapacity;
var unprocessed = response.UnprocessedItems;
Console.WriteLine("Per-table consumed capacity");
foreach (var tableConsumedCapacity in tableConsumedCapacities)
{
Console.WriteLine("{0} - {1}", tableConsumedCapacity.TableName, tableConsumedCapacity.CapacityUnits);
}
Console.WriteLine("Unprocessed");
foreach (var unp in unprocessed)
{
Console.WriteLine("{0} - {1}", unp.Key, unp.Value.Count);
}
Console.WriteLine();
// For the next iteration, the request will have unprocessed items.
request.RequestItems = unprocessed;
} while (response.UnprocessedItems.Count > 0);
Console.WriteLine("Total # of batch write API calls made: {0}", callCount);
}
}
}
```
## Example: Batch get operation using the AWS SDK for .NET low-level API
The following C\$1 code example uses the `BatchGetItem` method to retrieve multiple items from the `Forum` and the `Thread` tables in Amazon DynamoDB. The `BatchGetItemRequest` specifies the table names and a list of primary keys for each table. The example processes the response by printing the items retrieved.
For step-by-step instructions for testing the following sample, see [.NET code examples](CodeSamples.DotNet.md).
**Example**
```
using System;
using System.Collections.Generic;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
namespace com.amazonaws.codesamples
{
class LowLevelBatchGet
{
private static string table1Name = "Forum";
private static string table2Name = "Thread";
private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();
static void Main(string[] args)
{
try
{
RetrieveMultipleItemsBatchGet();
Console.WriteLine("To continue, press Enter");
Console.ReadLine();
}
catch (AmazonServiceException e) { Console.WriteLine(e.Message); }
catch (Exception e) { Console.WriteLine(e.Message); }
}
private static void RetrieveMultipleItemsBatchGet()
{
var request = new BatchGetItemRequest
{
RequestItems = new Dictionary()
{
{ table1Name,
new KeysAndAttributes
{
Keys = new List >()
{
new Dictionary()
{
{ "Name", new AttributeValue {
S = "Amazon DynamoDB"
} }
},
new Dictionary()
{
{ "Name", new AttributeValue {
S = "Amazon S3"
} }
}
}
}},
{
table2Name,
new KeysAndAttributes
{
Keys = new List >()
{
new Dictionary()
{
{ "ForumName", new AttributeValue {
S = "Amazon DynamoDB"
} },
{ "Subject", new AttributeValue {
S = "DynamoDB Thread 1"
} }
},
new Dictionary()
{
{ "ForumName", new AttributeValue {
S = "Amazon DynamoDB"
} },
{ "Subject", new AttributeValue {
S = "DynamoDB Thread 2"
} }
},
new Dictionary()
{
{ "ForumName", new AttributeValue {
S = "Amazon S3"
} },
{ "Subject", new AttributeValue {
S = "S3 Thread 1"
} }
}
}
}
}
}
};
BatchGetItemResponse response;
do
{
Console.WriteLine("Making request");
response = client.BatchGetItem(request);
// Check the response.
var responses = response.Responses; // Attribute list in the response.
foreach (var tableResponse in responses)
{
var tableResults = tableResponse.Value;
Console.WriteLine("Items retrieved from table {0}", tableResponse.Key);
foreach (var item1 in tableResults)
{
PrintItem(item1);
}
}
// Any unprocessed keys? could happen if you exceed ProvisionedThroughput or some other error.
Dictionary unprocessedKeys = response.UnprocessedKeys;
foreach (var unprocessedTableKeys in unprocessedKeys)
{
// Print table name.
Console.WriteLine(unprocessedTableKeys.Key);
// Print unprocessed primary keys.
foreach (var key in unprocessedTableKeys.Value.Keys)
{
PrintItem(key);
}
}
request.RequestItems = unprocessedKeys;
} while (response.UnprocessedKeys.Count > 0);
}
private static void PrintItem(Dictionary attributeList)
{
foreach (KeyValuePair kvp in attributeList)
{
string attributeName = kvp.Key;
AttributeValue value = kvp.Value;
Console.WriteLine(
attributeName + " " +
(value.S == null ? "" : "S=[" + value.S + "]") +
(value.N == null ? "" : "N=[" + value.N + "]") +
(value.SS == null ? "" : "SS=[" + string.Join(",", value.SS.ToArray()) + "]") +
(value.NS == null ? "" : "NS=[" + string.Join(",", value.NS.ToArray()) + "]")
);
}
Console.WriteLine("************************************************");
}
}
}
```
# Example: Handling binary type attributes using the AWS SDK for .NET low-level API
The following C\$1 code example illustrates the handling of binary type attributes. The example adds an item to the `Reply` table. The item includes a binary type attribute (`ExtendedMessage`) that stores compressed data. The example then retrieves the item and prints all the attribute values. For illustration, the example uses the `GZipStream` class to compress a sample stream and assigns it to the `ExtendedMessage` attribute, and decompresses it when printing the attribute value.
For step-by-step instructions for testing the following example, see [.NET code examples](CodeSamples.DotNet.md).
**Example**
```
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
namespace com.amazonaws.codesamples
{
class LowLevelItemBinaryExample
{
private static string tableName = "Reply";
private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();
static void Main(string[] args)
{
// Reply table primary key.
string replyIdPartitionKey = "Amazon DynamoDB#DynamoDB Thread 1";
string replyDateTimeSortKey = Convert.ToString(DateTime.UtcNow);
try
{
CreateItem(replyIdPartitionKey, replyDateTimeSortKey);
RetrieveItem(replyIdPartitionKey, replyDateTimeSortKey);
// Delete item.
DeleteItem(replyIdPartitionKey, replyDateTimeSortKey);
Console.WriteLine("To continue, press Enter");
Console.ReadLine();
}
catch (AmazonDynamoDBException e) { Console.WriteLine(e.Message); }
catch (AmazonServiceException e) { Console.WriteLine(e.Message); }
catch (Exception e) { Console.WriteLine(e.Message); }
}
private static void CreateItem(string partitionKey, string sortKey)
{
MemoryStream compressedMessage = ToGzipMemoryStream("Some long extended message to compress.");
var request = new PutItemRequest
{
TableName = tableName,
Item = new Dictionary()
{
{ "Id", new AttributeValue {
S = partitionKey
}},
{ "ReplyDateTime", new AttributeValue {
S = sortKey
}},
{ "Subject", new AttributeValue {
S = "Binary type "
}},
{ "Message", new AttributeValue {
S = "Some message about the binary type"
}},
{ "ExtendedMessage", new AttributeValue {
B = compressedMessage
}}
}
};
client.PutItem(request);
}
private static void RetrieveItem(string partitionKey, string sortKey)
{
var request = new GetItemRequest
{
TableName = tableName,
Key = new Dictionary()
{
{ "Id", new AttributeValue {
S = partitionKey
} },
{ "ReplyDateTime", new AttributeValue {
S = sortKey
} }
},
ConsistentRead = true
};
var response = client.GetItem(request);
// Check the response.
var attributeList = response.Item; // attribute list in the response.
Console.WriteLine("\nPrinting item after retrieving it ............");
PrintItem(attributeList);
}
private static void DeleteItem(string partitionKey, string sortKey)
{
var request = new DeleteItemRequest
{
TableName = tableName,
Key = new Dictionary()
{
{ "Id", new AttributeValue {
S = partitionKey
} },
{ "ReplyDateTime", new AttributeValue {
S = sortKey
} }
}
};
var response = client.DeleteItem(request);
}
private static void PrintItem(Dictionary attributeList)
{
foreach (KeyValuePair kvp in attributeList)
{
string attributeName = kvp.Key;
AttributeValue value = kvp.Value;
Console.WriteLine(
attributeName + " " +
(value.S == null ? "" : "S=[" + value.S + "]") +
(value.N == null ? "" : "N=[" + value.N + "]") +
(value.SS == null ? "" : "SS=[" + string.Join(",", value.SS.ToArray()) + "]") +
(value.NS == null ? "" : "NS=[" + string.Join(",", value.NS.ToArray()) + "]") +
(value.B == null ? "" : "B=[" + FromGzipMemoryStream(value.B) + "]")
);
}
Console.WriteLine("************************************************");
}
private static MemoryStream ToGzipMemoryStream(string value)
{
MemoryStream output = new MemoryStream();
using (GZipStream zipStream = new GZipStream(output, CompressionMode.Compress, true))
using (StreamWriter writer = new StreamWriter(zipStream))
{
writer.Write(value);
}
return output;
}
private static string FromGzipMemoryStream(MemoryStream stream)
{
using (GZipStream zipStream = new GZipStream(stream, CompressionMode.Decompress))
using (StreamReader reader = new StreamReader(zipStream))
{
return reader.ReadToEnd();
}
}
}
}
```