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