Example: Updating JSON Data

This is an example of handling heterogeneity in json documents. Assume that the Peoples table contains the row from earlier example, as well as the following row:

INSERT INTO People VALUES (
    1,
    {
        "firstName":"Jane",
        "lastName":"Doe",
        "address": {
            "city": "Santa Cruz",
            "state" : "CA",
            "phones" : { "areacode":831, "number":5294368, "kind":"mobile" }
        }
    }
);

Jane has a single phone which is not stored inside an array, but as a single json object. We want to write an update query to add a phone for a person. The query must work on any row, where some rows store phones as an array, and others store it as a single json object. Here it is:

DECLARE $id INTEGER;
$areacode INTEGER;
$number INTEGER;
$kind STRING;

UPDATE People p
ADD p.info.address[$element.phones IS OF TYPE (ARRAY(any))].phones
    { "areacode" : $areacode, "number" : $number, "kind" : $kind },
SET p.info.address[$element.phones is of type (map(any))].phones =
    [ $, { "areacode" : $areacode, "number" : $number, "kind" : $kind } ]
WHERE id = $id;

In the ADD clause, the expression p.info.address[$element.phones is of type (array(any))].phones checks whether the phones field of the address is an array and if so, returns that array, which becomes the target of the ADD. The 2nd expression in the add is a json-object constructor, that creates the new phone object and appends it into the target array.

Note:

Although p.info.address is not an array, an array filtering step is applied to it in the target expression. This works fine because (as explained in the Array-Filter Step Expressions section) the address will be treated as an array containing the address as its only element; as a result, $element will be bound to the address item, and if the filtering condition is true, the address items becomes the input to the next step in the path expression.

In the SET clause, the first expression checks whether the phones field of the address is a json object, and if so, returns that phone object, which becomes the target of the SET. The second expression constructs a new array with 2 json objects: the first is the existing phone object and the second is the newly constructed phone object. The target object is then replaced with the newly constructed array.