Inserting, Modifying, and Deleting Data

You can perform various data manipulation operations in your table. You can add data, modify an existing data and remove data.

If you want to follow along with the examples, download the script baggageschema_loaddata.sql and execute it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
Using the load command, execute the script.
load -file baggageschema_loaddata.sql

Insert data

The INSERT statement is used to construct a new row and add it to a specified table. Optional column(s) may be specified after the table name. This list contains the column names for a subset of the table’s columns. The subset must include all the primary key columns. If no columns list is present, the default columns list is the one containing all the columns of the table, in the order, they are specified in the CREATE TABLE statement.

The columns in the columns list correspond one-to-one to the expressions (or DEFAULT keywords) listed after the VALUES clause (an error is raised if the number of expressions/DEFAULTs is not the same as the number of columns). These expressions/DEFAULTs compute the value for their associated column in the new row. An error is raised if an expression returns more than one item. If an expression returns no result, NULL is used as the result of that expression. If instead of an expression, the DEFAULT keyword appears in the VALUES list, the default value of the associated column is used as the value of that column in the new row. The default value is also used for any missing columns when the number of columns in the columns list is less than the total number of columns in the table.

Example 1: Inserting a row into BaggageInfo table providing all column values:
INSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
    "id" : "7903989918469",
    "tagNum" : "17657806240229",
    "routing" : "JFK/MAD",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MAD",
    "flightLegs" : [ {
      "flightNo" : "BM495",
      "flightDate" : "2019-03-07T07:00:00Z",
      "fltRouteSrc" : "JFK",
      "fltRouteDest" : "MAD",
      "estimatedArrival" : "2019-03-07T14:00:00Z",
      "actions" : [ {
        "actionAt" : "MAD",
        "actionCode" : "Offload to Carousel at MAD",
        "actionTime" : "2019-03-07T13:54:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "ONLOAD to MAD",
        "actionTime" : "2019-03-07T07:00:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "BagTag Scan at JFK",
        "actionTime" : "2019-03-07T06:53:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "Checkin at JFK",
        "actionTime" : "2019-03-07T05:03:00Z"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
    "bagArrivalDate" : "2019-03-07T13:51:00Z"
  } ]
)

Example 2: Skipping some data while doing an INSERT statement by specifying the DEFAULT clause.

You can skip the data of some columns by specifying "DEFAULT".
INSERT INTO BaggageInfo VALUES(
1762397286805,
"Bonnie Williams",
DEFAULT,
DEFAULT,
"CZ1O5I",
[ {
    "id" : "79039899129693",
    "tagNum" : "17657806216554",
    "routing" : "SFO/ORD/FRA",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "FRA",
    "flightLegs" : [ {
      "flightNo" : "BM572",
      "flightDate" : "2019-03-02T05:00:00Z",
      "fltRouteSrc" : "SFO",
      "fltRouteDest" : "ORD",
      "estimatedArrival" : "2019-03-02T09:00:00Z",
      "actions" : [ {
        "actionAt" : "SFO",
        "actionCode" : "ONLOAD to ORD",
        "actionTime" : "2019-03-02T05:24:00Z"
      }, {
        "actionAt" : "SFO",
        "actionCode" : "BagTag Scan at SFO",
        "actionTime" : "2019-03-02T04:52:00Z"
      }, {
        "actionAt" : "SFO",
        "actionCode" : "Checkin at SFO",
        "actionTime" : "2019-03-02T03:28:00Z"
      } ]
    }, {
      "flightNo" : "BM582",
      "flightDate" : "2019-03-02T05:24:00Z",
      "fltRouteSrc" : "ORD",
      "fltRouteDest" : "FRA",
      "estimatedArrival" : "2019-03-02T13:24:00Z",
      "actions" : [ {
        "actionAt" : "FRA",
        "actionCode" : "Offload to Carousel at FRA",
        "actionTime" : "2019-03-02T13:20:00Z"
      }, {
        "actionAt" : "ORD",
        "actionCode" : "ONLOAD to FRA",
        "actionTime" : "2019-03-02T12:54:00Z"
      }, {
        "actionAt" : "ORD",
        "actionCode" : "OFFLOAD from ORD",
        "actionTime" : "2019-03-02T12:30:00Z"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-03-02T13:18:00Z",
    "bagArrivalDate" : "2019-03-02T13:18:00Z"
  } ]
)

Example 3: Specifying column names and skipping columns in the insert statement.

If you have data only for some columns of a table, you can specify the name of the columns in the INSERT clause and then specify the corresponding values in the "VALUES" clause.
INSERT INTO BaggageInfo(ticketNo, fullName,confNo,bagInfo) VALUES(
1762355349471,
"Bryant Weber",
"LI7N1W",
[ {
    "id" : "79039899149056",
    "tagNum" : "17657806234185",
    "routing" : "MEL/LAX/MIA",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MIA",
    "flightLegs" : [ {
      "flightNo" : "BM114",
      "flightDate" : "2019-03-01T12:00:00Z",
      "fltRouteSrc" : "MEL",
      "fltRouteDest" : "LAX",
      "estimatedArrival" : "2019-03-02T02:00:00Z",
      "actions" : [ {
        "actionAt" : "MEL",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-03-01T12:20:00Z"
      }, {
        "actionAt" : "MEL",
        "actionCode" : "BagTag Scan at MEL",
        "actionTime" : "2019-03-01T11:52:00Z"
      }, {
        "actionAt" : "MEL",
        "actionCode" : "Checkin at MEL",
        "actionTime" : "2019-03-01T11:43:00Z"
      } ]
    }, {
      "flightNo" : "BM866",
      "flightDate" : "2019-03-01T12:20:00Z",
      "fltRouteSrc" : "LAX",
      "fltRouteDest" : "MIA",
      "estimatedArrival" : "2019-03-02T16:21:00Z",
      "actions" : [ {
        "actionAt" : "MIA",
        "actionCode" : "Offload to Carousel at MIA",
        "actionTime" : "2019-03-02T16:18:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MIA",
        "actionTime" : "2019-03-02T16:12:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-03-02T16:02:00Z"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-03-02T16:09:00Z",
    "bagArrivalDate" : "2019-03-02T16:09:00Z"
  } ]
)
Example 4: Inserting a row into stream_acct table providing all column values:
INSERT INTO stream_acct VALUES(
1,
"AP",
"2023-10-18",
{
   "firstName": "Adam",
   "lastName": "Phillips",
   "country": "Germany",
   "contentStreamed": [{
      "showName": "At the Ranch",
      "showId": 26,
      "showtype": "tvseries",
      "genres": ["action", "crime", "spanish"],
      "numSeasons": 4,
      "seriesInfo": [{
         "seasonNum": 1,
         "numEpisodes": 2,
         "episodes": [{
            "episodeID": 20,
            "episodeName": "Season 1 episode 1",
            "lengthMin": 85,
            "minWatched": 85,
            "date": "2022-04-18"
         },
         {
            "episodeID": 30,
            "lengthMin": 60,
            "episodeName": "Season 1 episode 2",
            "minWatched": 60,
            "date": "2022 - 04 - 18 "
         }]
      },
      {
         "seasonNum": 2,
         "numEpisodes": 2,
         "episodes": [{
            "episodeID": 40,
            "episodeName": "Season 2 episode 1",
            "lengthMin": 50,
            "minWatched": 50,
            "date": "2022-04-25"
         },
         {
            "episodeID": 50,
            "episodeName": "Season 2 episode 2",
            "lengthMin": 45,
            "minWatched": 30,
            "date": "2022-04-27"
         }]
      },
      {
         "seasonNum": 3,
         "numEpisodes": 2,
         "episodes": [{
            "episodeID": 60,
            "episodeName": "Season 3 episode 1",
            "lengthMin": 50,
            "minWatched": 50,
            "date": "2022-04-25"
         },
         {
            "episodeID": 70,
            "episodeName": "Season 3 episode 2",
            "lengthMin": 45,
            "minWatched": 30,
            "date": "2022 - 04 - 27 "
         }]
      }]
   },
   {
      "showName": "Bienvenu",
      "showId": 15,
      "showtype": "tvseries",
      "genres": ["comedy", "french"],
      "numSeasons": 2,
      "seriesInfo": [{
         "seasonNum": 1,
         "numEpisodes": 2,
         "episodes": [{
            "episodeID": 20,
            "episodeName": "Bonjour",
            "lengthMin": 45,
            "minWatched": 45,
            "date": "2022-03-07"
         },
         {
            "episodeID": 30,
            "episodeName": "Merci",
            "lengthMin": 42,
            "minWatched": 42,
            "date": "2022-03-08"
         }]
      }]
   }]
});
Example 5: Insert data into the JSON collection table created for a shopping application.
INSERT into storeAcct(contactPhone, firstName, lastName, address, cart) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}])

In the above example, you insert the shopper's data by supplying the contactPhone as the primary key followed by other details of the shoppers. The shopper's details are stored as a single document. Notice that in JSON collection tables, you do not supply a column name for the document itself and you only provide the JSON fields in the document.

You can add another row to the same table with additional fields.
INSERT into  storeAcct(contactPhone, firstName, lastName, gender, address, notify, cart, wishlist) values("1917113999", "Sharon", "Willard", "F", {"street" : "Maine", "number" : 501, "city" : "San Jose", "state" : "San Francisco",   "zip" : 95095},"yes", [{"item" : "wallet", "quantity" : 2, "priceperunit" : 950},{"item" : "wall art", "quantity" : 1, "priceperunit" : 9500}], [{"item" : "Tshirt", "priceperunit" : 500},{"item" : "Jenga", "priceperunit" : 850}])

In the above statement, you insert the shopper data with additional fields such as gender, notify, and wishlist as compared with the first inserted row. The wishlist field is a JSON array that includes the details of the items wishlisted by the shopper.

Using APIs to insert data into tables

Use the Oracle NoSQL Database SDK for Java to add data to a table. Download the full code AddData.java from the examples here.
private static void writeRows(NoSQLHandle handle, MapValue value) 
                                               throws Exception {
   PutRequest putRequest =
     new PutRequest().setValue(value).setTableName(tableName);
   PutResult putResult = handle.put(putRequest);

   if (putResult.getVersion() != null) {
      System.out.println("Added a row to the stream_acct table");
   } else {
      System.out.println("Put failed");
   }
}
Inserting data into a JSON collection table: You can insert the top-level fields of the document in the JSON collection table using a sequence of PutRequest operations. For nested-level JSON fields, you can supply the JSON string in the putFromJson operation. You can also use the createFromJson method which takes the fields as a JSON string and uses that to populate a row in the table.
/*
 * Construct a row for JSON collection table with the following data:
 * {
 *   "id": 1,
     "name": "John Doe",
     "age": 25,
     "college" : {"name" : "Presidency", "branch" : "Biotechnology"}
 *   }
 * }
 */

String tableName = "usersJSON";

MapValue value = new MapValue().put("id", 1)

            .put("name", "John Doe")
            .put("age", 25)
            .putFromJson("college",  
                 "{\"name\" : \"Presidency\"," +
                 "\"branch\" : \"Biotechnology\"" +
                 " }", null); 

            PutRequest putRequest = new PutRequest()
                .setValue(value)
                .setTableName(tableName);

            PutResult putRes = handle.put(putRequest);
            System.out.println("Put row: " + value + " result=" + putRes);
Use the Oracle NoSQL Database Python SDK to add data to a table. Download the full code AddData.py from the examples here.
def insert_record(handle,table_name,acct_data):
  request = PutRequest().set_table_name(table_name)
                        .set_value_from_json(acct_data)

  handle.put(request)
  print('Added a row to the stream_acct table')
Inserting data into a JSON collection table: You can add a row directly into the JSON collection table as a JSON string.
from borneo import PutRequest
request = PutRequest().set_table_name('usersJSON').request.set_value_from_json('{"id": 1, "name": "John Doe", "age": 25, "college" : {"name" : "Presidency", "branch" : "Biotechnology"}}')
handle.put(request)
Use the Oracle NoSQL Database Go SDK to add data to a table. Download the full code AddData.go from the examples here.
func insertData(client *nosqldb.Client, err error, 
                tableName string,value1 *types.MapValue )(){
  putReq := &nosqldb.PutRequest{
    	TableName: tableName,
    	Value: value1,
  }
  putRes, err := client.Put(putReq)
  if err != nil {
     fmt.Printf("failed to put single row: %v\n", err)
     return
  }
  fmt.Printf("Added a row to the stream_acct table\n")
}
Inserting data into a JSON collection table: You can create a map value from JSON data and add the row to the JSON collection table.
value, err:=types.NewMapValueFromJSON(`{"id": 1, "name": "John Doe", "age": 25, "college" : {"name" : "Presidency", "branch" : "Biotechnology"}}`)
iferr!=nil {
    return
}
req:=&nosqldb.PutRequest{
    TableName: "usersJSON",
    Value: value,
}
res, err:=client.Put(req) 
Use the Oracle NoSQL Database Node.js SDK add data to a table. Download the full code AddData.js from the examples here.
/* Adding 3 records in acct_stream table */
let putResult = await handle.put(TABLE_NAME, JSON.parse(acct1));
let putResult1 = await handle.put(TABLE_NAME, JSON.parse(acct2));
let putResult2 = await handle.put(TABLE_NAME, JSON.parse(acct3));

console.log("Added rows to the stream_acct table");
Inserting data into a JSON collection table: You can add a row into the JSON collection table by supplying a plain JavaScript object with supported JSON types.
import { NoSQLClient, ServiceType } from 'oracle-nosqldb';
const client = new NoSQLClient('config.json');
const TABLE_NAME = 'usersJSON';
const record = {id : 1, 
    name : 'John Doe',
    age : 25,
    college : {
        name : 'Presidency', 
        branch : 'Biotechnology'
        }
}


async function writeARecord(client, record) {
    await client.put(TABLE_NAME, record);
}
Use the Oracle NoSQL Database Dotnet SDK to add data to a table. Download the full code AddData.cs from the examples here.
private static async Task insertData(NoSQLClient client, String acctdet){
   var putResult = await client.PutAsync(TableName, 
                              FieldValue.FromJsonString(acctdet).AsMapValue);
   if (putResult.ConsumedCapacity != null)
   {
      Console.WriteLine(" Added a row to the stream_acct table");      
   }
}
Inserting data into a JSON collection table: You add a row in the JSON collection table by putting its value from a JSON string created through FromJsonString method.
var tableName = "usersJSON";

private const string data= @"{
"id": 1,
"name": "John Doe",
"age": 25,
"college" : {"name" : "Presidency", "branch" : "Biotechnology"}
}

var result = await client.PutAsync(tableName, FieldValue.FromJsonString(data).AsMapValue);

Upsert Data

The word UPSERT combines UPDATE and INSERT, describing the statement's function. Use an UPSERT statement to insert a row where it does not exist, or to update the row with new values when it does.

Example 1: Updating data in the BaggageInfo table using UPSERT command.

The existing details for the customer with full name Adam Phillips is shown below.
SELECT * FROM BaggageInfo WHERE fullname="Adam Phillips"

{
  "ticketNo" : 1762344493810,
  "fullName" : "Adam Phillips",
  "gender" : "M",
  "contactPhone" : "893-324-1064",
  "confNo" : "LE6J4Z",
  "bagInfo" : [{
    "bagArrivalDate" : "2019-02-01T16:13:00Z",
    "flightLegs" : [{
      "actions" : [{
        "actionAt" : "MIA",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-02-01T06:13:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "BagTag Scan at MIA",
        "actionTime" : "2019-02-01T05:47:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "Checkin at MIA",
        "actionTime" : "2019-02-01T04:38:00Z"
      }],
      "estimatedArrival" : "2019-02-01T11:00:00Z",
      "flightDate" : "2019-02-01T06:00:00Z",
      "flightNo" : "BM604",
      "fltRouteDest" : "LAX",
      "fltRouteSrc" : "MIA"
    }, {
      "actions" : [{
        "actionAt" : "MEL",
        "actionCode" : "Offload to Carousel at MEL",
        "actionTime" : "2019-02-01T16:15:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MEL",
        "actionTime" : "2019-02-01T15:35:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-02-01T15:18:00Z"
      }],
      "estimatedArrival" : "2019-02-01T16:15:00Z",
      "flightDate" : "2019-02-01T06:13:00Z",
      "flightNo" : "BM667",
      "fltRouteDest" : "MEL",
      "fltRouteSrc" : "LAX"
    }],
    "id" : "79039899165297",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MEL",
    "lastSeenTimeGmt" : "2019-02-01T16:13:00Z",
    "routing" : "MIA/LAX/MEL",
    "tagNum" : "17657806255240"
  }]
}
1 row returned
You modify the existing row using the UPSERT command. You can use an optional RETURNING clause to fetch the values after UPSERT is performed. The updated value for the customer with full name Adam Phillips is fetched as shown below.
UPSERT INTO BaggageInfo VALUES(
1762344493810,
"Adam Phillips",
"M",
"893-324-1864",
"LE6J4Y",
[ {
    "id" : "79039899165297",
    "tagNum" : "17657806255240",
    "routing" : "MIA/LAX/MEL",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MEL",
    "flightLegs" : [ {
      "flightNo" : "BM604",
      "flightDate" : "2019-02-01T06:00:00Z",
      "fltRouteSrc" : "MIA",
      "fltRouteDest" : "LAX",
      "estimatedArrival" : "2019-02-01T11:00:00Z",
      "actions" : [ {
        "actionAt" : "MIA",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-02-01T06:13:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "BagTag Scan at MIA",
        "actionTime" : "2019-02-01T05:47:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "Checkin at MIA",
        "actionTime" : "2019-02-01T04:38:00Z"
      } ]
    }, {
      "flightNo" : "BM667",
      "flightDate" : "2019-02-01T06:13:00Z",
      "fltRouteSrc" : "LAX",
      "fltRouteDest" : "MEL",
      "estimatedArrival" : "2019-02-01T16:15:00Z",
      "actions" : [ {
        "actionAt" : "MEL",
        "actionCode" : "Offload to Carousel at MEL",
        "actionTime" : "2019-02-01T16:15:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MEL",
        "actionTime" : "2019-02-01T15:35:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-02-01T15:18:00Z"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
    "bagArrivalDate" : "2019-02-01T16:18:00Z"
  } ]
) RETURNING *


{
  "ticketNo" : 1762344493810,
  "fullName" : "Adam Phillips",
  "gender" : "M",
  "contactPhone" : "893-324-1864",
  "confNo" : "LE6J4Y",
  "bagInfo" : [{
    "bagArrivalDate" : "2019-02-01T16:18:00Z",
    "flightLegs" : [{
      "actions" : [{
        "actionAt" : "MIA",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-02-01T06:13:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "BagTag Scan at MIA",
        "actionTime" : "2019-02-01T05:47:00Z"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "Checkin at MIA",
        "actionTime" : "2019-02-01T04:38:00Z"
      }],
      "estimatedArrival" : "2019-02-01T11:00:00Z",
      "flightDate" : "2019-02-01T06:00:00Z",
      "flightNo" : "BM604",
      "fltRouteDest" : "LAX",
      "fltRouteSrc" : "MIA"
    }, {
      "actions" : [{
        "actionAt" : "MEL",
        "actionCode" : "Offload to Carousel at MEL",
        "actionTime" : "2019-02-01T16:15:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MEL",
        "actionTime" : "2019-02-01T15:35:00Z"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-02-01T15:18:00Z"
      }],
      "estimatedArrival" : "2019-02-01T16:15:00Z",
      "flightDate" : "2019-02-01T06:13:00Z",
      "flightNo" : "BM667",
      "fltRouteDest" : "MEL",
      "fltRouteSrc" : "LAX"
    }],
    "id" : "79039899165297",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MEL",
    "lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
    "routing" : "MIA/LAX/MEL",
    "tagNum" : "17657806255240"
  }]
}

Note:

If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values.

Example 2: Inserting data in the BaggageInfo table using UPSERT command.

A new entry value for a customer with full name Birgit Naquin is added using the UPSERT command.
SELECT * FROM BaggageInfo WHERE fullname="Birgit Naquin";

0 row returned


UPSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
    "id" : "7903989918469",
    "tagNum" : "17657806240229",
    "routing" : "JFK/MAD",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MAD",
    "flightLegs" : [ {
      "flightNo" : "BM495",
      "flightDate" : "2019-03-07T07:00:00Z",
      "fltRouteSrc" : "JFK",
      "fltRouteDest" : "MAD",
      "estimatedArrival" : "2019-03-07T14:00:00Z",
      "actions" : [ {
        "actionAt" : "MAD",
        "actionCode" : "Offload to Carousel at MAD",
        "actionTime" : "2019-03-07T13:54:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "ONLOAD to MAD",
        "actionTime" : "2019-03-07T07:00:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "BagTag Scan at JFK",
        "actionTime" : "2019-03-07T06:53:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "Checkin at JFK",
        "actionTime" : "2019-03-07T05:03:00Z"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
    "bagArrivalDate" : "2019-03-07T13:51:00Z"
  } ]
)

{"NumRowsInserted":1}

1 row returned
The result shows {"NumRowsInserted":1} which implies a new row has been inserted. The value inserted using the UPSERT command can be viewed as shown below:
SELECT * FROM BaggageInfo where fullname="Birgit Naquin"
{
  "ticketNo" : 1762392196147,
  "fullName" : "Birgit Naquin",
  "gender" : "M",
  "contactPhone" : "165-742-5715",
  "confNo" : "QD1L0T",
  "bagInfo" : [{
    "bagArrivalDate" : "2019-03-07T13:51:00Z",
    "flightLegs" : [{
      "actions" : [{
        "actionAt" : "MAD",
        "actionCode" : "Offload to Carousel at MAD",
        "actionTime" : "2019-03-07T13:54:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "ONLOAD to MAD",
        "actionTime" : "2019-03-07T07:00:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "BagTag Scan at JFK",
        "actionTime" : "2019-03-07T06:53:00Z"
      }, {
        "actionAt" : "JFK",
        "actionCode" : "Checkin at JFK",
        "actionTime" : "2019-03-07T05:03:00Z"
      }],
      "estimatedArrival" : "2019-03-07T14:00:00Z",
      "flightDate" : "2019-03-07T07:00:00Z",
      "flightNo" : "BM495",
      "fltRouteDest" : "MAD",
      "fltRouteSrc" : "JFK"
    }],
    "id" : "7903989918469",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MAD",
    "lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
    "routing" : "JFK/MAD",
    "tagNum" : "17657806240229"
  }]
}
1 row returned

Note:

If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values. You can also use an optional RETURNING clause as part of the UPSERT command.

Example 3: Add a new shopper's record to the storeAcct table.

You can use the UPSERT statement to add a new document or update fields in an existing document in the JSON collection tables. Consider the JSON collection table created for a shopping application table.

UPSERT into storeAcct values ("1417114588", {"firstName" : "Dori", "lastName" : "Martin", "email" : "dormartin@usmail.com", "address" : {"Dropbox" : "Presidency College"}}) RETURNING *;

In the above example, you use the UPSERT statement to add a new row to the storeAcct table.

You can use the UPSERT statement to update a shopper's information. Only the fields supplied in the UPSERT statement are updated in the document. The omitted fields are removed from the document.

Output:
{"contactPhone":"1417114588","address":{"Dropbox":"Presidency College"},"email":"lorphil@usmail.com","firstName":"Dori","lastName":"Martin"}
Example 4: Use UPSERT statement to add/modify data in the stream_acct table.
UPSERT INTO stream_acct VALUES
(
   1,
   "AP",
   "2023-10-18",
   {
      "firstName": "Adam",
      "lastName": "Phillips",
      "country": "Germany",
      "contentStreamed": [{
         "showName": "At the Ranch",
         "showId": 26,
         "showtype": "tvseries",
         "genres": ["action", "crime", "spanish"],
         "numSeasons": 4,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Season 1 episode 1",
               "lengthMin": 75,
               "minWatched": 75,
               "date": "2022-04-18"
            },
            {
               "episodeID": 30,
               "lengthMin": 60,
               "episodeName": "Season 1 episode 2",
               "minWatched": 40,
               "date": "2022 - 04 - 18 "
            }]
         },
         {
            "seasonNum": 2,
            "numEpisodes": 2,
            "episodes": [{
            "episodeID": 40,
               "episodeName": "Season 2 episode 1",
               "lengthMin": 40,
               "minWatched": 30,
               "date": "2022-04-25"
            },
            {
               "episodeID": 50,
               "episodeName": "Season 2 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022-04-27"
            }]
         },
         {
            "seasonNum": 3,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 60,
               "episodeName": "Season 3 episode 1",
               "lengthMin": 20,
               "minWatched": 20,
               "date": "2022-04-25"
            },
            {
               "episodeID": 70,
               "episodeName": "Season 3 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022 - 04 - 27 "
            }]
         }]
      },
      {
         "showName": "Bienvenu",
         "showId": 15,
         "showtype": "tvseries",
         "genres": ["comedy", "french"],
         "numSeasons": 2,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Bonjour",
               "lengthMin": 45,
               "minWatched": 45,
               "date": "2022-03-07"
            },
            {
               "episodeID": 30,
               "episodeName": "Merci",
               "lengthMin": 42,
               "minWatched": 42,
               "date": "2022-03-08"
            }]
         }]
      }]
   }
) RETURNING * 

In the above example, a new row is inserted if the stream_acct table does not have a row corresponding to acct_id =1. Else the existing row with the value of acct_id =1 is updated.

Using APIs to upsert data into tables

Use the Oracle NoSQL Database Java SDK to upsert data in a table. Download the full code ModifyData.java from the examples here.
/*Upsert data*/
private static void upsertRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
         for (MapValue res : results) {
            System.out.println("\t" + res);
         }
      }
}
String upsert_row = "UPSERT INTO stream_acct VALUES("+
         "1,"+
         "\"AP\","+
         "\"2023-10-18\","+
         "{\"firstName\": \"Adam\","+
         "\"lastName\": \"Phillips\","+
         "\"country\": \"Germany\","+
         "\"contentStreamed\": [{"+
            "\"showName\" : \"At the Ranch\","+
            "\"showId\" : 26,"+
            "\"showtype\" : \"tvseries\","+
            "\"genres\" : [\"action\", \"crime\", \"spanish\"],"+
            "\"numSeasons\" : 4,"+
            "\"seriesInfo\": [ {"+
               "\"seasonNum\" : 1,"+
               "\"numEpisodes\" : 2,"+
               "\"episodes\": [ {"+
                  "\"episodeID\": 20,"+
                  "\"episodeName\" : \"Season 1 episode 1\","+
                  "\"lengthMin\": 70,"+
                  "\"minWatched\": 70,"+
                  "\"date\" : \"2022-04-18\""+
               "},"+
               "{"+
                  "\"episodeID\": 30,"+
                  "\"lengthMin\": 60,"+
   		 "\"episodeName\" : \"Season 1 episode 2\","+
                  "\"minWatched\": 60,"+
                  "\"date\" : \"2022-04-18\""+
               "}]"+
            "},"+
            "{"+
               "\"seasonNum\": 2,"+
               "\"numEpisodes\" : 2,"+
               "\"episodes\": [{"+
                  "\"episodeID\": 40,"+
   		 "\"episodeName\" : \"Season 2 episode 1\","+
                  "\"lengthMin\": 40,"+
                  "\"minWatched\": 40,"+
                  "\"date\" : \"2022-04-25\""+
               "},"+
               "{"+
                  "\"episodeID\": 50,"+
   		 "\"episodeName\" : \"Season 2 episode 2\","+
                  "\"lengthMin\": 45,"+
                  "\"minWatched\": 30,"+
                  "\"date\" : \"2022-04-27\""+
               "}"+
               "]"+
            "},"+
            "{"+
               "\"seasonNum\": 3,"+
               "\"numEpisodes\" : 2,"+
               "\"episodes\": [{"+
                  "\"episodeID\": 60,"+
   	        "\"episodeName\" : \"Season 3 episode 1\","+
                  "\"lengthMin\": 50,"+
                  "\"minWatched\": 50,"+
                  "\"date\" : \"2022-04-25\""+
               "},"+
               "{"+
                  "\"episodeID\": 70,"+
   		  "\"episodeName\" : \"Season 3 episode 2\","+
                  "\"lengthMin\": 45,"+
                  "\"minWatched\": 30,"+
                  "\"date\" : \"2022-04-27\""+
               "}"+
               "]"+
            "}"+
            "]"+
         "},"+
         "{"+
            "\"showName\": \"Bienvenu\","+
            "\"showId\": 15,"+
            "\"showtype\": \"tvseries\","+
            "\"genres\" : [\"comedy\", \"french\"],"+
            "\"numSeasons\" : 2,"+
            "\"seriesInfo\": ["+
            "{"+
               "\"seasonNum\" : 1,"+
               "\"numEpisodes\" : 2,"+
               "\"episodes\": ["+
               "{"+
                  "\"episodeID\": 20,"+
   		 "\"episodeName\" : \"Bonjour\","+
                  "\"lengthMin\": 45,"+
                  "\"minWatched\": 45,"+
                  "\"date\" : \"2022-03-07\""+
               "},"+
               "{"+
                  "\"episodeID\": 30,"+
   		 "\"episodeName\" : \"Merci\","+
                  "\"lengthMin\": 42,"+
                  "\"minWatched\": 42,"+
                  "\"date\" : \"2022-03-08\""+
               "}"+
               "]"+
            "}"+
            "]"+
         "}"+
         "]}) RETURNING *";
         System.out.println("Upsert data ");
         upsertRows(handle,upsert_row);
Use the Oracle NoSQL Database Python SDK to upsert data in a table. Download the full code ModifyData.py from the examples here.
#upsert data
def upsert_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   result = handle.query(request)
   print('Upsert data')
   for r in result.get_results():
      print('\t' + str(r))
upsert_row = '''
UPSERT INTO stream_acct VALUES
(
   1,
   "AP",
   "2023-10-18",
   {
      "firstName": "Adam",
      "lastName": "Phillips",
      "country": "Germany",
      "contentStreamed": [{
         "showName": "At the Ranch",
         "showId": 26,
         "showtype": "tvseries",
         "genres": ["action", "crime", "spanish"],
         "numSeasons": 4,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Season 1 episode 1",
               "lengthMin": 75,
               "minWatched": 75,
               "date": "2022-04-18"
            },
            {
               "episodeID": 30,
               "lengthMin": 60,
               "episodeName": "Season 1 episode 2",
               "minWatched": 40,
               "date": "2022 - 04 - 18 "
            }]
         },
         {
            "seasonNum": 2,
            "numEpisodes": 2,
            "episodes": [{
            "episodeID": 40,
               "episodeName": "Season 2 episode 1",
               "lengthMin": 40,
               "minWatched": 30,
               "date": "2022-04-25"
            },
            {
               "episodeID": 50,
               "episodeName": "Season 2 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022-04-27"
            }]
         },
         {
            "seasonNum": 3,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 60,
               "episodeName": "Season 3 episode 1",
               "lengthMin": 20,
               "minWatched": 20,
               "date": "2022-04-25"
            },
            {
               "episodeID": 70,
               "episodeName": "Season 3 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022 - 04 - 27 "
            }]
         }]
      },
      {
         "showName": "Bienvenu",
         "showId": 15,
         "showtype": "tvseries",
         "genres": ["comedy", "french"],
         "numSeasons": 2,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Bonjour",
               "lengthMin": 45,
               "minWatched": 45,
               "date": "2022-03-07"
            },
            {
               "episodeID": 30,
               "episodeName": "Merci",
               "lengthMin": 42,
               "minWatched": 42,
               "date": "2022-03-08"
            }]
         }]
      }]
   }
) RETURNING * 
'''
upsert_data(handle,upsert_row)
Use the Oracle NoSQL Database Go SDK to upsert data in a table. Download the full code ModifyData.go from the examples here.
//upsert data in the table
func upsertRows(client *nosqldb.Client, err error, 
                tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
	}
	prepRes, err := client.Prepare(prepReq)
	if err != nil {
		fmt.Printf("Prepare failed: %v\n", err)
		return
	}
	queryReq := &nosqldb.QueryRequest{
		PreparedStatement: &prepRes.PreparedStatement,   }
	var results []*types.MapValue
	for {
		queryRes, err := client.Query(queryReq)
		if err != nil {
			fmt.Printf("Upsert failed: %v\n", err)
			return
		}
		res, err := queryRes.GetResults()
		if err != nil {
			fmt.Printf("GetResults() failed: %v\n", err)
			return
		}
		results = append(results, res...)
		if queryReq.IsDone() {
			break
		}
	}
	for i, r := range results {
		fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
	}
}
upsert_data := `UPSERT INTO stream_acct VALUES(
1,
"AP",
"2023-10-18",
{
    "firstName": "Adam",
    "lastName": "Phillips",
    "country": "Germany",
    "contentStreamed": [
        {
            "showName": "At the Ranch",
            "showId": 26,
            "showtype": "tvseries",
            "genres": [
                "action",
                "crime",
                "spanish"
            ],
            "numSeasons": 4,
            "seriesInfo": [
                {
                    "seasonNum": 1,
                    "numEpisodes": 2,
                    "episodes": [
                        {
                            "episodeID": 20,
                            "episodeName": "Season 1 episode 1",
                            "lengthMin": 75,
                            "minWatched": 75,
                            "date": "2022-04-18"
                        },
                        {
                            "episodeID": 30,
                            "lengthMin": 60,
                            "episodeName": "Season 1 episode 2",
                            "minWatched": 40,
                            "date": "2022 - 04 - 18 "
                        }
                    ]
                },
                {
                    "seasonNum": 2,
                    "numEpisodes": 2,
                    "episodes": [
                        {
                            "episodeID": 40,
                            "episodeName": "Season 2 episode 1",
                            "lengthMin": 40,
                            "minWatched": 30,
                            "date": "2022-04-25"
                        },
                        {
                            "episodeID": 50,
                            "episodeName": "Season 2 episode 2",
                            "lengthMin": 45,
                            "minWatched": 30,
                            "date": "2022-04-27"
                        }
                    ]
                },
                {
                    "seasonNum": 3,
                    "numEpisodes": 2,
                    "episodes": [
                        {
                            "episodeID": 60,
                            "episodeName": "Season 3 episode 1",
                            "lengthMin": 20,
                            "minWatched": 20,
                            "date": "2022-04-25"
                        },
                        {
                            "episodeID": 70,
                            "episodeName": "Season 3 episode 2",
                            "lengthMin": 45,
                            "minWatched": 30,
                            "date": "2022 - 04 - 27 "
                        }
                    ]
                }
            ]
        },
        {
            "showName": "Bienvenu",
            "showId": 15,
            "showtype": "tvseries",
            "genres": [
                "comedy",
                "french"
            ],
            "numSeasons": 2,
            "seriesInfo": [
                {
                    "seasonNum": 1,
                    "numEpisodes": 2,
                    "episodes": [
                        {
                            "episodeID": 20,
                            "episodeName": "Bonjour",
                            "lengthMin": 45,
                            "minWatched": 45,
                            "date": "2022-03-07"
                        },
                        {
                            "episodeID": 30,
                            "episodeName": "Merci",
                            "lengthMin": 42,
                            "minWatched": 42,
                            "date": "2022-03-08"
                        }
                    ]
                }
            ]
        }
    ]
}) RETURNING *`

upsertRows(client, err,tableName,upsert_data)
Use the Oracle NoSQL Database Node.js SDK to upsert in a table. Download the full code ModifyData.js from the examples here.
/*upserts data in the table*/
async function upsertData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const upsert_row = `UPSERT INTO stream_acct VALUES
(
   1,
   "AP",
   "2023-10-18",
   {
      "firstName": "Adam",
      "lastName": "Phillips",
      "country": "Germany",
      "contentStreamed": [{
         "showName": "At the Ranch",
         "showId": 26,
         "showtype": "tvseries",
         "genres": ["action", "crime", "spanish"],
         "numSeasons": 4,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Season 1 episode 1",
               "lengthMin": 75,
               "minWatched": 75,
               "date": "2022-04-18"
            },
            {
               "episodeID": 30,
               "lengthMin": 60,
               "episodeName": "Season 1 episode 2",
               "minWatched": 40,
               "date": "2022 - 04 - 18 "
            }]
         },
         {
            "seasonNum": 2,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 40,
               "episodeName": "Season 2 episode 1",
               "lengthMin": 40,
               "minWatched": 30,
               "date": "2022-04-25"
            },
            {
               "episodeID": 50,
               "episodeName": "Season 2 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022-04-27"
            }]
         },
         {
            "seasonNum": 3,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 60,
               "episodeName": "Season 3 episode 1",
               "lengthMin": 20,
               "minWatched": 20,
               "date": "2022-04-25"
            },
            {
               "episodeID": 70,
               "episodeName": "Season 3 episode 2",
               "lengthMin": 45,
               "minWatched": 30,
               "date": "2022 - 04 - 27 "
            }]
         }]
      },
      {
         "showName": "Bienvenu",
         "showId": 15,
         "showtype": "tvseries",
         "genres": ["comedy", "french"],
         "numSeasons": 2,
         "seriesInfo": [{
            "seasonNum": 1,
            "numEpisodes": 2,
            "episodes": [{
               "episodeID": 20,
               "episodeName": "Bonjour",
               "lengthMin": 45,
               "minWatched": 45,
               "date": "2022-03-07"
            },
            {
               "episodeID": 30,
               "episodeName": "Merci",
               "lengthMin": 42,
               "minWatched": 42,
               "date": "2022-03-08"
            }]
         }]
      }]
   }) RETURNING *`

await upsertData(handle,upsert_row);
console.log("Upsert data into table");
Use the Oracle NoSQL Database Dotnet SDK to upsert data in a table. Download the full code ModifyData.cs from the examples here.
private static async Task upsertData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
  }
}
private const string upsert_row = @"UPSERT INTO stream_acct VALUES
(
   1,
   ""AP"",
   ""2023-10-18"",
   {
      ""firstName"": ""Adam"",
      ""lastName"": ""Phillips"",
      ""country"": ""Germany"",
      ""contentStreamed"": [{
         ""showName"": ""At the Ranch"",
         ""showId"": 26,
         ""showtype"": ""tvseries"",
         ""genres"": [""action"", ""crime"", ""spanish""],
         ""numSeasons"": 4,
         ""seriesInfo"": [{
            ""seasonNum"": 1,
            ""numEpisodes"": 2,
            ""episodes"": [{
               ""episodeID"": 20,
               ""episodeName"": ""Season 1 episode 1"",
               ""lengthMin"": 75,
               ""minWatched"": 75,
               ""date"": ""2022-04-18""
            },
            {
               ""episodeID"": 30,
               ""lengthMin"": 60,
               ""episodeName"": ""Season 1 episode 2"",
               ""minWatched"": 40,
               ""date"": ""2022 - 04 - 18""
            }]
         },
         {
            ""seasonNum"": 2,
            ""numEpisodes"": 2,
            ""episodes"": [{
               ""episodeID"": 40,
               ""episodeName"": ""Season 2 episode 1"",
               ""lengthMin"": 40,
               ""minWatched"": 30,
               ""date"": ""2022-04-25""
            },
            {
               ""episodeID"": 50,
               ""episodeName"": ""Season 2 episode 2"",
               ""lengthMin"": 45,
               ""minWatched"": 30,
               ""date"": ""2022-04-27""
            }]
         },
         {
            ""seasonNum"": 3,
            ""numEpisodes"": 2,
            ""episodes"": [{
               ""episodeID"": 60,
               ""episodeName"": ""Season 3 episode 1"",
               ""lengthMin"": 20,
               ""minWatched"": 20,
               ""date"": ""2022-04-25""
            },
            {
               ""episodeID"": 70,
               ""episodeName"": ""Season 3 episode 2"",
               ""lengthMin"": 45,
               ""minWatched"": 30,
               ""date"": ""2022 - 04 - 27""
            }]
         }]
      },
      {
         ""showName"": ""Bienvenu"",
         ""showId"": 15,
         ""showtype"": ""tvseries"",
         ""genres"": [""comedy"", ""french""],
         ""numSeasons"": 2,
         ""seriesInfo"": [{
            ""seasonNum"": 1,
            ""numEpisodes"": 2,
            ""episodes"": [{
               ""episodeID"": 20,
               ""episodeName"": ""Bonjour"",
               ""lengthMin"": 45,
               ""minWatched"": 45,
               ""date"": ""2022-03-07""
            },
            {
               ""episodeID"": 30,
               ""episodeName"": ""Merci"",
               ""lengthMin"": 42,
               ""minWatched"": 42,
               ""date"": ""2022-03-08""
            }]
         }]
      }]
   }
) RETURNING *";

await upsertData(client,upsert_row);
Console.WriteLine("Upsert data in table");

Update Data

An update statement can be used to update a row in a table.
  • The SET clause consists of two expressions: the target expression and the new-value expression. The target expression returns the items to be updated. The new-value expression may return zero or more items. If it returns an empty result, the SET is a no-op. If it returns more than one item, the items are enclosed inside a newly constructed array (this is the same as the way the SELECT clause treats multi-valued expressions in the select list)) So, effectively, the result of the new-value expression contains at most one item.
  • The WHERE clause specifies what row to update. In the current implementation, only single-row updates are allowed, so the WHERE clause must specify a complete primary key.
  • There is an optional RETURNING clause which acts the same way as the SELECT clause: it can be a "*", in which case, the full updated row will be returned, or it can have a list of expressions specifying what needs to be returned.
  • Furthermore, if no row satisfies the WHERE conditions, the update statement returns an empty result.

Example 1: Simple example to change the column values.

You are updating some column values for a given ticket number.
UPDATE BaggageInfo 
SET contactPhone = "823-384-1964", 
confNo = "LE6J4Y" 
WHERE ticketNo = 1762344493810

Example 2: Update row data and fetch the values with a RETURNING clause.

You could use the RETURNING clause to fetch back the data after the UPDATE clause is executed.
UPDATE BaggageInfo 
SET contactPhone = "823-384-1964", 
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810 RETURNING *
Output:
{"ticketNo":1762344493810,"fullName":"Adam Phillips","gender":"M","contactPhone":"823-384-1964",
"confNo":"LE6J4Y",
"bagInfo":{"bagInfo":[{"bagArrivalDate":"2019.02.02 at 03:13:00 AEDT","flightLegs":
[{"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019.02.01 at 01:13:00 EST"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019.02.01 at 00:47:00 EST"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019.01.31 at 23:38:00 EST"}],
"estimatedArrival":"2019.02.01 at 03:00:00 PST","flightDate":"2019.02.01 at 01:00:00 EST",
"flightNo":"BM604","fltRouteDest":"LAX","fltRouteSrc":"MIA"},{"actions":
[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019.02.02 at 03:15:00 AEDT"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019.02.01 at 07:35:00 PST"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019.02.01 at 07:18:00 PST"}],
"estimatedArrival":"2019.02.02 at 03:15:00 AEDT","flightDate":"2019.01.31 at 22:13:00 PST",
"flightNo":"BM667","fltRouteDest":"MEL","fltRouteSrc":"LAX"}],"id":"79039899165297",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019.02.02 at 03:13:00 AEDT","routing":"MIA/LAX/MEL","tagNum":"17657806255240"}]}}

Example 3: Modify the erroneous shopper data record in the storeAcct table.

You can use the UPDATE statement to update fields in an existing document in the JSON collection tables. The UPDATE operation works in the same way as fixed schema tables. Consider a row from the JSON collection table created for a shopping application.

{"contactPhone":"1617114988","address":{"Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"A4 sheets","priceperunit":500,"quantity":2},{"item":"Mobile Holder","priceperunit":700,"quantity":1}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"yes","orders":[{"EstDelivery":"2023-11-15","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}
Use the update clauses to correct a shopper's data as follows:
UPDATE storeAcct s
SET s.notify = "no",
REMOVE s.cart [$element.item = "A4 sheets"],
PUT s.address {"Block" : "C"},
SET s.orders[0].EstDelivery =  "2023-11-17",
ADD s.cart 1 {"item":"A3 sheets", "priceperunit":600, "quantity":2}
WHERE s.contactPhone = "1617114988"

In the above example, you update the shopper's record in the storeAcct table to correct a few inadvertent errors. This correction requires updates to various fields of the storeAcct table. The SET clause deactivates the notification setting in the shopper's data record. The REMOVE clause checks if any item field in the cart matches A4 sheets and deletes the corresponding element from the orders array. The PUT clause adds a new JSON field to indicate the landmark for delivery. The second SET clause accesses the deeply nested EstDelivery field and updates the estimated delivery date for the first item in the orders array. The ADD clause inserts a new element into the cart field to shortlist an additional item.

When you fetch the updated shopper's data, you get the following output:
{"contactPhone":"1617114988","address":{"Block":"C","Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"Mobile Holder","priceperunit":700,"quantity":1},{"item":"A3 sheets","priceperunit":600,"quantity":2}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"no","orders":[{"EstDelivery":"2023-11-17","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}
Example 4: Update account expiry date for a customer in the stream_acct table.
UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3
1 row updated

Using APIs to update data in tables

Use the Oracle NoSQL Database Java SDK to update data in a table. Download the full code ModifyData.java from the examples here.
//Update data
private static void updateRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
   handle.query(queryRequest);
   System.out.println("Updated table " + tableName);
}
/* update non-JSON data*/
String upd_stmt ="UPDATE stream_acct SET account_expiry=\"2023-12-28T00:00:00.0Z\" WHERE acct_Id=3";
updateRows(handle,upd_stmt);
Use the Oracle NoSQL Database Python SDK to update data in a table. Download the full code ModifyData.py from the examples here.
#update data
def update_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   result = handle.query(request)
   print('Data Updated in table: stream_acct')
# update non-JSON data
upd_stmt ='''UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3'''
update_data(handle,upd_stmt)
Use the Oracle NoSQL Database Go SDK to update data in a table. Download the full code ModifyData.go from the examples here.
//update data in the table
func updateRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
      PreparedStatement: &prepRes.PreparedStatement,   }
      var results []*types.MapValue
      for {
         queryRes, err := client.Query(queryReq)
  	if err != nil {
     	fmt.Printf("Upsert failed: %v\n", err)
     	return
  	}
  	res, err := queryRes.GetResults()
  	if err != nil {
     	fmt.Printf("GetResults() failed: %v\n", err)
     	return
  	}
  	results = append(results, res...)
  	if queryReq.IsDone() {
     	break
  	}
     }  
     for i, r := range results {
        fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
     }
     fmt.Printf("Updated data in the table: \n")
}
updt_stmt := "UPDATE stream_acct SET account_expiry='2023-12-28T00:00:00.0Z' WHERE acct_Id=3"
updateRows(client, err,tableName,updt_stmt)
Use the Oracle NoSQL Database Node.js SDK to update in a table. Download the full code ModifyData.js from the examples here.
/*updates data in the table*/
async function updateData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const updt_stmt = 'UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3'

await updateData(handle,updt_stmt);
console.log("Data updated in the table");
Use the Oracle NoSQL Database Dotnet SDK to update data in a table. Download the full code ModifyData.cs from the examples here.
private static async Task updateData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}
private const string updt_stmt = @"UPDATE stream_acct SET account_expiry =""2023-12-28T00:00:00.0Z"" WHERE acct_Id=3";

await updateData(client,updt_stmt);
Console.WriteLine("Data updated in the table");

Modify JSON data

While updating JSON data, in addition to WHERE, SET and RETURNING clause, the following clauses can be used..
  • The ADD clause is used to add new elements into one or more arrays. It consists of a target expression, which should normally return one or more array items, an optional position expression, which specifies the position within each array where the new elements should be placed, and a new-elements expression that returns the new elements to insert.
  • The PUT clause is used primarily to add new fields to a JSON document. It consists of a target expression, which should normally return one or more fields to be inserted into the target JSON document.
  • The REMOVE clause consists of a single target expression, which computes the items to be removed.

Example 1: Update table and add data in a JSON object

Add elements to the action array (at a given array element) for a particular flight Leg of a passenger. By default, the element is added at the end. If a number is specified, it is inserted in that position. In the example below, you want the new element to be added in the 2nd position.
UPDATE BaggageInfo bag
ADD bag.bagInfo[0].flightLegs[0].actions 2  {"actionAt" : "LAX",
         "actionCode" : "WAITING at LAX",
         "actionTime" : "2019-02-01T06:13:00Z"}
WHERE ticketNo=1762344493810
RETURNING *

Example 2: Update table and update data from a JSON object.

You could update the data from a JSON object using the SET clause. Here the second element of the actions array is updated with new values for a given ticket number.
UPDATE BaggageInfo bag
SET bag.bagInfo[0].flightLegs[0].actions[2]=
{"actionAt" : "LAX",
"actionCode" : "STILL WAITING at LAX",
"actionTime" : "2019-02-01T06:15:00Z"}
WHERE ticketNo=1762344493810 RETURNING *

Example 3: Update table and remove data in a JSON object.

You can use the REMOVE clause to remove a given element from an array. You need to specify which element of the array needs to be removed using the index of the array.
UPDATE BaggageInfo bag
REMOVE bag.bagInfo[0].flightLegs[0].actions[1]
WHERE ticketNo=1762344493810
RETURNING *

Example 4: Update stream_acct table and add and remove data in a JSON object.

In the stream_acct table, for a customer you can add the details of a particular series episode of a show using the ADD clause in the UPDATE statement.
UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
     "date" : "2022-04-26",
     "episodeID" : 43,
     "episodeName" : "Season 2 episode 2",
     "lengthMin" : 45,
     "minWatched" : 45} WHERE acct_Id=2 RETURNING *
Similarly , you can remove the details of a particular series episode of a show using the REMOVE clause in the UPDATE statement.
UPDATE stream_acct acct1 
REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] 
WHERE acct_Id=2 RETURNING *

Using APIs to update JSON data in tables

Use the Oracle NoSQL Database Java SDK to add and remove data in a JSON object in the stream_acct table. Download the full code ModifyData.java from the examples here.
//Update data
private static void updateRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
   handle.query(queryRequest);
   System.out.println("Updated table " + tableName);
}
/* update JSON data and add a node*/
String upd_json_addnode="UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes 
   {\"date\" : \"2022-04-26\","+
   "\"episodeID\" : 43,"+
   "\"episodeName\" : \"Season 2 episode 2\","+
   "\"lengthMin\" : 45,"+
   "\"minWatched\" : 45} WHERE acct_Id=2 RETURNING *";
updateRows(handle,upd_json_addnode);

/* update JSON data and remove a node*/
String upd_json_delnode="UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] WHERE acct_Id=2 RETURNING *";
updateRows(handle,upd_json_delnode);
Use the Oracle NoSQL Database Python SDK to add and remove data in a JSON object in the stream_acct table. Download the full code ModifyData.py from the examples here.
#update data
def update_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   result = handle.query(request)
   print('Data Updated in table: stream_acct')
# update JSON data and add a node
upd_json_addnode = '''UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
     "date" : "2022-04-26",
     "episodeID" : 43,
     "episodeName" : "Season 2 episode 2",
     "lengthMin" : 45,
     "minWatched" : 45} WHERE acct_Id=2 RETURNING *'''
update_data(handle,upd_json_addnode)

# update JSON data and delete a node
upd_json_delnode = '''UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] WHERE acct_Id=2 RETURNING *'''
update_data(handle,upd_json_delnode)
Use the Oracle NoSQL Database Go SDK to add and remove data in a JSON object in the stream_acct table. Download the full code ModifyData.go from the examples here.
//update data in the table
func updateRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
      PreparedStatement: &prepRes.PreparedStatement,   }
      var results []*types.MapValue
      for {
         queryRes, err := client.Query(queryReq)
  	if err != nil {
     	fmt.Printf("Upsert failed: %v\n", err)
     	return
  	}
  	res, err := queryRes.GetResults()
  	if err != nil {
     	fmt.Printf("GetResults() failed: %v\n", err)
     	return
  	}
  	results = append(results, res...)
  	if queryReq.IsDone() {
     	break
  	}
     }  
     for i, r := range results {
        fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
     }
     fmt.Printf("Updated data in the table: \n")
}
upd_json_addnode := `UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
	   "date" : "2022-04-26",
	   "episodeID" : 43,
	   "episodeName" : "Season 2 episode 2",
	   "lengthMin" : 45,
	   "minWatched" : 45} WHERE acct_Id=2 RETURNING *`
updateRows(client, err,tableName,upd_json_addnode)

upd_json_delnode := `UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] 
WHERE acct_Id=2 RETURNING *`
updateRows(client, err,tableName,upd_json_delnode)
Use the Oracle NoSQL Database Node.js SDK to add and remove data in a JSON object in the stream_acct table. Download the full code ModifyData.js from the examples here.
/*updates data in the table*/
async function updateData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const upd_json_addnode = 
`UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
   "date" : "2022-04-26",
   "episodeID" : 43,
   "episodeName" : "Season 2 episode 2",
   "lengthMin" : 45,
   "minWatched" : 45} WHERE acct_Id=2 RETURNING *`
await updateData(handle,upd_json_addnode);
console.log("New data node added in the table");

const upd_json_delnode = 
'UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] 
WHERE acct_Id=2 RETURNING *'
await updateData(handle,upd_json_delnode);
console.log("New Data node removed from the table");
Use the Oracle NoSQL Database Dotnet SDK to add and remove data in a JSON object in the stream_acct table. Download the full code ModifyData.cs from the examples here.
private static async Task updateData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}
private const string upd_json_addnode = 
@"UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
        ""date"" : ""2022-04-26"",
        ""episodeID"" : 43,
        ""episodeName"" : ""Season 2 episode 2"",
        ""lengthMin"" : 45,
        ""minWatched"" : 45} WHERE acct_Id=2 RETURNING *";

await updateData(client,upd_json_addnode);
Console.WriteLine("New data node added in the table");

private const string upd_json_delnode = 
"UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] 
WHERE acct_Id=2 RETURNING *";
await updateData(client,upd_json_delnode);
Console.WriteLine("New Data node removed from the table");

Delete Data

The DELETE statement is used to remove from a table a set of rows satisfying a condition. The condition is specified in a WHERE clause that behaves the same way as in the SELECT expression. The result of the DELETE statement depends on whether a RETURNING clause is present or not. Without a RETURNING clause the DELETE returns the number of rows deleted. Otherwise, for each deleted row the expressions following the RETURNING clause are computed the same way as in the SELECT clause and the result is returned to the application.

Example 1: Delete data from a table with a simple WHERE clause.

You delete the data corresponding to a user with a given fullname.
DELETE FROM BaggageInfo 
WHERE fullName = "Bonnie Williams"

Example 2: Delete data from a table with a RETURNING clause.

The RETURNING clause fetches the details of the row to be deleted. In the example below, you are fetching the full name and conf number corresponding to a ticket number which will be deleted.
DELETE FROM BaggageInfo 
WHERE ticketNo = 1762392196147 
RETURNING fullName,confNo
Output:
{"fullName":"Birgit Naquin","confNo":"QD1L0T"}

Note:

If any error occurs during the execution of a DELETE statement, there is a possibility that some rows will be deleted and some not. The system does not keep track of what rows got deleted and what rows are not yet deleted. This is because Oracle NoSQL Database focuses on low latency operations. Long-running operations across shards are not coordinated using a two-phase commit and lock mechanism. In such cases, it is recommended that the application re-run the DELETE statement.
Example 3: Delete data from stream_acct table based on the last name.
DELETE FROM stream_acct acct1 
WHERE acct1.acct_data.firstName="Adelaide"
 AND acct1.acct_data.lastName="Willard"

Using APIs to delete data from tables

Use the Oracle NoSQL Database Java SDK to delete data from a table. Download the full code ModifyData.java from the examples here.
//delete row based on primary KEY
private static void delRow(NoSQLHandle handle, MapValue m1) throws Exception {
   DeleteRequest delRequest = new DeleteRequest().setKey(m1).setTableName(tableName);
   DeleteResult del = handle.delete(delRequest);
   if (del.getSuccess()) {
     System.out.println("Delete succeed");
   }
   else {
     System.out.println("Delete failed");
   }
}

//delete rows based on a filter condition
private static void deleteRows(NoSQLHandle handle, String sqlstmt) throws Exception {
   QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
   handle.query(queryRequest);
   System.out.println("Deleted row(s) from table " + tableName);
}
/*delete a single row*/
MapValue m1= new MapValue();
m1.put("acct_Id",1);
delRow(handle,m1);
String del_stmt ="DELETE FROM stream_acct acct1 WHERE acct1.acct_data.firstName=\"Adelaide\" AND acct1.acct_data.lastName=\"Willard\"";
/*delete rows based on a filter condition*/
deleteRows(handle,del_stmt);
Use the Oracle NoSQL Database Python SDK to delete data from a table. Download the full code ModifyData.py from the examples here.
#del row with a primary KEY
def del_row(handle,table_name):
   request = DeleteRequest().set_key({'acct_Id': 1}).set_table_name(table_name)
   result = handle.delete(request)
   print('Deleted data from table: stream_acct')

#del row(s) with a filter condition
def delete_rows(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   result = handle.query(request)
   print('Deleted data from table: stream_acct')
# delete row based on primary key
del_row(handle,'stream_acct')
# delete data based on a filter condition
del_stmt ='''DELETE FROM stream_acct acct1 WHERE acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"'''
delete_rows(handle,del_stmt)
Use the Oracle NoSQL Database Go SDK to delete data from a table. Download the full code ModifyData.go from the examples here.
//delete with primary key
func delRow(client *nosqldb.Client, err error, tableName string)(){
   key := &types.MapValue{}
   key.Put("acct_Id",1)
   delReq := &nosqldb.DeleteRequest{
   	TableName: tableName,
   	Key:       key,
   }
   delRes, err := client.Delete(delReq)
   if err != nil {
	fmt.Printf("failed to delete a row: %v", err)
	return
   }
   if delRes.Success {
	fmt.Println("Delete succeeded")
   }
}

//delete rows based on a filter condition
func deleteRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
	PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
	  fmt.Printf("Upsert failed: %v\n", err)
	  return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
	  return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
	fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
   fmt.Printf("Deleted data from the table: %v\n",tableName)
}
delRow(client, err,tableName)
delete_stmt := `DELETE FROM stream_acct acct1 WHERE acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"`
deleteRows(client, err,tableName,delete_stmt)
Use the Oracle NoSQL Database Node.js SDK to delete data from a table. Download the full code ModifyData.js from the examples here.
/*delete row based on primary key*/
async function delRow(handle) {
   try {
      /* Unconditional delete, should succeed.*/
      var result = await handle.delete(TABLE_NAME, { acct_Id: 1 });
      /* Expected output: delete succeeded*/
      console.log('delete ' + result.success ? 'succeeded' : 'failed');
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
/*deletes data based on a filter conditioin */
async function deleteRows(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
await delRow(handle);
console.log("Row deleted based on primary key");
await deleteRows(handle,del_stmt);
console.log("Rows deleted");
Use the Oracle NoSQL Database Dotnet SDK to delete data from a table. Download the full code ModifyData.cs from the examples here.
private static async Task delRow(NoSQLClient client){
   var primaryKey = new MapValue
   {
      ["acct_Id"] = 1
   };
   // Unconditional delete, should succeed.
   var deleteResult = await client.DeleteAsync(TableName, primaryKey);
   // Expected output: Delete succeeded.
   Console.WriteLine("Delete {0}.",deleteResult.Success ? "succeeded" : "failed");
}

private static async Task deleteRows(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}
await delRow(client);
Console.WriteLine("Row deleted based on primary key");
await deleteRows(client,del_stmt);
Console.WriteLine("Rows removed from the table");