Example: Updating Rows

Let’s assume a table, called People, with only two columns: an integer id column and an info column of type JSON. Furthermore, let’s assume the following row to be updated:

CREATE TABLE People (
    id INTEGER,
    info JSON,
PRIMARY KEY(id))

INSERT INTO People VALUES (
    0,
    {
        "firstName":"John",
        "lastName":"Doe",
        "profession":"software engineer",
        "income":200000,
        "address": {
            "city" : "San Fransisco",
            "state" : "CA",
            "phones" : [ 
                { "areacode":415, "number":2840060, "kind":"office" },
                { "areacode":650, "number":3789021, "kind":"mobile" },
                { "areacode":415, "number":6096010, "kind":"home" }
            ]
        },
        "children": {
            "Anna" : { 
                "age" : 10,
                "school" : "school_1",
                "friends" : ["Anna", "John", "Maria"]
            },
            "Ron" : { "age" : 2 },
            "Mary" : { 
                "age" : 7,
                "school" : "school_3",
                "friends" : ["Anna", "Mark"] 
            }
        }
    }
)

Example 7-31 Use UPDATE statement to update various fields in the table

The following update statement updates various fields in the above row:

UPDATE People p
    SET p.info.profession = "surfing instructor",
    SET p.info.address.city = "Santa Cruz",
    SET p.info.income = p.info.income / 10,
    SET p.info.children.values().age = $ + 1,
    ADD p.info.address.phones
      0 { "areacode":831, "number":5294368, "kind":"mobile" },
    REMOVE p.info.address.phones [$element.kind = "office"],
    PUT p.info.children.Ron { "friends" : ["Julie"] },
    ADD p.info.children.values().friends seq_concat("Ada", "Aris")
WHERE id = 0
RETURNING *

After the update, the row looks like this:

{
    "id":0,
    "info":{
        "firstName":"John",
        "lastName":"Doe",
        "profession":"surfing instructor",
        "income":20000,
        "address":{
            "city":"Santa Cruz",
            "phones":[
                {"areacode":831,"kind":"mobile","number":5294368},
                {"areacode":650,"kind":"mobile","number":3789021},
                {"areacode":415,"kind":"home","number":6096010}
            ],
            "state":"CA"
        },
        "children":{
            "Anna":{
                "age":11,
                "friends":["Anna","John","Maria","Ada","Aris"],
                "school":"school_1"
            },
            "Ron":{
                "age":3,
                "friends":["Julie","Ada","Aris"]
            },
            "Mary":{
                "age":8,
                "friends":["Anna","Mark","Ada","Aris"],
                "school":"school_3"
            }
        }
    }
}

The first two SET clauses change the profession and city of John Doe. The third SET reduces his income to one-tenth. The fourth SET increases the age of his children by 1. Notice the use of the $ variable here: the expression p.info.children.values().age returns 3 ages; The SET will iterate over these ages, bind the $ variable to each age in turn, compute the expression $ + 1 for each age, and update the age with the new value. Notice that the income update can also have used a $ variable: set p.info.income = $ / 10. This would have saved the re-evaluation of the p.info.income path on the right-hand side or the "=".

The ADD clause adds a new phone at position 0 inside the phones array. The REMOVE removes all the office phones (only one in this example). The PUT clause adds a friend for Ron. In this clause, the expression p.info.children.Ron returns the value associated with the Ron child. This value is a map (the json object { "age" : 3 }) and becomes the target of the update. The 2nd expression in the PUT ({ "friends" : ["Julie"] }) constructs and returns a new map. The fields of this map are added to the target map. Finally, the last ADD clause adds the same two new friends to each child. See seq_concat function function.

Notice that the update query in this example would have been exactly the same if instead of type JSON, the info column had the following RECORD type:

RECORD(
    firstName STRING,
    lastName STRING,
    profession STRING,
    income INTEGER,
    address RECORD(
        city STRING,
        state STRING,
        phones ARRAY(
            RECORD(
                areacode INTEGER,
                number INTEGER,
                kind STRING
            )
        )
    ),
    children MAP(
        RECORD(
            age INTEGER,
            school STRING,
            friends ARRAY(STRING)
        )
    )
)

Example: Updating multiple rows

Create a table named EmployeeInfo with an integer empID column as the primary key, a string department column as the shard key, a string fullName column, and a JSON type info column.

CREATE TABLE EmployeeInfo (
    empID INTEGER,
    department STRING,
    fullName STRING,
    info JSON,
    PRIMARY KEY(SHARD(department), empID))

Next, add the following rows:

INSERT INTO EmployeeInfo VALUES (101, "HR", "Liam Phillips", {"salary":100000,"address":{"city":"Toronto","country":"Canada"}})
 
INSERT INTO EmployeeInfo VALUES (102, "HR", "Emma Johnson", {"salary":105000,"address":{"city":"Melbourne","country":"Australia"}})
 
INSERT INTO EmployeeInfo VALUES (103, "IT", "Carlos Martinez", {"salary":110000,"address":{"city":"Barcelona","country":"Spain"}})   
 
INSERT INTO EmployeeInfo VALUES (104, "Finance", "Sophia Becker", {"salary":130000,"address":{"city":"Munich","country":"Germany"}})
 
INSERT INTO EmployeeInfo VALUES (105, "IT", "Ethan Dalmini", {"salary":250000,"address":{"city":"Cape Town","country":"South Africa"}})
 
INSERT INTO EmployeeInfo VALUES (106, "HR", "James Peterson", {"salary":100500,"address":{"city":"Istanbul","country":"Turkey"}})
 
INSERT INTO EmployeeInfo VALUES (107, "Finance", "John Doe", {"salary":250000,"address":{"city":"Seoul","country":"South Korea"}})

Example 7-32 Use UPDATE statement to update multiple rows in the table

The following statement updates the specified field in the rows associated with the mentioned shard key.

UPDATE EmployeeInfo emp
    SET emp.info.address.city="Oslo",
    SET emp.info.address.country="Norway",
    SET emp.info.salary = emp.info.salary + 5000
 where department="HR"

Explanation: In the above query, the SET clause updates the info.address.city field to 'Oslo,' the info.address.country field to 'Norway,' and increases the info.salary field by 5000 for all rows where the department column, designated as the shard key, equals 'HR.' Because the UPDATE statement only mentions the shard key, the database returns only the number of rows it updates.

Output:

+----------------+
 | NumRowsUpdated |
 +----------------+
 |              3 |
 +----------------+

Now, run the SELECT query to verify the updated rows. Check that you have updated the info.address.city and info.address.country fields to 'Oslo' and 'Norway,' respectively, and confirm that you have increased the info.salary field by 5000 for all employees working in the HR department.

select * from EmployeeInfo

Output:

+-------+------------+-----------------+----------------------------+
 | empID | department |    fullName     |            info            |
 +-------+------------+-----------------+----------------------------+
 |   103 | IT         | Carlos Martinez | address                    |
 |       |            |                 |     city    | Barcelona    |
 |       |            |                 |     country | Spain        |
 |       |            |                 | salary      | 110000       |
 +-------+------------+-----------------+----------------------------+
 |   105 | IT         | Ethan Dalmini   | address                    |
 |       |            |                 |     city    | Cape Town    |
 |       |            |                 |     country | South Africa |
 |       |            |                 | salary      | 250000       |
 +-------+------------+-----------------+----------------------------+
 |   101 | HR         | Liam Phillips   | address                    |
 |       |            |                 |     city    | Oslo         |
 |       |            |                 |     country | Norway       |
 |       |            |                 | salary      | 105000       |
 +-------+------------+-----------------+----------------------------+
 |   102 | HR         | Emma Johnson    | address                    |
 |       |            |                 |     city    | Oslo         |
 |       |            |                 |     country | Norway       |
 |       |            |                 | salary      | 110000       |
 +-------+------------+-----------------+----------------------------+
 |   106 | HR         | James Peterson  | address                    |
 |       |            |                 |     city    | Oslo         |
 |       |            |                 |     country | Norway       |
 |       |            |                 | salary      | 105500       |
 +-------+------------+-----------------+----------------------------+
 |   104 | Finance    | Sophia Becker   | address                    |
 |       |            |                 |     city    | Munich       |
 |       |            |                 |     country | Germany      |
 |       |            |                 | salary      | 130000       |
 +-------+------------+-----------------+----------------------------+
 |   107 | Finance    | John Doe        | address                    |
 |       |            |                 |     city    | Seoul        |
 |       |            |                 |     country | South Korea  |
 |       |            |                 | salary      | 250000       |
 +-------+------------+-----------------+----------------------------+
 
7 rows returned