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 *