Using APIs to modify or delete table data

Using API to upsert data

You can use the UPSERT SQL command in the Query request to update or insert data.

To execute your query, you use the NoSQLHandle.query() API.

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);

To execute your query use the borneo.NoSQLHandle.query() method.

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)

To execute a query use the Client.Query function.

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)

You can use the UPSERT SQL command in the Query request to update or insert data. To execute a query use query method.

JavaScript: 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);
   }
}

TypeScript: Download the full code ModifyData.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

async function upsertData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(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");

You can use the UPSERT SQL command in the Query request to update or insert data. To execute a query, you can use QueryAsync method or GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

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

Using API to update data

You can use the UPDATE SQL command in the Query request to update data.

To execute your query, you use the NoSQLHandle.query() API.

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);

To execute your query use the borneo.NoSQLHandle.query() method.

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)

To execute a query use the Client.Query function.

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)

To execute a query use query method.

JavaScript: 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);
   }
}

TypeScript: Download the full code ModifyData.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

async function updateData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(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 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");

You can use the UPDATE SQL command in the Query request to update data. To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

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

Using API to update JSON data

You can use the UPDATE SQL command to add and remove data in a JSON object in your table.

To execute your query, you use the NoSQLHandle.query() API.

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);

To execute your query use the borneo.NoSQLHandle.query() method.

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)

To execute a query use the Client.Query function.

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)

To execute a query use query method.

JavaScript: 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);
   }
}

TypeScript: Download the full code ModifyData.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

async function updateData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(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 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");

You can use the UPDATE SQL command to add and remove data in a JSON object in your table. To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

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

Using Query API to delete data

You can use the QueryRequest API and delete one or more rows from a NoSQL table that satisfy a filter condition.

You can use the DELETE SQL command in the Query request to delete data. To execute your query, you use the NoSQLHandle.query() API.

Download the full code ModifyData.java from the examples here.


//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);
}


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);

You can use the DELETE SQL command in the Query request to delete data. To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code ModifyData.py from the examples here.


#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 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)

You can use the DELETE SQL command in the Query request to delete data. To execute a query use the Client.Query function.

Download the full code ModifyData.go from the examples here.


//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)
}

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)

You can use the DELETE SQL command in the Query request to delete data. To execute a query use query method.

JavaScript: Download the full code ModifyData.js from the examples here.

/*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 deleteRows(handle,del_stmt);
console.log("Rows deleted");

TypeScript: Download the full code ModifyData.ts from the examples here.

async function deleteRows(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(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);
   }
}

await deleteRows(handle,del_stmt);
console.log("Rows deleted");

You can use the DELETE SQL command in the Query request to delete data. To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code ModifyData.cs from the examples here.

private static async Task deleteRows(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}

await deleteRows(client,del_stmt);
Console.WriteLine("Rows removed from the table");

Using API to delete a single row

You can use the DeleteRequest API and delete a single row using a primary key.

The DeleteRequest API can be used to perform unconditional and conditional deletes.

Delete any existing row. This is the default.
Succeed only if the row exists and its version matches a specific version. Use setMatchVersion for this case.

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 a single row*/
MapValue m1= new MapValue();
m1.put("acct_Id",1);
delRow(handle,m1);

Single rows are deleted using borneo.DeleteRequest using a primary key value.

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')

# delete row based on primary key
del_row(handle,'stream_acct')

The DeleteRequest is used to delete a row from a table. The row is identified using a primary key specified in DeleteRequest.Key.

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

delRow(client, err,tableName)

Use the delete method to delete a row from a table. For method details, see NoSQLClient class.

You must pass the table name and primary key of the row. In addition, you can make the delete operation conditional by specifying a RowVersion of the row that was previously returned by get or put methods.

JavaScript: 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);
   }
}

await delRow(handle);
console.log("Row deleted based on primary key");

TypeScript: Download the full code ModifyData.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

/*delete row based on primary key*/
async function delRow(handle: NoSQLClient) {
   try {
      /* Unconditional delete, should succeed.*/
      var result = await handle.delete<StreamInt>(TABLE_NAME, { acct_Id: 1 });
      /* Expected output: delete succeeded*/
      console.log('delete ' + result.success ? 'succeeded' : 'failed');
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

await delRow(handle);
console.log("Row deleted based on primary key");

To delete a row, use DeleteAsync method. Pass to it the table name and primary key of the row to delete. This method takes the primary key as MapValue. The field names should be the same as the table primary key column names.

DeleteAsync and DeleteIfVersionAsync methods return Task<DeleteResult<RecordValue>>. DeleteResult instance contains success status of the Delete operation. Delete operation may fail if the row with given primary key does not exist or this is a conditional Delete and provided row version did not match the existing row version.

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

await delRow(client);
Console.WriteLine("Row deleted based on primary key");

Using API to delete multiple rows

You can use the MultiDeleteRequest API and delete more than one row from a NoSQL table.

You can use MultiDeleteRequest to delete multiple rows from a table in an atomic operation. The key used may be partial but must contain all of the fields that are in the shard key. A range may be specified to delete a range of keys. As this operation can exceed the maximum amount of data that can be modified in a single operation, a continuation key can be used to continue the operation.

If a table's primary key is <YYYYMM, timestamp> and the its shard key is the YYYYMM, then all records that hit in the same month would be in same shard. It is possible to delete a range of timestamp values for a specific month using MultiDeleteRequest class.

See Oracle NoSQL Java SDK API Reference for more details on the various classes and methods.

Download the full code MultiDataOps.java from the examples here.

//Delete multiple rows from the table
private static void delMulRows(NoSQLHandle handle,int pinval) throws Exception {
   MapValue key = new MapValue().put("pin", 1234567);
   MultiDeleteRequest multiDelRequest = new MultiDeleteRequest()
          .setKey(key)
          .setTableName(tableName);

   MultiDeleteResult mRes = handle.multiDelete(multiDelRequest);
   System.out.println("MultiDelete result = " + mRes);
}


/*delete multiple rows using shard key*/
delMulRows(handle,1234567);

You can use borneo.MultiDeleteRequest class to perform multiple deletes in a single atomic operation.

See Oracle NoSQL Python SDK API Reference for more details on the various classes and methods.

Download the full code MultiDataOps.py from the examples here.

#delete multiple rows
def multirow_delete(handle,table_name,pinval):
   request = MultiDeleteRequest().set_table_name(table_name).set_key({'pin': pinval})
   result = handle.multi_delete(request)
)

/*delete multiple rows using shard key*/
multirow_delete(handle,'examplesAddress',1234567)

You can use MultiDelete method to delete multiple rows from a table in a single atomic operation.

See Oracle NoSQL Go SDK API Reference for more details on the various classes and methods.

Download the full code MultiDataOps.go from the examples here.

//delete multiple rows
func delMulRows(client *nosqldb.Client, err error, tableName string,pinval int)(){
   shardKey := &types.MapValue{}
   shardKey.Put("pin", pinval)
   multiDelReq := &nosqldb.MultiDeleteRequest{
		TableName: tableName,
		Key:       shardKey,
   }
   multiDelRes, err := client.MultiDelete(multiDelReq)
   if err != nil {
      fmt.Printf("failed to delete multiple rows: %v", err)
      return
   }
   fmt.Printf("MultiDelete result=%v\n", multiDelRes)
}

/*delete multiple rows using shard key*/
delMulRows(client, err,tableName,1234567)

You can delete multiple rows having the same shard key in a single atomic operation using the deleteRange method.

JavaScript: Download the full code MultiDataOps.js from the examples here.

//deletes multiple rows
async function mulRowDel(handle,pinval){
   try {
      /* Unconditional delete, should succeed.*/
      var result = await handle.deleteRange(TABLE_NAME, { pin: pinval });
      /* Expected output: delete succeeded*/
      console.log('delete ' + result.success ? 'succeeded' : 'failed');
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

/*delete multiple rows using shard key*/
await mulRowDel(handle,1234567);

TypeScript: Download the full code MultiDataOps.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

//deletes multiple rows
async function mulRowDel(handle: NoSQLClient,pinVal: Integer){
   try {
      /* Unconditional delete, should succeed.*/
      var result = await handle.deleteRange<StreamInt>(TABLE_NAME, { pin: pinval });
      /* Expected output: delete succeeded*/
      console.log('delete ' + result.success ? 'succeeded' : 'failed');
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

/*delete multiple rows using shard key*/
await mulRowDel(handle,1234567);

You can delete multiple rows having the same shard key in a single atomic operation using DeleteRangeAsync method.

Download the full code MultiDataOps.cs from the examples here.

//delete multiple rows
private static async Task mulDelRows(NoSQLClient client,int pinval){
   var parKey = new MapValue {["pin"] = pinval};
   var options = new DeleteRangeOptions();
   do
   {
      var result = await client.DeleteRangeAsync(TableName,parKey,options);
      Console.WriteLine($"Deleted {result.DeletedCount} row(s)");
      options.ContinuationKey = result.ContinuationKey;
   } while(options.ContinuationKey != null);
}

/*delete multiple rows using shard key*/
await mulDelRows(client,1234567);