Examples using QueryRequest API

You can use QueryRequest API and apply SQL functions to fetch data from a NoSQL table.

• Java
• Python
• Go
• Node.js
• C#

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

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

 //Fetch rows from the table
private static void fetchRows(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 string_func1="SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag WHERE ticketNo=1762376407826";
System.out.println("Using substring function ");
fetchRows(handle,string_func1);
String string_func2="SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo WHERE ticketNo=1762320369957";
System.out.println("Using length function ");
fetchRows(handle,string_func2);
String string_func3="SELECT fullname FROM baggageInfo bag WHERE EXISTS bag.bagInfo[contains($element.routing,\"SFO\")]";
System.out.println("Using contains function ");
fetchRows(handle,string_func3);

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

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

# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))  

 string_func1 = '''SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                     WHERE ticketNo=1762376407826'''
 print('Using substring function:')
 fetch_data(handle,string_func1)

 string_func2 = '''SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                     WHERE ticketNo=1762320369957'''
 print('Using length function:')
 fetch_data(handle,string_func2)

 string_func3 = '''SELECT fullname FROM baggageInfo bag WHERE
                     EXISTS bag.bagInfo[contains($element.routing,"SFO")]'''
 print('Using contains function:')
 fetch_data(handle,string_func3)

To execute a query use the Client.Query function.

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

 //fetch data from the table
func fetchData(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("Query 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()))
   }
} 

string_func1 := `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                    WHERE ticketNo=1762376407826`
fmt.Printf("Using substring function:\n")
fetchData(client, err,tableName,string_func1)

string_func2 := `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                    WHERE ticketNo=1762320369957`
fmt.Printf("Using length function:\n")
fetchData(client, err,tableName,string_func2)

string_func3 := `SELECT fullname FROM baggageInfo bag WHERE
                    EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
fmt.Printf("Using contains function:\n")
fetchData(client, err,tableName,string_func3)

To execute a query use query method.

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

  //fetches data from the table
async function fetchData(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 SQLFunctions.ts from the examples here.

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

/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: any) {
   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 string_func1 = `SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                      WHERE ticketNo=1762376407826`
console.log("Using substring function:");
await fetchData(handle,string_func1);

const string_func2 = `SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                      WHERE ticketNo=1762320369957`
console.log("Using length function");
await fetchData(handle,string_func2);

const string_func3 = `SELECT fullname FROM baggageInfo bag WHERE
                      EXISTS bag.bagInfo[contains($element.routing,"SFO")]`
console.log("Using contains function");
await fetchData(handle,string_func3);

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

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

private static async Task fetchData(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 string_func1 =@"SELECT substring(bag.baginfo.routing,0,3) AS Source FROM baggageInfo bag
                                           WHERE ticketNo=1762376407826" ;
Console.WriteLine("\nUsing substring function!");
await fetchData(client,string_func1);

private const string string_func2 =@"SELECT fullname, length(fullname) AS fullname_length FROM BaggageInfo
                                           WHERE ticketNo=1762320369957";
Console.WriteLine("\nUsing length function!");
await fetchData(client,string_func2);


private const string string_func3 =@"SELECT fullname FROM baggageInfo bag WHERE
                                           EXISTS bag.bagInfo[contains($element.routing,""SFO"")]";
Console.WriteLine("\nUsing contains function!");
await fetchData(client,string_func3);