Previous Next JavaScript must be enabled to correctly display this content

## SQL Operators examples using QueryRequest API

You can use `QueryRequest` API and filter data from a NoSQL table using SQL operators.

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

Download the full code SQLOperators.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 seq_comp_ope="SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
    System.out.println("Using Sequence Comparison operator ");
    fetchRows(handle,seq_comp_ope);
    String logical_ope="SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,bag.bagInfo[].flightLegs[].fltRouteSrc FROM BaggageInfo bag WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any \"SFO\"";
    System.out.println("Using Logical operator ");
    fetchRows(handle,logical_ope);
    String value_comp_ope="SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=\"M\"";
    System.out.println("Using Value Comparison operator ");
    fetchRows(handle,value_comp_ope);
    String in_ope="SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet WHERE bagdet.fullName IN (\"Lucinda Beckman\", \"Adam Phillips\",\"Dierdre Amador\",\"Fallon Clements\")";System.out.println("Using IN operator ");fetchRows(handle,in_ope);
    String exists_ope="SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE EXISTS bag.bagInfo[$element.bagArrivalDate >=\"2019-03-01T00:00:00\"]";
    System.out.println("Using EXISTS operator ");
    fetchRows(handle,exists_ope);

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

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

    seqcomp_stmt = '''SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                         FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
    print('Using Sequence Comparison operator:')
    fetch_data(handle,seqcomp_stmt)
    logope_stmt = '''SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                     bag.bagInfo[].flightLegs[].fltRouteSrc
                     FROM BaggageInfo bag
                     WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"'''
    print('Using Logical operator:')
    fetch_data(handle,logope_stmt)
    valcomp_stmt = '''SELECT fullname, bag.bagInfo[].routing
                      FROM BaggageInfo bag WHERE gender="M"'''
    print('Using Value Comparison operator:')
    fetch_data(handle,valcomp_stmt)
    inope_stmt = '''SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                    FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                   ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")'''
    print('Using IN operator:')
    fetch_data(handle,inope_stmt)
    existsope_stmt = '''SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                        EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]'''
    print('Using EXISTS operator:')
    fetch_data(handle,existsope_stmt)

To execute a query use the `Client.Query` function.

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

    seqcomp_stmt := `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                        FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
    fmt.Printf("Using Sequence Comparison operator:\n"
    fetchData(client, err,tableName,seqcomp_stmt)

    logope_stmt := `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                    bag.bagInfo[].flightLegs[].fltRouteSrc
                    FROM BaggageInfo bag
                    WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
    fmt.Printf("Using Logical operator:\n")
    fetchData(client, err,tableName,logope_stmt)

    valcomp_stmt := `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
    fmt.Printf("Using Value Comparison operator:\n")
    fetchData(client, err,tableName,valcomp_stmt)

    inope_stmt := `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum FROM BaggageInfo bagdet
    	        WHERE bagdet.fullName IN ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
    fmt.Printf("Using IN operator:\n")
    fetchData(client, err,tableName,inope_stmt)

    existsope_stmt := `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                       EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
    fmt.Printf("Using EXISTS operator:\n")
    fetchData(client, err,tableName,existsope_stmt)

To execute a query use `query` method.

JavaScript: Download the full code SQLOperators.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 SQLOperators.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 seqcomp_stmt = `SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                          FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
    console.log("Using Sequence Comparison operator");
    await fetchData(handle,seqcomp_stmt);

    const logope_stmt = `SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                         bag.bagInfo[].flightLegs[].fltRouteSrc
                         FROM BaggageInfo bag
                         WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"`
    console.log("Using Logical operator");
    await fetchData(handle,logope_stmt);

    const valcomp_stmt = `SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender="M"`
    console.log("Using Value Comparison operator");
    await fetchData(handle,valcomp_stmt);

    const inope_stmt = `SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                        FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                        ("Lucinda Beckman", "Adam Phillips","Dierdre Amador","Fallon Clements")`
    console.log("Using IN operator");
    await fetchData(handle,inope_stmt);

    const existsope_stmt = `SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                            EXISTS bag.bagInfo[$element.bagArrivalDate >="2019-03-01T00:00:00"]`
    console.log("Using EXISTS operator");
    await fetchData(handle,existsope_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 SQLOperators.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 seqcomp_stmt =@"SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc
                                               FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
    Console.WriteLine("\nUsing Sequence Comparison operator!");
    await fetchData(client,seqcomp_stmt);

    private const string logope_stmt =@"SELECT fullName, bag.bagInfo[].tagNum, bag.bagInfo[].routing,
                                               bag.bagInfo[].flightLegs[].fltRouteSrc
                                               FROM BaggageInfo bag
                                               WHERE NOT bag.bagInfo[].flightLegs[].fltRouteSrc=any ""SFO""";
    Console.WriteLine("\nUsing Logical operator!");
    await fetchData(client,logope_stmt);

    private const string valcomp_stmt =@"SELECT fullname, bag.bagInfo[].routing FROM BaggageInfo bag WHERE gender=""M""" ;
    Console.WriteLine("\nUsing Value Comparison operator!");
    await fetchData(client,valcomp_stmt);

    private const string inope_stmt =@"SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
                                              FROM BaggageInfo bagdet WHERE bagdet.fullName IN
                                              (""Lucinda Beckman"", ""Adam Phillips"",""Dierdre Amador"",""Fallon Clements"")";
    Console.WriteLine("\nUsing IN operator!");
    await fetchData(client,inope_stmt);

    private const string existsope_stmt =@"SELECT fullName, bag.ticketNo FROM BaggageInfo bag WHERE
                                                 EXISTS bag.bagInfo[$element.bagArrivalDate >=""2019-03-01T00:00:00""]";
    Console.WriteLine("\nUsing EXISTS operator!");
    await fetchData(client,existsope_stmt);