Previous Next JavaScript must be enabled to correctly display this content

## 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 `query`one 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 `queryIterable`method, 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.