Previous Next JavaScript must be enabled to correctly display this content

## Using API

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