Modifying Array Values

Adding Elements to an Array
Changing an Existing Element in an Array
Removing Elements from Arrays

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->