Previous Next JavaScript must be enabled to correctly display this content

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