Getting started with Amazon dynamodb 7: project update

Time:2020-10-25

The previous section introduced dynamodb’s query. Originally, this section planned to introduce the query using index, but when you see the update operation randomly, you should write the update operation first

Update (modify the data in the table)

The SQL language provides update statements for modifying data. Dynamodb uses the updateitem operation to accomplish similar tasks.

SQL

In SQL, you can use the update statement to modify one or more rows. The set clause specifies a new value for one or more columns, and the where clause determines the modified row. Examples are as follows:

UPDATE Music
SET RecordLabel = 'Global Records'
WHERE Artist = 'No One You Know' AND SongTitle = 'Call Me Today';

If no rows match the where clause, the update statement does not work.

DynamoDB

In dynamodb, you can use the updateitem operation to modify a single item.

The API syntax is as follows:

{
   "AttributeUpdates": {
      "string" : {
         "Action": "string",
         "Value": {
            "B": blob,
            "BOOL": boolean,
            "BS": [ blob ],
            "L": [
               "AttributeValue"
            ],
            "M": {
               "string" : "AttributeValue"
            },
            "N": "string",
            "NS": [ "string" ],
            "NULL": boolean,
            "S": "string",
            "SS": [ "string" ]
         }
      }
   },
   "ConditionalOperator": "string",
   "ConditionExpression": "string",
   "Expected": {
      "string" : {
         "AttributeValueList": [
            {
               "B": blob,
               "BOOL": boolean,
               "BS": [ blob ],
               "L": [
                  "AttributeValue"
               ],
               "M": {
                  "string" : "AttributeValue"
               },
               "N": "string",
               "NS": [ "string" ],
               "NULL": boolean,
               "S": "string",
               "SS": [ "string" ]
            }
         ],
         "ComparisonOperator": "string",
         "Exists": boolean,
         "Value": {
            "B": blob,
            "BOOL": boolean,
            "BS": [ blob ],
            "L": [
               "AttributeValue"
            ],
            "M": {
               "string" : "AttributeValue"
            },
            "N": "string",
            "NS": [ "string" ],
            "NULL": boolean,
            "S": "string",
            "SS": [ "string" ]
         }
      }
   },
   "ExpressionAttributeNames": {
      "string" : "string"
   },
   "ExpressionAttributeValues": {
      "string" : {
         "B": blob,
         "BOOL": boolean,
         "BS": [ blob ],
         "L": [
            "AttributeValue"
         ],
         "M": {
            "string" : "AttributeValue"
         },
         "N": "string",
         "NS": [ "string" ],
         "NULL": boolean,
         "S": "string",
         "SS": [ "string" ]
      }
   },
   "Key": {
      "string" : {
         "B": blob,
         "BOOL": boolean,
         "BS": [ blob ],
         "L": [
            "AttributeValue"
         ],
         "M": {
            "string" : "AttributeValue"
         },
         "N": "string",
         "NS": [ "string" ],
         "NULL": boolean,
         "S": "string",
         "SS": [ "string" ]
      }
   },
   "ReturnConsumedCapacity": "string",
   "ReturnItemCollectionMetrics": "string",
   "ReturnValues": "string",
   "TableName": "string",
   "UpdateExpression": "string"
}

Parameter Description:

  • Key: primary key, used to locate items

  • Tablename: table name (minimum 3. Maximum 255)

  • Expected:

  • Attributeupdates: Legacy parameter, obsolete

  • Conditionaloperator: Legacy parameter, obsolete

  • Conditionexpression: conditional expression (successfully completed only if the specific conditionexpression evaluates to true)

  • Expressionattributenames: alias of the name of the conditional expression. For example, date is a reserved word, and the alias can be defined as ා D

  • Expressionattributevalues: values of conditional expressions

  • Return combined capacity: displays the number of units of write capacity used

    • Total will return the write capacity occupied by the table and all its global secondary indexes;

    • Indexes only returns the write capacity occupied by global secondary index;

    • None means you don’t need to return any occupancy statistics.

  • Returnvalues: the data returned after the update

    • None – if not specified, return none (this is the default)

    • ALL_ Old – returns the entire project as it was before the update.

    • ALL_ New – returns the entire project as it was after the update.

    • UPDATED_ Old – returns only the updated value as it was before the update.

    • UPDATED_ New – returns only the updated value as it was after the update.

  • Update expression: Specifies the properties to modify and their new values, and the update expression also specifies how to modify the properties. Here is a syntax summary of the update expression:

update-expression ::=
SET set-action , ...
| REMOVE remove-action , ...  
| ADD add-action , ...
| DELETE delete-action , ...  

The update expression consists of multiple parts. Each section begins with a set, remove, add, or delete keyword. You can include any part of the update expression in any order. However, each partial keyword can only appear once. You can modify multiple properties at the same time. Here are some examples of update expressions:

  • SET list[0] = :val1

  • REMOVE #m.nestedField1, #m.nestedField2

  • ADD aNumber :val2, anotherNumber :val3

  • DELETE aSet :val4

The following example shows a single update expression with multiple parts:

SET list[0] = :val1 REMOVE #m.nestedField1, #m.nestedField2 ADD aNumber :val2, anotherNumber :val3 DELETE aSet :val4

We can use any property name in the update expression,The first character is A-Z or A-ZThe second character, if present, is A-Z, A-Z, or 0-9
If the property name does not meet this requirement, you need to define the expression property name as a placeholder. Refer to (expression property name) for more information.

To specify a text value in an update expression, you can use the expression property value. For more information, refer to (expression property values).


SET

Use the set action in an update expression to add one or more properties and values to an item. If these properties already exist, they are updated. You can also use set to add or subtract numeric type properties. Perform set operations on multiple properties, separated by commas.

Set syntax is as follows:

set-action ::=
    path = value

value ::=
    operand
    | operand '+' operand
    | operand '-' operand

operand ::=
    path | function
  • The path element is the document path of the project. (for example, info in the project is the dictionary, and the path of a in info is info [‘a ‘])

  • The operand element can be the document path of the project, or it can be a function.

The set operation supports the following functions:

  • If_ not_ Exists (path, operand) – if the item does not contain an attribute in the specified path_ not_ Exists evaluates to operand; otherwise, it evaluates to path. You can use this function to avoid overriding properties that already exist in the project.

  • list_ Append (operand, operand) – this function evaluates to a list to which new elements are added. The new element must be included in the list. For example, to add 2 to the list, the operands will be [2]. You can append new elements to the beginning or end of the list by reversing the order of operands.

Here are some examples of using set operations in these functions.

If the property already exists, the following example does nothing; otherwise, it sets the property to the default value.

SET Price = if_not_exists(Price, 100)

The following example adds a new element to the list of five star comments. Expression property name ා PR is productreviews; attribute value: R is a list containing only one element. If the list is preceded by two elements [0] and [1], the new element will be [2].

SET #pr.FiveStar = list_append(#pr.FiveStar, :r)

The following example adds another element to the list of five star comments, but at this point the element is attached at the beginning of the list at [0]. All other elements in the list will be moved one bit.

SET #pr.FiveStar = list_append(:r, #pr.FiveStar)
REMOVE

Use the remove operation in an update expression to remove one or more elements from the project. To perform multiple remove operations, separate them with commas.

The following is a syntax summary of remove in the update expression. The only operand is the document path of the property you want to delete:

remove-action ::=
    path

The following is an example of an update expression that uses the remove operation. Remove multiple properties from the project:

REMOVE Title, RelatedItems[2], Pictures.RearView

Use remove for list elements

When you delete an existing list element, the remaining elements are shifted. For example, consider the following list:

MyNumbers: { ["Zero","One","Two","Three","Four"] }

The list contains elements [0], [1], [2], [3], and [4]. Now, we use the remove operation to remove two elements:

REMOVE MyNumbers[1], MyNumbers[3]

The remaining elements are shifted to the right to generate a list with elements [0], [1], and [2], each with the following data:

MyNumbers: { ["Zero","Two","Four"] }

If you use remove to delete nonexistent items beyond the position of the last element in the list, nothing is done: that is, no data is deleted. For example, the following expression has no effect on the mynumbers list:

REMOVE MyNumbers[11]
ADD

The add operation only supports numeric and set data types. In general, we recommend using set instead of add.

Use add in an update expression to do any of the following:

  • If the property does not already exist, the new property and its value are added to the project.

  • If the property already exists, the behavior of add depends on the data type of the property:

  • If the property is a number and the added value is also a number, the value is added mathematically to the existing property. (if the value is negative, the value is subtracted from the existing attribute.)

  • If the property is a set, and the value you add is also a set, the value is appended to the existing set.

  • To perform multiple add operations, separate them with commas.

In the following grammar Abstract:

  • The path element is the document path of the attribute. Property must be numeric or set data type.

  • The value element is the value to add to the attribute (for numeric data types), or the set to attach to the attribute (for set types).

add-action ::=
    path value

Here are some examples of update expressions that use the add operation.

The following example adds numbers. Expression property value: n is a number, which will be added to price.

ADD Price :n

The following example adds one or more values to the color set. Expression property value: C is the string set.

ADD Color :c
DELETE

The delete operation only supports set data types.

Use the delete operation in an update expression to remove elements from the set. To separate multiple deletions, use a comma.

In the following grammar Abstract:

  • The path element is the document path of the attribute. The property must be a set data type.

  • The value element is the element in the set to be deleted.

delete-action ::=
    path value

The following example uses the delete operation to delete elements from a color set. Expression property value: C is the string set.

DELETE Color :c

An example of the updateitem is as follows:

{
    TableName: "Music",
    Key: {
        "Artist":"No One You Know",
        "SongTitle":"Call Me Today"
    },
    UpdateExpression: "SET RecordLabel = :label",
    ExpressionAttributeValues: {
        ":label": "Global Records"
    }
}
  • The updateitem must specify the key property of the item to be modified and an updateexpression to specify the value of the property.

  • Updateitem replaces the entire item instead of replacing a single property.

  • The behavior of the updateitem is similar to that of the “upsert” operation: if the item is in a table, the item is updated; otherwise, a new item is added (inserted).

  • Updateitem can only modify a single item. If you want to modify multiple items, you must use multiple updateitem operations.

  • Updateitem supports conditional writing, in which case the operation completes successfully only if the evaluation of a specific conditionexpression is true. For example, the following updateitem actions do not perform updates unless the price of the song is greater than or equal to 2.00:

Conditional writing

To perform a conditional update, use the update expression and the conditional expression to perform the updateitem operation. To continue the operation, the conditional expression must evaluate to true; otherwise, the operation will fail.

Suppose you want to increase the price of an item by a certain amount, such as AMT, but only if the result does not exceed the maximum price. To do this, you can calculate the maximum price that is currently allowed to raise the price, and then subtract the increased amount from the maximum price: AMT. Define the result as: limit, and then use the following conditional expression:

Conditional expression: price < =: limit)
Update expression: set price = price +: AMT
Now suppose you want to set the front view picture for the project, but only if the project does not have any pictures and you do not want to overlay any existing elements. You can use the following expression to perform the operation:

Update expression: Set Pictures.FrontView = :myUR
(suppose: myurl is the location of the project image, for example http://example.com/picture.jpg 。)
Conditional expression: attribute_ not_ exists( Pictures.FrontView )

{
    TableName: "Music",
    Key: {
        "Artist":"No One You Know",
        "SongTitle":"Call Me Today"
    },
    UpdateExpression: "SET RecordLabel = :label",
    ConditionExpression: "Price >= :p",
    ExpressionAttributeValues: {
        ":label": "Global Records",
        ":p": 2.00
    }
}
  • Updateitem also supports atomic counters or properties of type number, which can be incremented or decremented. Atomic counters are similar in many ways to sequential generators, identity columns, or self incrementing fields in SQL databases.

The following is an example of an updateitem operation that initializes a new property (plays) to track the number of times a song has been played:

{
    TableName: "Music",
    Key: {
        "Artist":"No One You Know",
        "SongTitle":"Call Me Today"
    },
    UpdateExpression: "SET Plays = :val",
    ExpressionAttributeValues: {
        ":val": 0
    },
    ReturnValues: "UPDATED_NEW"
}

The returnvalues parameter is set to updated_ New, which returns the new value of any property that has been updated. In this example, it returns 0 (zero).

When someone plays this song, you can use the following updateitem action to increase plays by 1:

{
    TableName: "Music",
    Key: {
        "Artist":"No One You Know",
        "SongTitle":"Call Me Today"
    },
    UpdateExpression: "SET Plays = Plays + :incr",
    ExpressionAttributeValues: {
        ":incr": 1
    },
    ReturnValues: "UPDATED_NEW"
}

To sum up

  • Updateitem can only update one item at a time

  • Updateitem updates the entire project instead of modifying the value of the feature only

  • Updateitem supports conditional writing

In this section, we introduce the update operation of dynamodb project, and in the next section, we will introduce the deletion operation of the project..

Link to original text

Recommended Today

Graphic SQL, this is too image!

Author Tony, who doesn’t cut her hairSource https://blog.csdn.net/horses/… This paper introduces the design idea of relational database: everything is relational in SQL. There are many great design concepts and ideas in the computer field, such as: In UNIX, everything is a file. In object-oriented programming languages, everything is an object. The same is true of […]