Examples using QueryRequest API

You can use QueryRequest API to group and order data and also fetch it from a NoSQL table.

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

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

Download the full code GroupSortData.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 orderby_stmt="SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag "+
                             "WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
System.out.println("Using ORDER BY to sort data ");
fetchRows(handle,orderby_stmt);
String sortlimit_stmt="SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag, "+
                       "$bag.bagInfo[].lastSeenTimeGmt $flt_time WHERE $bag.bagInfo[].lastSeenStation=any \"JTR\""+
                       "ORDER BY $flt_time LIMIT 4";
System.out.println("Using ORDER BY and LIMIT to sort and limit data ");
fetchRows(handle,sortlimit_stmt);
String groupsortlimit_stmt="SELECT $flt_src as SOURCE,count(*) as COUNT FROM BaggageInfo $bag, "+
                           "$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src";
System.out.println("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data ");
fetchRows(handle,groupsortlimit_stmt);

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

Download the full code GroupSortData.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))  

orderby_stmt = '''SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
                     WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC'''
print('Using ORDER BY to sort data:')
fetch_data(handle,orderby_stmt)

sortlimit_stmt = '''SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
                    $bag.bagInfo[].lastSeenTimeGmt $flt_time
                    WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
                    ORDER BY $flt_time LIMIT 4'''
print('Using ORDER BY and LIMIT to sort and limit data:')
fetch_data(handle,sortlimit_stmt)

groupsortlimit_stmt = '''SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
                         $bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src'''
print('Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data:')
fetch_data(handle,groupsortlimit_stmt)   

To execute a query use the Client.Query function.

Download the full code GroupSortData.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()))
   }
} 

orderby_stmt := `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
                    WHERE bag.bagInfo[].lastSeenStation=any "MEL" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
fmt.Printf("Using ORDER BY to sort data::\n")
fetchData(client, err,tableName,orderby_stmt)

sortlimit_stmt := `SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
                   $bag.bagInfo[].lastSeenTimeGmt $flt_time
                   WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
                   ORDER BY $flt_time LIMIT 4`
fmt.Printf("Using ORDER BY and LIMIT to sort and limit data::\n")
fetchData(client, err,tableName,sortlimit_stmt)

groupsortlimit_stmt := `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
                        $bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
fmt.Printf("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data::\n")
fetchData(client, err,tableName,groupsortlimit_stmt)

To execute a query use query method.

JavaScript: Download the full code GroupSortData.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 GroupSortData.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: 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 orderby_stmt = `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
                      WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
console.log("Using ORDER BY to sort data");
await fetchData(handle,orderby_stmt);

const sortlimit_stmt = `SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
                        $bag.bagInfo[].lastSeenTimeGmt $flt_time
                        WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
                        ORDER BY $flt_time LIMIT 4`
console.log("Using ORDER BY and LIMIT to sort and limit data");
await fetchData(handle,sortlimit_stmt);

const groupsortlimit_stmt = `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
                             $bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
console.log("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data");
await fetchData(handle,groupsortlimit_stmt);

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

Download the full code GroupSortData.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 orderby_stmt =@"SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt 
                                             FROM BaggageInfo bag WHERE bag.bagInfo[].lastSeenStation=any ""MEL"" 
                                             ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
Console.WriteLine("\nUsing ORDER BY to sort data!");
await fetchData(client,orderby_stmt);

private const string sortlimit_stmt =@"SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
                                             $bag.bagInfo[].lastSeenTimeGmt $flt_time
                                             WHERE $bag.bagInfo[].lastSeenStation=any ""JTR""
                                             ORDER BY $flt_time LIMIT 4";
Console.WriteLine("\nUsing ORDER BY and LIMIT to sort and limit data!");
await fetchData(client,sortlimit_stmt);


private const string groupsortlimit_stmt =@"SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
                                                  $bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src" ;
Console.WriteLine("\nUsing GROUP BY, ORDER BY and LIMIT to group, sort and limit data:");
await fetchData(client,groupsortlimit_stmt);