Previous Next JavaScript must be enabled to correctly display this content

## Using SQL command

While updating JSON data, in addition to `WHERE`, `SET` and `RETURNING` clause, the following clauses can be used..

- The `ADD` clause is used to add new elements into one or more arrays. It consists of a target expression, which should normally return one or more array items, an optional position expression, which specifies the position within each array where the new elements should be placed, and a new-elements expression that returns the new elements to insert.
- The `PUT` clause is used primarily to add new fields to a JSON document. It consists of a target expression, which should normally return one or more fields to be inserted into the target JSON document.
- The `REMOVE` clause consists of a single target expression, which computes the items to be removed.

Example 1: Update table and add data in a JSON object

Add elements to the action array (at a given array element) for a particular flight Leg of a passenger. By default, the element is added at the end. If a number is specified, it is inserted in that position. In the example below, you want the new element to be added in the 2nd position.

    UPDATE BaggageInfo bag
    ADD bag.bagInfo[0].flightLegs[0].actions 2  {"actionAt" : "LAX",
             "actionCode" : "WAITING at LAX",
             "actionTime" : "2019-02-01T06:13:00Z"}
    WHERE ticketNo=1762344493810
    RETURNING *

Example 2: Update table and update data from a JSON object.

You could update the data from a JSON object using the SET clause. Here the second element of the actions array is updated with new values for a given ticket number.

    UPDATE BaggageInfo bag
    SET bag.bagInfo[0].flightLegs[0].actions[2]=
    {"actionAt" : "LAX",
    "actionCode" : "STILL WAITING at LAX",
    "actionTime" : "2019-02-01T06:15:00Z"}
    WHERE ticketNo=1762344493810 RETURNING *

Example 3: Update table and remove data in a JSON object.

You can use the `REMOVE` clause to remove a given element from an array. You need to specify which element of the array needs to be removed using the index of the array.

    UPDATE BaggageInfo bag
    REMOVE bag.bagInfo[0].flightLegs[0].actions[1]
    WHERE ticketNo=1762344493810
    RETURNING *

Example 4: Update stream_acct table and add and remove data in a JSON object.

In the `stream_acct` table, for a customer you can add the details of a particular series episode of a show using the ADD clause in the UPDATE statement.

    UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
         "date" : "2022-04-26",
         "episodeID" : 43,
         "episodeName" : "Season 2 episode 2",
         "lengthMin" : 45,
         "minWatched" : 45} WHERE acct_Id=2 RETURNING *

Similarly , you can remove the details of a particular series episode of a show using the REMOVE clause in the UPDATE statement.

    UPDATE stream_acct acct1
    REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1]
    WHERE acct_Id=2 RETURNING *