Previous Next JavaScript must be enabled to correctly display this content

## Example: Updating JSON Data

You can update JSON data using either JSON MERGE clause or ADD clause.

JSON MERGE Clause

You can use the JSON MERGE clause to make a set of changes to a JSON document. The samples in the following section consider airline baggage tracking application and streaming media service application from the CREATE TABLE topic.

Example 7-33 Update existing fields and add a new field to a JSON document

    UPDATE BaggageInfo b
    JSON MERGE b.bagInfo[0] WITH PATCH {"tagNum" : "18657806255240", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"}
    WHERE ticketNo = 1762344493810 RETURNING b.bagInfo[0].tagNum, b.bagInfo[0].routing, b.bagInfo[0].lastSeenStation, b.bagInfo[0].bagStatus

Explanation: In the query above, you update a passenger's record in the airline baggage tracking application to correct a few inadvertent errors. The JSON MERGE clause compares the content of the patch with the current contents and updates the `tagNum`, `routing`, and `lastSeenStation` fields in the first element of the `bagInfo` JSON array. As the `bagStatus` field is currently not a part of the first element in the `bagInfo` array, the JSON MERGE patch inserts it into the document.

You use the UPDATE statement with a RETURNING clause to display only the updated fields.

Output:

    {
      "tagNum" : "18657806255240",
      "routing" : "MIA/LAX",
      "lastSeenStation" : "LAX",
      "bagStatus" : "delivered"
    }

Note:

Here, `bagInfo` is an array of JSON documents, which represents the checked bags per passenger. If you don't specify the array element to be updated, the JSON MERGE clause replaces the entire `bagInfo` array with the patch content. If there are more than one checked bag per passenger, you can use the JSON MERGE clause iteratively in the same UPDATE statement as follows:

    UPDATE BaggageInfo b
    JSON MERGE b.bagInfo[0] WITH PATCH {"tagNum" : "18657806255111", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"},
    JSON MERGE b.bagInfo[1] WITH PATCH {"tagNum" : "18657806255112", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"}
    WHERE ticketNo = 1762320369957

Example 7-34 Add a new field using its JSON path in the patch

Consider the following passenger's data from the airline baggage tracking application. The nested `flightLegs` document does not include the flight number.

    {"ticketNo":1882344493810,"fullName":"Joan Smith","gender":"F","contactPhone":"886-324-1064","confNo":"LE6F4Z","bagInfo":[{"flightLegs":{"estimatedArrival":"2019-02-01T11:00:00Z","flightDate":"2019-02-01T06:00:00Z","fltRouteDest":"LAX","fltRouteSrc":"MIA"},"id":"79039899165297","routing":"MIA/LAX","tagNum":"17657806255240"}]}

You can apply a patch to include the `flightNo` field into the `flightLegs` document as follows:

    UPDATE BaggageInfo b
    JSON MERGE b.bagInfo[0] WITH PATCH {"flightLegs": {"flightNo":"BM107"}} WHERE ticketNo = 1882344493810 RETURNING *

Explanation: Here, you supply the JSON path of the `flightNo` field in the patch expression. The JSON MERGE clause evaluates the JSON path and inserts the `flightNo` field into the nested `flightLegs` document of the first element of the `bagInfo` array.

Output:

    {"ticketNo":1882344493810,"fullName":"Joan Smith","gender":"F","contactPhone":"886-324-1064","confNo":"LE6F4Z","bagInfo":[{"flightLegs":{"estimatedArrival":"2019-02-01T11:00:00Z","flightDate":"2019-02-01T06:00:00Z","flightNo":"BM107","fltRouteDest":"LAX","fltRouteSrc":"MIA"},"id":"79039899165297","routing":"MIA/LAX","tagNum":"17657806255240"}]}

Note:

If the nested `flightLegs` document does not exist, the JSON MERGE patch creates one.

Example 7-35 Remove field from a JSON document and insert a nested document

    UPDATE stream_acct s
    JSON MERGE s.acct_data WITH PATCH {"country" : NULL, "recommended" : {"showName1" : "laugh it", "showName2": "Mystery solved", "showName3": "bakesnCooks"}}
    WHERE acct_id = 1 RETURNING *

Explanation: In the query above, you use the JSON MERGE clause to update a subscriber record in the streaming media service application. In the patch expression, you supply the `country` and `recommended` fields. The JSON MERGE patch inserts the new nested `recommended` JSON document into the `acct_data` JSON document. You indicate the removal of the `country` field from the document by supplying a NULL value.

The RETURNING clause fetches the entire updated row.

Output:

    {
      "acct_id" : 1,
      "profile_name" : "AP",
      "account_expiry" : "2023-10-18T00:00:00.000000000Z",
      "acct_data" : {
        "contentStreamed" : [{
          "genres" : ["action", "crime", "spanish"],
          "numSeasons" : 4,
          "seriesInfo" : [{
            "episodes" : [{  .... }],
          "showId" : 26,
          "showName" : "At the Ranch",
          "showtype" : "tvseries"
        }, {
          "genres" : ["comedy", "french"],
          "numSeasons" : 2,
          "seriesInfo" : [{
            "episodes" : [{ .... }],

          "showId" : 15,
          "showName" : "Bienvenu",
          "showtype" : "tvseries"
        }],
        "firstName" : "Adam",
        "lastName" : "Phillips",
        "recommended" : {
          "showName1" : "laugh it",
          "showName2" : "Mystery solved",
          "showName3" : "bakesnCooks"
        }
      }
    }

Example 7-36 Update/add a field to a document

Consider following subscriber records from the streaming media service application.

    {"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":{"contentStreamed":[{"genres":["action","crime","spanish"],"numSeasons":4,"showId":26,"showName":"At the Ranch","showtype":"tvseries"},{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"}],"firstName":"Adam","lastName":"Phillips"}}

    {"acct_id":2,"profile_name":"AW","account_expiry":"2025-12-18T00:00:00.000000000Z","acct_data":{"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"country":"France","firstName":"Adelaide","lastName":"Willard"}}

Example 4a: Use the JSON MERGE clause to update the country field in the `acct_data` JSON document for the records above:

    UPDATE stream_acct s
    JSON MERGE s.acct_data WITH PATCH { "country" : "USA"} WHERE acct_id = 1 RETURNING s.acct_id, s.acct_data.country

    UPDATE stream_acct s
    JSON MERGE s.acct_data WITH PATCH { "country" : "Italy"} WHERE acct_id = 2 RETURNING s.acct_id, s.acct_data.country

Explanation: In the first UPDATE statement for the `acct_id` 1, the JSON MERGE clause inserts the country field to the `acct_data` JSON document as the field does not exist in the original document. In the second UPDATE statement, the JSON MERGE clause updates the `country` field value from `France` to `Italy` for the subscriber `acct_id` 2.

Output:

    {"acct_id":1,"country":"USA"}
    1 row returned

    {"acct_id":2,"country":"Italy"}
    1 row returned

Example 4b: Update the `country` field using path expression.

Consider the original subscriber records from the streaming media service application.

Update the `country` field using its path expression for the record with the `acct_id` 2.

    UPDATE stream_acct s
    JSON MERGE s.acct_data.country WITH PATCH "Switzerland" where acct_id = 2

    {"NumRowsUpdated":1}

Explanation: The UPDATE operation is successful, and the `country` field value is replaced with the supplied value. Now update the subscriber record for `acct_id` 1.

    UPDATE stream_acct s
    JSON MERGE s.acct_data.country WITH PATCH "USA" where acct_id = 1

    {"NumRowsUpdated":0}

In contrast to the UPDATE operation in the Example 4a for `acct_id` 1, where the country field was successfully inserted, here it is a no-op. When the field that is directly accessed using its path expression does not exist in the target JSON document, the resulting JSON MERGE operation is a no-op.

Example 7-37 Update a JSON NULL field

Consider the following subscriber record from the streaming media service application.

    {"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":null}

Here, the `acct_data` is a JSON field with a NULL value. Use the JSON MERGE clause to update the field.

    JSON MERGE s.acct_data WITH PATCH {"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"country":NULL,"firstName":"Adam","lastName":"null"} WHERE acct_id = 1 RETURNING *

Explanation: The JSON MERGE patch replaces the NULL value in the `acct_data` document with the supplied JSON values from the patch expression. Observe that you supply a JSON NULL value to the `country` field. The JSON MERGE patch does not insert this field in the `acct_data` document. However, the `lastName` field with a string value `null` is successfully inserted.

Output:

    {"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":{"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"firstName":"Adam","lastName":"null"}}

ADD clause

You can use ADD clause to update a field in a JSON document. You can add one or more array elements to an existing array using the ADD clause. You can also optionally indicate the position of the new elements to be added in the array.

Example 7-38 Adding a single element to an existing array in JSON data

The `People` table has one row currently as shown below. For creating the table and inserting data, see Example: Updating Rows.

    SELECT * FROM People

    {
      "id" : 0,
      "info" : {
        "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"
          },
          "Mary" : {
            "age" : 8,
            "friends" : ["Anna", "Mark", "Ada", "Aris"],
            "school" : "school_3"
          },
          "Ron" : {
            "age" : 3,
            "friends" : ["Julie", "Ada", "Aris"]
          }
        },
        "firstName" : "John",
        "income" : 20000,
        "lastName" : "Doe",
        "profession" : "surfing instructor"
      }
    }

Add a new element to the `phones` array at the beginning of the array.

    UPDATE People p ADD p.info.address.phones 0
    {"areacode":499, "number":33864368, "kind":"mobile" }
    WHERE id = 0

    SELECT * FROM People

Output:

    {

      "id" : 0,
      "info" : {
        "address" : {
          "city" : "Santa Cruz",
          "phones" : [{
            "areacode" : 499,
            "kind" : "mobile",
            "number" : 33864368
          }, {
            "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"
          },
          "Mary" : {
            "age" : 8,
            "friends" : ["Anna", "Mark", "Ada", "Aris"],
            "school" : "school_3"
          },
          "Ron" : {
            "age" : 3,
            "friends" : ["Julie", "Ada", "Aris"]
          }
        },
        "firstName" : "John",
        "income" : 20000,
        "lastName" : "Doe",
        "profession" : "surfing instructor"
      }
    }

Example 7-39 Adding an array of elements to an existing array in JSON data

When you need to add more than one element of an array to an existing array in JSON data, you need to add the new-elements expressions inside parentheses and optionally add the position expression (if any).

The following query throws an error as shown below:

    UPDATE People p
    ADD p.info.address.phones
    0 { "areacode":5, "number":1, "kind":"mobile" },
      { "areacode":6, "number":2, "kind":"mobile" }
    WHERE id = 0

Output:

    Error handling command UPDATE People p
    ADD p.info.address.phones
    0 { "areacode":5, "number":1, "kind":"mobile" },
      { "areacode":6, "number":2, "kind":"mobile" }
    WHERE id = 0:
    Error: at (5, 12) mismatched input '<EOF>' expecting {WHERE, ','}, at line 5:12
    rule stack: [parse, statement, update_statement]

This can be corrected using the one of the two different options as shown below:

Option 1:

    UPDATE People p
    ADD p.info.address.phones
    ([{ "areacode":1, "number":1, "kind":"mobile" },
      { "areacode":2, "number":2, "kind":"mobile" }
     ][])
    WHERE id = 0

Output:

    {
      "NumRowsUpdated" : 1
    }
    1 row returned

Option 2:

    UPDATE People p
    ADD p.info.address.phones
    0 [{ "areacode":3, "number":1, "kind":"mobile" },
       {"areacode":4, "number":2, "kind":"mobile" }
      ]
    WHERE id = 0

Output:

    {
      "NumRowsUpdated" : 1
    }
    1 row returned

The result of the UPDATE statement can be verified as shown below.

    select * from People

Output:

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