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 *