Example: Updating JSON collection tables

You can update data in the JSON collection tables using the UPDATE statement. The UPDATE operation works in the same way as fixed schema tables.

Consider a row from a JSON collection table created for a shopping application.

{"contactPhone":"1617114988","address":{"Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"A4 sheets","priceperunit":500,"quantity":2},{"item":"Mobile Holder","priceperunit":700,"quantity":1}],"email":"[e-mail]","firstName":"Lorenzo","lastName":"Phil","notify":"yes","orders":[{"EstDelivery":"2023-11-15","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}

Example 7-16 Correct a few inadvertent errors in the shopper's data

Use Update clauses to correct a shopper's data as follows:

UPDATE storeAcct s
SET s.notify = "no",
REMOVE s.cart [$element.item = "A4 sheets"],
PUT s.address {"Block" : "C"},
SET s.orders[0].EstDelivery =  "2023-11-17",
ADD s.cart 1 {"item":"A3 sheets", "priceperunit":600, "quantity":2}
WHERE s.contactPhone = "1617114988"

Explanation: In the above example, you update the shopper's record in the storeAcct table to correct a few inadvertent errors. This correction requires updates to various fields of the storeAcct table. The SET clause deactivates the notification setting in the shopper's data record. The REMOVE clause checks if any item field in the cart matches A4 sheets and deletes the corresponding element from the orders array. The PUT clause adds a new JSON field to indicate the landmark for delivery. The second SET clause accesses the deeply nested EstDelivery field and updates the estimated delivery date for the first item in the orders array. The ADD clause inserts a new element into the cart field to shortlist an additional item.

When you fetch the updated shopper's data, you get the following output:

{"contactPhone":"1617114988","address":{"Block":"C","Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"Mobile Holder","priceperunit":700,"quantity":1},{"item":"A3 sheets","priceperunit":600,"quantity":2}],"email":"[e-mail]","firstName":"Lorenzo","lastName":"Phil","notify":"no","orders":[{"EstDelivery":"2023-11-17","item":"AG Novels 1","priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","priceperunit":950,"status":"Transit"}]}