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"] 
            }
        }
    }
);

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 could (and 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)
        )
    )
)