Modifying Map Values

Removing Elements from a Map
Adding Elements to a Map
Updating Existing Map Elements

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 (see Time Functions):

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.