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