1. Developers Guide
  2. Develop
  3. Simple SELECT queries
  4. Using Query API to fetch data

Using Query API to fetch data

You can use the QueryRequest to construct queries to filter data from your NoSQL table.

To execute your query, you use the NoSQLHandle.query() API. See Oracle NoSQL Java SDK API Reference for more details on the various classes and methods.

There are two ways to get the results of a query: using an iterator or loop through partial results.
  • Iterator: Use NoSQLHandle.queryIterable(QueryRequest) to get an iterable that contains all the results.
  • Partial Results: To compute and retrieve the full result set of a query, the same QueryRequest instance will, in general, have to be executed multiple times (via NoSQLHandle.query(oracle.nosql.driver.ops.QueryRequest). Each execution returns a QueryRequest, which contains a subset of the result set. 
String sqlstmt_allrows="SELECT * FROM stream_acct";
private static void fetchRows(NoSQLHandle handle,String sqlstmt) 
                                              throws Exception {
   try (
      QueryRequest queryRequest = 
            new QueryRequest().setStatement(sqlstmt_allrows);

      QueryIterableResult results = 
            handle.queryIterable(queryRequest)){

      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
}
You can also apply filter conditions using the WHERE clause in the query. 
String sqlstmt_allrows=
"SELECT account_expiry, acct.acct_data.lastName, 
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct WHERE acct_id=1";
Download the full code QueryData.java from the examples here.

Note:

To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.java from the examples to understand how to fetch data from a parent-child table here.

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

There are two ways to get the results of a query: using an iterator or loop through partial results.
  • Use borneo.NoSQLHandle.query_iterable() to get an iterable that contains all the results of a query.
  • You can loop through partial results by using the borneo.NoSQLHandle.query() method. For example, to execute a SELECT query to read data from your table, a borneo.QueryResult contains a list of results. And if the borneo.QueryRequest.is_done() returns False, there may be more results, so queries should generally be run in a loop. It is possible for single request to return no results but the query still not done, indicating that the query loop should continue. 
sqlstmt = 'SELECT * FROM stream_acct'

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))
You can also apply filter conditions using the WHERE clause in the query. 
sqlstmt = 'SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct WHERE acct_id=1'
Download the full code QueryData.py from the examples here.

Note:

To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.py from the examples to understand how to fetch data from a parent-child table here.

To execute a query use the Client.Query function. When execute on the cloud service, the amount of data read by a single query request is limited by a system default and can be further limited using QueryRequest.MaxReadKB. This limits the amount of data read and not the amount of data returned, which means that a query can return zero results but still have more data to read. For this reason queries should always operate in a loop, acquiring more results, until QueryRequest.IsDone() returns true, indicating that the query is done. 

querystmt := "select * FROM stream_acct"

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()))
   }
}
You can also apply filter conditions using the WHERE clause in the query. 
querystmt := "SELECT account_expiry, acct.acct_data.lastName, 
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct where acct_id=1"
Download the full code QueryData.go from the examples here.

Note:

To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.go from the examples to understand how to fetch data from a parent-child table here.
You can query data from the NoSQL tables using one of these methods. For method details, see NoSQLClient class.
  1. Use the query method to execute a query. This method returns a Promise of QueryResult,which is a plain JavaScript object containing an array of resulting rows as well as a continuation key. You can use the query method in two ways:
    • You can call the query method only once for queries that access at most one row. These queries can only include select statements based on the primary key (the where clause must specify equality based on the complete primary key). In all other cases, you can use either query in a loop or queryIterable method.
    • You can call the query method in a loop to retrieve multiple rows. As the amount of data returned by a query is limited by the system default and can be further limited by setting the maxReadKB property in the QueryOpt argument of the queryone invocation of the query method can't return all the available results. To address this issue, run the query in a loop until the continuationKey in QueryResult becomes null/undefined.
  2. Iterate over the query results using the queryIterable method. This method returns an iterable object that you can iterate over with a for-await-of loop. You need not manage the continuation in this method.

    Note:

    With the queryIterablemethod, you can also use the QueryOpt argument with properties other than continuationKey.
JavaScript: Download the full code QueryData.js from the examples here.
const querystmt = 'SELECT * FROM stream_acct';

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);
    }
}
You can also apply filter conditions using the WHERE clause in the query. 
const querystmt = 
'SELECT account_expiry, acct.acct_data.lastName, 
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct WHERE acct_id=1';
TypeScript:You can use the same methods described in JavaScript above for TypeScript. You can also supply an optional query result schema as the type parameter to the query method to provide type hints for the rows returned in the QueryResult. This need not be the same as table row schema (unless using SELECT * query) as the query can include projections, name aliases, aggregate values, and so forth. Download the full code QueryData.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);
   }
}
You can also apply filter conditions using the WHERE clause in the query. 
const querystmt = 
'SELECT account_expiry, acct.acct_data.lastName, 
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct WHERE acct_id=1';

Note:

To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full JavaScript code TableJoins.js here and the full TypeScript code TableJoins.ts here to understand how to fetch data from a parent-child table.

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable. You may pass options to each of these methods as QueryOptions. QueryAsync method return Task<QueryResult<RecordValue>>. QueryResult contains query results as a list of RecordValue instances, as well as other information. When your query specifies a complete primary key, it is sufficient to call QueryAsync once. The amount of data returned by the query is limited by the system. It could also be further limited by setting MaxReadKB property of QueryOptions. This means that one invocation of QueryAsync may not return all available results. This situation is dealt with by using continuation key. Non-null continuation key in QueryResult means that more query results may be available. This means that queries should run in a loop, looping until the continuation key becomes null. See Oracle NoSQL Dotnet SDK API Reference for more details of all classes and methods. 

private const string querystmt ="SELECT * FROM stream_acct";

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

//function to display result
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());
      }
   }
}
You can also apply filter conditions using the WHERE clause in the query. 
private const string querystmt =
"SELECT account_expiry, acct.acct_data.lastName, 
acct.acct_data.contentStreamed[].showName 
FROM stream_acct acct WHERE acct_id=1";
Download the full code QueryData.cs from the examples here.

Note:

To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.cs from the examples to understand how to fetch data from a parent-child table here.