Previous Next JavaScript must be enabled to correctly display this content

## Modifying Table Rows using UPDATE Statements

This topic provides examples of how to update table rows using SQL for Oracle NoSQL Database UPDATE statements. These are an efficient way to update table row data, because UPDATE statements make server-side updates directly, without requiring a Read/Modify/Write update cycle.

Note:

You can use UPDATE statements to update only an existing row. You cannot use UPDATE to either create new rows, or delete existing rows. An UPDATE statement can modify only a single row at a time.

### Example Data

This chapter's examples uses the data loaded by the `SQLJSONExamples` script, which can be found in the `Examples` download package. For details on using this script, the sample data it loads, and the `Examples` download, see See SQLJSONExamples Script.

### Changing Field Values

In the simplest case, you can change the value of a field using the Update Statement SET clause. The JSON example data set has a row which contains just an array and an integer. This is row ID 6:

    sql-> mode column
    Query output mode is COLUMN
    sql-> SELECT * from JSONPersons j WHERE j.id = 6;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    | mynumber | 5      |
     +----+-------------------+

    1 row returned

You can change the value of `mynumber` in that row using the following statement:

    sql-> UPDATE JSONPersons j
              SET j.person.mynumber = 100
              WHERE j.id = 6;
     +----------+
     | Column_1 |
     +----------+
     |        1 |
     +----------+

    1 row returned
    sql-> SELECT * from JSONPersons j WHERE j.id = 6;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    | mynumber | 100    |
     +----+-------------------+

    1 row returned

In the previous example, the results returned by the Update statement was not very informative, so we were required to reissue the Select statement in order to view the results of the update. You can avoid that by using a RETURNING clause. This functions exactly like a Select statement:

    sql-> UPDATE JSONPersons j
              SET j.person.mynumber = 200
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    | mynumber | 200    |
     +----+-------------------+

    1 row returned
    sql->

You can further limit and customize the displayed results in the same way that you can do so using a SELECT statement:

    sql-> UPDATE JSONPersons j
              SET j.person.mynumber = 300
              WHERE j.id = 6
              RETURNING id, j.person.mynumber AS MyNumber;
     +----+---------------------+
     | id |      MyNumber       |
     +----+---------------------+
     |  6 | 300                 |
     +----+---------------------+

    1 row returned
    sql->

It is normally possible to update the value of a non-JSON field using the SET clause. However, you cannot change a field if it is a primary key. For example:

    sql-> UPDATE JSONPersons j
              SET j.id = 1000
              WHERE j.id = 6
              RETURNING *;
    Error handling command UPDATE JSONPersons j
    SET j.id = 1000
    WHERE j.id = 6
    RETURNING *: Error: at (2, 4) Cannot update a primary key column
    Usage:

    Unknown statement

    sql->

### Modifying Array Values

You use the Update statement ADD clause to add elements into an array. You use a SET clause to change the value of an existing array element. And you use a REMOVE clause to remove elements from an array.

#### Adding Elements to an Array

The ADD clause requires you to identify the array position that you want to operate on, followed by the value you want to set to that position in the array. If the index value that you set is `0` or a negative number, the value that you specify is inserted at the beginning of the array.

If you do not provide an index position, the array value that you specify is appended to the end of the array.

    sql-> SELECT * from JSONPersons j WHERE j.id = 6;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql-> UPDATE JSONPersons j
              ADD j.person.myarray 0 50,
              ADD j.person.myarray 100
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

Notice that multiple ADD clauses are used in the query above.

Array values get appended to the end of the array, even if you provide an array position that is larger than the size of the array. You can either provide an arbitrarily large number, or make use of the `size()` function:

    sql-> UPDATE JSONPersons j
              ADD j.person.myarray (size(j.person.myarray) + 1) 400
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    |            400    |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

You can append values to the array using the built-in `seq_concat()` function:

    sql-> UPDATE JSONPersons j
              ADD j.person.myarray seq_concat(66, 77, 88)
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    |            400    |
     |    |            66     |
     |    |            77     |
     |    |            88     |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

If you provide an array position that is between `0` and the array's size, then the value you specify will be inserted into the array before the specified position. To determine the correct position, start counting from 0:

    UPDATE JSONPersons j
        ADD j.person.myarray 3 250
        WHERE j.id = 6
        RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            250    |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    |            400    |
     |    |            66     |
     |    |            77     |
     |    |            88     |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

#### Changing an Existing Element in an Array

To change an existing value in an array, use the SET clause and identify the value's position using `[]`. To determine the value's position, start counting from `0`:

    sql-> UPDATE JSONPersons j
              SET j.person.myarray[3] = 1000
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            1000   |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    |            400    |
     |    |            66     |
     |    |            77     |
     |    |            88     |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

#### Removing Elements from Arrays

To remove an existing element from an array, use the REMOVE clause. To do this, you must identify the position of the element in the array that you want to remove. To determine the value's position, start counting from `0`:

    sql-> UPDATE JSONPersons j
              REMOVE j.person.myarray[3]
              WHERE j.id = 6
              RETURNING *;
     +----+-------------------+
     | id |      person       |
     +----+-------------------+
     |  6 | myarray           |
     |    |            50     |
     |    |            1      |
     |    |            2      |
     |    |            3      |
     |    |            4      |
     |    |            100    |
     |    |            400    |
     |    |            66     |
     |    |            77     |
     |    |            88     |
     |    | mynumber | 300    |
     +----+-------------------+

    1 row returned
    sql->

It is possible for the array position to be identified by an expression. For example, in our sample data, some records include an array of phone numbers, and some of those phone numbers include a work number:

    sql-> SELECT * FROM JSONPersons j WHERE j.id = 3;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  3 | address                                     |
     |    |     city         | Middleburg               |
     |    |     phones                                  |
     |    |         areacode | 305                      |
     |    |         number   | 1234079                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 305                      |
     |    |         number   | 2066401                  |
     |    |         type     | home                     |
     |    |     state        | FL                       |
     |    |     street       | 187 Aspen Drive          |
     |    | age              | 38                       |
     |    | connections                                 |
     |    |                    1                        |
     |    |                    4                        |
     |    |                    2                        |
     |    | expenses                                    |
     |    |     food         | 2000                     |
     |    |     gas          | 10                       |
     |    |     travel       | 700                      |
     |    | firstname        | John                     |
     |    | income           | 100000000                |
     |    | lastLogin        | 2016-11-29T08:21:35.4971 |
     |    | lastname         | Morgan                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

We can remove the work number from the array in one of two ways. First, we can directly specify its position in the array (position 0), but that only removes a single element at a time. If we want to remove all the work numbers, we can do it by using the $element variable. To illustrate, we first add another work number to the array:

    sql-> UPDATE JSONPersons j
              ADD j.person.address.phones 0
              {"type":"work", "areacode":415, "number":9998877}
              WHERE j.id = 3
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  3 | address                                     |
     |    |     city         | Middleburg               |
     |    |     phones                                  |
     |    |         areacode | 415                      |
     |    |         number   | 9998877                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 305                      |
     |    |         number   | 1234079                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 305                      |
     |    |         number   | 2066401                  |
     |    |         type     | home                     |
     |    |     state        | FL                       |
     |    |     street       | 187 Aspen Drive          |
     |    | age              | 38                       |
     |    | connections                                 |
     |    |                    1                        |
     |    |                    4                        |
     |    |                    2                        |
     |    | expenses                                    |
     |    |     food         | 2000                     |
     |    |     gas          | 10                       |
     |    |     travel       | 700                      |
     |    | firstname        | John                     |
     |    | income           | 100000000                |
     |    | lastLogin        | 2016-11-29T08:21:35.4971 |
     |    | lastname         | Morgan                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

Now we can remove all the work numbers as follows:

    sql-> UPDATE JSONPersons j
              REMOVE j.person.address.phones[$element.type = "work"]
              WHERE j.id = 3
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  3 | address                                     |
     |    |     city         | Middleburg               |
     |    |     phones                                  |
     |    |         areacode | 305                      |
     |    |         number   | 2066401                  |
     |    |         type     | home                     |
     |    |     state        | FL                       |
     |    |     street       | 187 Aspen Drive          |
     |    | age              | 38                       |
     |    | connections                                 |
     |    |                    1                        |
     |    |                    4                        |
     |    |                    2                        |
     |    | expenses                                    |
     |    |     food         | 2000                     |
     |    |     gas          | 10                       |
     |    |     travel       | 700                      |
     |    | firstname        | John                     |
     |    | income           | 100000000                |
     |    | lastLogin        | 2016-11-29T08:21:35.4971 |
     |    | lastname         | Morgan                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

### Modifying Map Values

To write a new field to a map, use the PUT clause. You can also use the PUT clause to change an existing map value. To remove a map field, use the REMOVE clause.

For example, consider the following two rows from our sample data:

    sql-> SELECT * FROM JSONPersons j WHERE j.id = 6 OR j.id = 3;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  3 | address                                     |
     |    |     city         | Middleburg               |
     |    |     phones                                  |
     |    |         areacode | 305                      |
     |    |         number   | 2066401                  |
     |    |         type     | home                     |
     |    |     state        | FL                       |
     |    |     street       | 187 Aspen Drive          |
     |    | age              | 38                       |
     |    | connections                                 |
     |    |                    1                        |
     |    |                    4                        |
     |    |                    2                        |
     |    | expenses                                    |
     |    |     food         | 2000                     |
     |    |     gas          | 10                       |
     |    |     travel       | 700                      |
     |    | firstname        | John                     |
     |    | income           | 100000000                |
     |    | lastLogin        | 2016-11-29T08:21:35.4971 |
     |    | lastname         | Morgan                   |
     +----+---------------------------------------------+
     |  6 | myarray                                     |
     |    |                    50                       |
     |    |                    1                        |
     |    |                    2                        |
     |    |                    3                        |
     |    |                    4                        |
     |    |                    100                      |
     |    |                    400                      |
     |    |                    66                       |
     |    |                    77                       |
     |    |                    88                       |
     |    | mynumber         | 300                      |
     +----+---------------------------------------------+

     2 rows returned
     sql->

These two rows look nothing alike. Row 3 contains information about a person, while row 6 contains, essentially, random data. This is possible because the `person` column is of type JSON, which is not strongly typed. But because we interact with JSON columns as if they are maps, we can fix row 6 by modifying it as a map.

#### Removing Elements from a Map

To begin, we remove the two existing elements from row six (`myarray` and `mynumber`). We do this with a single UPDATE statement, which allows us to execute multiple update clauses so long as they are comma-separated:

    sql-> UPDATE JSONPersons j
              REMOVE j.person.myarray,
              REMOVE j.person.mynumber
              WHERE j.id = 6
              RETURNING *;
     +----+-----------------+
     | id |     person      |
     +----+-----------------+
     |  6 |                 |
     +----+-----------------+

    1 row returned
    sql->

#### Adding Elements to a Map

Next, we add person data to this table row. We could do this with a single UPDATE statement by specifying the entire map with a single PUT clause, but for illustration purposes we do this in multiple steps.

To begin, we specify the person's name. Here, we use a single PUT clause that specifies a map with multiple elements:

    sql-> UPDATE JSONPersons j
              PUT j.person {"firstname" : "Wendy",
                            "lastname" : "Purvis"}
              WHERE j.id = 6
              RETURNING *;
     +----+--------------------+
     | id |       person       |
     +----+--------------------+
     |  6 | firstname | Wendy  |
     |    | lastname  | Purvis |
     +----+--------------------+

    1 row returned
    sql->

Next, we specify the age, connections, expenses, income, and lastLogin fields using multiple PUT clauses on a single UPDATE statement:

    sql-> UPDATE JSONPersons j
              PUT j.person {"age" : 43},
              PUT j.person {"connections" : [2,3]},
              PUT j.person {"expenses" : {"food" : 1100,
                                          "books" : 210,
                                          "travel" : 50}},
              PUT j.person {"income" : 80000},
              PUT j.person {"lastLogin" : "2017-06-29T16:12:35.0285"}
              WHERE j.id = 6
              RETURNING *;
     +----+----------------------------------------+
     | id |                 person                 |
     +----+----------------------------------------+
     |  6 | age         | 43                       |
     |    | connections                            |
     |    |               2                        |
     |    |               3                        |
     |    | expenses                               |
     |    |     books   | 210                      |
     |    |     food    | 1100                     |
     |    |     travel  | 50                       |
     |    | firstname   | Wendy                    |
     |    | income      | 80000                    |
     |    | lastLogin   | 2017-06-29T16:12:35.0285 |
     |    | lastname    | Purvis                   |
     +----+----------------------------------------+

    1 row returned
    sql->

We still need an address. Again, we could do this with a single PUT clause, but for illustration purposes we will use multiple clauses. Our first PUT creates the `address` element, which uses a map as a value. Our second PUT adds elements to the `address` map:

    sql-> UPDATE JSONPersons j
            PUT j.person {"address" : {"street" : "479 South Way Dr"}},
            PUT j.person.address {"city" : "St. Petersburg",
                                  "state" : "FL"}
            WHERE j.id = 6
            RETURNING *;
     +----+----------------------------------------+
     | id |                 person                 |
     +----+----------------------------------------+
     |  6 | address                                |
     |    |     city    | St. Petersburg           |
     |    |     state   | FL                       |
     |    |     street  | 479 South Way Dr         |
     |    | age         | 43                       |
     |    | connections                            |
     |    |               2                        |
     |    |               3                        |
     |    | expenses                               |
     |    |     books   | 210                      |
     |    |     food    | 1100                     |
     |    |     travel  | 50                       |
     |    | firstname   | Wendy                    |
     |    | income      | 80000                    |
     |    | lastLogin   | 2017-06-29T16:12:35.0285 |
     |    | lastname    | Purvis                   |
     +----+----------------------------------------+

    1 row returned
    sql->

Finally, we provide phone numbers for this person. These are specified as an array of maps:

    sql-> UPDATE JSONPersons j
              PUT j.person.address {"phones" :
                     [{"type":"work", "areacode":727, "number":8284321},
                      {"type":"home", "areacode":727, "number":5710076},
                      {"type":"mobile", "areacode":727, "number":8913080}
                     ]
                                    }
              WHERE j.id = 6
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  6 | address                                     |
     |    |     city         | St. Petersburg           |
     |    |     phones                                  |
     |    |         areacode | 727                      |
     |    |         number   | 8284321                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 5710076                  |
     |    |         type     | home                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 8913080                  |
     |    |         type     | mobile                   |
     |    |     state        | FL                       |
     |    |     street       | 479 South Way Dr         |
     |    | age              | 43                       |
     |    | connections                                 |
     |    |                    2                        |
     |    |                    3                        |
     |    | expenses                                    |
     |    |     books        | 210                      |
     |    |     food         | 1100                     |
     |    |     travel       | 50                       |
     |    | firstname        | Wendy                    |
     |    | income           | 80000                    |
     |    | lastLogin        | 2017-06-29T16:12:35.0285 |
     |    | lastname         | Purvis                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

#### Updating Existing Map Elements

To update an existing element in a map, you can use the PUT clause in exactly the same way as you add a new element to map. For example, to update the lastLogin time:

    sql-> UPDATE JSONPersons j
              PUT j.person {"lastLogin" : "2017-06-29T20:36:04.9661"}
              WHERE j.id = 6
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  6 | address                                     |
     |    |     city         | St. Petersburg           |
     |    |     phones                                  |
     |    |         areacode | 727                      |
     |    |         number   | 8284321                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 5710076                  |
     |    |         type     | home                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 8913080                  |
     |    |         type     | mobile                   |
     |    |     state        | FL                       |
     |    |     street       | 479 South Way Dr         |
     |    | age              | 43                       |
     |    | connections                                 |
     |    |                    2                        |
     |    |                    3                        |
     |    | expenses                                    |
     |    |     books        | 210                      |
     |    |     food         | 1100                     |
     |    |     travel       | 50                       |
     |    | firstname        | Wendy                    |
     |    | income           | 80000                    |
     |    | lastLogin        | 2017-06-29T20:36:04.9661 |
     |    | lastname         | Purvis                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

Alternatively, use a SET clause:

    sql-> UPDATE JSONPersons j
              SET j.person.lastLogin = "2017-06-29T20:38:56.2751"
              WHERE j.id = 6
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  6 | address                                     |
     |    |     city         | St. Petersburg           |
     |    |     phones                                  |
     |    |         areacode | 727                      |
     |    |         number   | 8284321                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 5710076                  |
     |    |         type     | home                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 8913080                  |
     |    |         type     | mobile                   |
     |    |     state        | FL                       |
     |    |     street       | 479 South Way Dr         |
     |    | age              | 43                       |
     |    | connections                                 |
     |    |                    2                        |
     |    |                    3                        |
     |    | expenses                                    |
     |    |     books        | 210                      |
     |    |     food         | 1100                     |
     |    |     travel       | 50                       |
     |    | firstname        | Wendy                    |
     |    | income           | 80000                    |
     |    | lastLogin        | 2017-06-29T20:38:56.2751 |
     |    | lastname         | Purvis                   |
     +----+---------------------------------------------+

    1 row returned
    sql->

If you want to set the timestamp to the current time, use the `current_time()` built-in function.

    sql-> UPDATE JSONPersons j
              SET j.person.lastLogin = cast(current_time() AS String)
              WHERE j.id = 6
              RETURNING *;
     +----+--------------------------------------------+
     | id |                   person                   |
     +----+--------------------------------------------+
     |  6 | address                                    |
     |    |     city         | St. Petersburg          |
     |    |     phones                                 |
     |    |         areacode | 727                     |
     |    |         number   | 8284321                 |
     |    |         type     | work                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 5710076                 |
     |    |         type     | home                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 8913080                 |
     |    |         type     | mobile                  |
     |    |     state        | FL                      |
     |    |     street       | 479 South Way Dr        |
     |    | age              | 43                      |
     |    | connections                                |
     |    |                    2                       |
     |    |                    3                       |
     |    | expenses                                   |
     |    |     books        | 210                     |
     |    |     food         | 1100                    |
     |    |     travel       | 50                      |
     |    | firstname        | Wendy                   |
     |    | income           | 80000                   |
     |    | lastLogin        | 2017-06-29T04:40:15.917 |
     |    | lastname         | Purvis                  |
     +----+--------------------------------------------+

    1 row returned
    sql->

If an element in the map is an array, you can modify it in the same way as you would any array. For example:

    sql-> UPDATE JSONPersons j
              ADD j.person.connections seq_concat(1, 4)
              WHERE j.id = 6
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  6 | address                                     |
     |    |     city         | St. Petersburg           |
     |    |     phones                                  |
     |    |         areacode | 727                      |
     |    |         number   | 8284321                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 5710076                  |
     |    |         type     | home                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 8913080                  |
     |    |         type     | mobile                   |
     |    |     state        | FL                       |
     |    |     street       | 479 South Way Dr         |
     |    | age              | 43                       |
     |    | connections                                 |
     |    |                    2                        |
     |    |                    3                        |
     |    |                    1                        |
     |    |                    4                        |
     |    | expenses                                    |
     |    |     books        | 210                      |
     |    |     food         | 1100                     |
     |    |     travel       | 50                       |
     |    | firstname        | Wendy                    |
     |    | income           | 80000                    |
     |    | lastLogin        | 2017-06-29T04:40:15.917  |
     |    | lastname         | Purvis                   |
     +----+---------------------------------------------+

    1 row returned

If you are unsure of an element being an array or a map, you can use both ADD and PUT within the same UPDATE statement. For example:

    sql-> UPDATE JSONPersons j
              ADD j.person.connections seq_concat(5, 7),
              PUT j.person.connections seq_concat(5, 7)
              WHERE j.id = 6
              RETURNING *;
     +----+---------------------------------------------+
     | id |                   person                    |
     +----+---------------------------------------------+
     |  6 | address                                     |
     |    |     city         | St. Petersburg           |
     |    |     phones                                  |
     |    |         areacode | 727                      |
     |    |         number   | 8284321                  |
     |    |         type     | work                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 5710076                  |
     |    |         type     | home                     |
     |    |                                             |
     |    |         areacode | 727                      |
     |    |         number   | 8913080                  |
     |    |         type     | mobile                   |
     |    |     state        | FL                       |
     |    |     street       | 479 South Way Dr         |
     |    | age              | 43                       |
     |    | connections                                 |
     |    |                    2                        |
     |    |                    3                        |
     |    |                    1                        |
     |    |                    4                        |
     |    |                    5                        |
     |    |                    7                        |
     |    | expenses                                    |
     |    |     books        | 210                      |
     |    |     food         | 1100                     |
     |    |     travel       | 50                       |
     |    | firstname        | Wendy                    |
     |    | income           | 80000                    |
     |    | lastLogin        | 2017-06-29T04:40:15.917  |
     |    | lastname         | Purvis                   |
     +----+---------------------------------------------+

    1 row returned

If the element is an array, the ADD gets applied and the PUT is a noop. If it is a map, then the PUT gets applied and ADD is a noop. In this example, since the element is an array, the ADD gets applied.

### Managing Time to Live Values

Time to Live (TTL) values indicate how long data can exist in a table before it expires. Expired data can no longer be returned as part of a query.

Default TTL values can be set on either a table-level or a row level when the table is first defined. Using UPDATE statements, you can change the TTL value for a single row.

You can see a row's TTL value using the `remaining_hours()`, `remaining_days()` or `expiration_time()` built-in functions. These TTL functions require a row as input. We accomplish this by using the $ as part of the table alias. This causes the table alias to function as a row variable.

    sql-> SELECT remaining_days($j) AS Expires
        FROM JSONPersons $j WHERE id = 6;
     +---------+
     | Expires |
     +---------+
     |      -1 |
     +---------+

    1 row returned
    sql->

The previous query returns `-1`. This means that the row has no expiration time. We can specify an expiration time for the row by using an UPDATE statement with a `set TTL` clause. This clause computes a new TTL by specifying an offset from the current expiration time. If the row never expires, then the current expiration time is `1970-01-01T00:00:00.000`. The value you provide to `set TTL` must specify units of either `HOURS` or `DAYS`.

    sql-> UPDATE JSONPersons $j
              SET TTL 1 DAYS
              WHERE id = 6
              RETURNING remaining_days($j) AS Expires;
     +---------+
     | Expires |
     +---------+
     |       1 |
     +---------+

    1 row returned
    sql->

To see the new expiration time, we can use the built-in `expiration_time()` function. Because we specified an expiration time based on a day boundary, the row expires at midnight of the following day (expiration rounds up):

    sql-> SELECT current_time() AS Now,
        expiration_time($j) AS Expires
        FROM JSONPersons $j WHERE id = 6;
     +-------------------------+-------------------------+
     |           Now           |         Expires         |
     +-------------------------+-------------------------+
     | 2017-07-03T21:56:47.778 | 2017-07-05T00:00:00.000 |
     +-------------------------+-------------------------+

    1 row returned
    sql->

To turn off the TTL so that the row will never expire, specify a negative value, using either `HOURS` or `DAYS` as the unit:

    sql-> UPDATE JSONPersons $j
              SET TTL -1 DAYS
              WHERE id = 6
              RETURNING remaining_days($j) AS Expires;
     +---------+
     | Expires |
     +---------+
     |       0 |
     +---------+

    1 row returned
    sql->

Notice that the RETURNING clause provides a value of 0 days. This indicates that the row will never expire. Further, if we look at the remaining_days() using a SELECT statement, we will once again see a negative value, indicating that the row never expires:

    sql-> SELECT remaining_days($j) AS Expires
        FROM JSONPersons $j WHERE id = 6;
     +---------+
     | Expires |
     +---------+
     |      -1 |
     +---------+

    1 row returned
    sql->

### Avoiding the Read-Modify-Write Cycle

An important aspect of UPDATE Statements is that you do not have to read a value in order to update it. Instead, you can blindly modify a value directly in the store without ever retrieving (reading) it. To do this, you refer to the value you want to modify using the `$` variable.

For example, we have a row in JSONPersons that looks like this:

    sql-> SELECT * FROM JSONPersons WHERE id=6;
     +----+--------------------------------------------+
     | id |                   person                   |
     +----+--------------------------------------------+
     |  6 | address                                    |
     |    |     city         | St. Petersburg          |
     |    |     phones                                 |
     |    |         areacode | 727                     |
     |    |         number   | 8284321                 |
     |    |         type     | work                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 5710076                 |
     |    |         type     | home                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 8913080                 |
     |    |         type     | mobile                  |
     |    |     state        | FL                      |
     |    |     street       | 479 South Way Dr        |
     |    | age              | 43                      |
     |    | connections                                |
     |    |                    2                       |
     |    |                    3                       |
     |    |                    1                       |
     |    |                    4                       |
     |    | expenses                                   |
     |    |     books        | 210                     |
     |    |     food         | 1100                    |
     |    |     travel       | 50                      |
     |    | firstname        | Wendy                   |
     |    | income           | 80000                   |
     |    | lastLogin        | 2017-07-25T22:50:06.482 |
     |    | lastname         | Purvis                  |
     +----+--------------------------------------------+

    1 row returned

We can blindly update the value of the `person.expenses.books` field by referencing `$`. In the following statement, no read is performed on the store. Instead, the write operation is performed directly at the store.

    sql-> UPDATE JSONPersons j
       ->     SET j.person.expenses.books = $ + 100
       ->     WHERE id = 6;
     +----------------+
     | NumRowsUpdated |
     +----------------+
     |              1 |
     +----------------+

    1 row returned

To see that the books expenses value has indeed been incremented by 100, we perform a second `SELECT` statement.

    sql-> SELECT * FROM JSONPersons WHERE id=6;
     +----+--------------------------------------------+
     | id |                   person                   |
     +----+--------------------------------------------+
     |  6 | address                                    |
     |    |     city         | St. Petersburg          |
     |    |     phones                                 |
     |    |         areacode | 727                     |
     |    |         number   | 8284321                 |
     |    |         type     | work                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 5710076                 |
     |    |         type     | home                    |
     |    |                                            |
     |    |         areacode | 727                     |
     |    |         number   | 8913080                 |
     |    |         type     | mobile                  |
     |    |     state        | FL                      |
     |    |     street       | 479 South Way Dr        |
     |    | age              | 43                      |
     |    | connections                                |
     |    |                    2                       |
     |    |                    3                       |
     |    |                    1                       |
     |    |                    4                       |
     |    | expenses                                   |
     |    |     books        | 310                     |
     |    |     food         | 1100                    |
     |    |     travel       | 50                      |
     |    | firstname        | Wendy                   |
     |    | income           | 80000                   |
     |    | lastLogin        | 2017-07-25T22:50:06.482 |
     |    | lastname         | Purvis                  |
     +----+--------------------------------------------+

    1 row returned