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"
  }
}