Previous Next JavaScript must be enabled to correctly display this content

## SQL Expression examples using QueryRequest API

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

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

Download the full code SQLExpressions.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 paran_expr="SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing, "+
    "bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag WHERE "+
    "bag.bagInfo.flightLegs[].fltRouteSrc=any \"SFO\" AND "+
    "(bag.bagInfo[].flightLegs[].fltRouteDest=any \"ATH\" OR "+
    "bag.bagInfo[].flightLegs[].fltRouteDest=any \"JTR\" )";
    System.out.println("Using Paranthesized expression ");
    fetchRows(handle,paran_expr);

    String case_expr="SELECT fullName,"+
             "CASE WHEN NOT exists bag.bagInfo.flightLegs[0] "+
            "THEN \"you have no bag info\" "+
            "WHEN NOT exists bag.bagInfo.flightLegs[1] "+
            "THEN \"you have one hop\" "+
            "WHEN NOT exists bag.bagInfo.flightLegs[2] "+
            "THEN \"you have two hops.\" "+
            "ELSE \"you have three hops.\" "+
            "END AS NUMBER_HOPS "+
    "FROM BaggageInfo bag WHERE ticketNo=1762341772625";
    System.out.println("Using Case Expression ");
    fetchRows(handle,case_expr);

    String seq_trn_expr="SELECT seq_transform(l.bagInfo[],"+
                                  "seq_transform("+
                                    "$sq1.flightLegs[],"+
                                    "seq_transform("+
                                      "$sq2.actions[],"+
                                      "{"+
                                       "\"at\" : $sq3.actionAt,"+
                                       "\"action\" : $sq3.actionCode,"+
                                       "\"flightNo\" : $sq2.flightNo,"+
                                       "\"tagNum\" : $sq1.tagNum"+
                                      "}"+
                                   ")"+
                                  ")"+
                               ") AS actions FROM baggageInfo l WHERE ticketNo=1762376407826";
    System.out.println("Using Sequence Transform Expressions ");
    fetchRows(handle,seq_trn_expr);

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

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

    paran_expr = '''SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
                       bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
                       WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
                       (bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
                       bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )'''
    print('Using Paranthesized expression:')
    fetch_data(handle,paran_expr)

    case_expr = '''SELECT fullName,
        CASE
            WHEN NOT exists bag.bagInfo.flightLegs[0]
            THEN "you have no bag info"
            WHEN NOT exists bag.bagInfo.flightLegs[1]
            THEN "you have one hop"
            WHEN NOT exists bag.bagInfo.flightLegs[2]
            THEN "you have two hops."
            ELSE "you have three hops."
        END AS NUMBER_HOPS
        FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
    print('Using Case Expression:')
    fetch_data(handle,case_expr)

    seq_trn_expr = '''SELECT seq_transform(l.bagInfo[],
                         seq_transform(
                           $sq1.flightLegs[],
                             seq_transform(
                               $sq2.actions[],
                                {
                                  "at" : $sq3.actionAt,
                                  "action" : $sq3.actionCode,
                                  "flightNo" : $sq2.flightNo,
                                  "tagNum" : $sq1.tagNum
                                }
                            )
                        )
                    ) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826'''
    print('Using Sequence Transform Expressions:')
    fetch_data(handle,seq_trn_expr)

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

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

    paran_expr := `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
                        bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
                        WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
                        (bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
                        bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
    fmt.Printf("Using Paranthesized expression:\n")
    fetchData(client, err,tableName,paran_expr)

    case_expr := `SELECT fullName,
                     CASE
             			WHEN NOT exists bag.bagInfo.flightLegs[0]
             			THEN "you have no bag info"
             			WHEN NOT exists bag.bagInfo.flightLegs[1]
             			THEN "you have one hop"
             			WHEN NOT exists bag.bagInfo.flightLegs[2]
             			THEN "you have two hops."
             			ELSE "you have three hops."
         				END AS NUMBER_HOPS
         				FROM BaggageInfo bag WHERE ticketNo=1762341772625`
    fmt.Printf("Using Case Expression:\n")
    fetchData(client, err,tableName,case_expr)

    seq_trn_expr := `SELECT seq_transform(l.bagInfo[],
                          seq_transform(
                            $sq1.flightLegs[],
                              seq_transform(
                                $sq2.actions[],
                                 {
                                   "at" : $sq3.actionAt,
                                   "action" : $sq3.actionCode,
                                   "flightNo" : $sq2.flightNo,
                                   "tagNum" : $sq1.tagNum
                                 }
                             )
                         )
                     ) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
    fmt.Printf("Using Sequence Transform Expressions:\n")
    fetchData(client, err,tableName,seq_trn_expr)

To execute a query use `query` method.

JavaScript: Download the full code SQLExpressions.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 SQLExpressions.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 paran_expr = `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
                        bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
                        WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
                        (bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
                        bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
    console.log("Using Paranthesized expression");
    await fetchData(handle,paran_expr);

    const case_expr = `SELECT fullName,
                      CASE
                        WHEN NOT exists bag.bagInfo.flightLegs[0]
                        THEN "you have no bag info"
                        WHEN NOT exists bag.bagInfo.flightLegs[1]
                        THEN "you have one hop"
                        WHEN NOT exists bag.bagInfo.flightLegs[2]
                        THEN "you have two hops."
                        ELSE "you have three hops."
                      END AS NUMBER_HOPS
                      FROM BaggageInfo bag WHERE ticketNo=1762341772625`
    console.log("Using Case Expression");
    await fetchData(handle,case_expr);

    const seq_trn_expr = `SELECT seq_transform(l.bagInfo[],
                          seq_transform(
                            $sq1.flightLegs[],
                            seq_transform(
                              $sq2.actions[],
                              {
                                "at" : $sq3.actionAt,
                                "action" : $sq3.actionCode,
                                "flightNo" : $sq2.flightNo,
                                "tagNum" : $sq1.tagNum
                              }
                          )
                      )
                    ) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
    console.log("Using Sequence Transform Expressions");
    await fetchData(handle,seq_trn_expr);

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

Download the full code SQLExpressions.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 paran_expr =@"SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
                                             bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
                                             WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any ""SFO"" AND
                                             (bag.bagInfo[].flightLegs[].fltRouteDest=any ""ATH"" OR
                                             bag.bagInfo[].flightLegs[].fltRouteDest=any ""JTR"" )";
    Console.WriteLine("\nUsing Paranthesized expression:!");
    await fetchData(client,paran_expr);

    private const string case_expr =@"SELECT fullName,
                                            CASE
                                              WHEN NOT exists bag.bagInfo.flightLegs[0]
                                              THEN ""you have no bag info""
                                              WHEN NOT exists bag.bagInfo.flightLegs[1]
                                              THEN ""you have one hop""
                                              WHEN NOT exists bag.bagInfo.flightLegs[2]
                                              THEN ""you have two hops.""
                                              ELSE ""you have three hops.""
                                           END AS NUMBER_HOPS
                                           FROM BaggageInfo bag WHERE ticketNo=1762341772625";
    Console.WriteLine("\nUsing Case Expression!");
    await fetchData(client,case_expr);

    private const string seq_trn_expr =@"SELECT seq_transform(l.bagInfo[],
                                               seq_transform(
                                                 $sq1.flightLegs[],
                                                 seq_transform(
                                                    $sq2.actions[],
                                                    {
                                                       ""at"" : $sq3.actionAt,
                                                       ""action"" : $sq3.actionCode,
                                                       ""flightNo"" : $sq2.flightNo,
                                                       ""tagNum"" : $sq1.tagNum
                                                    }
                                                   )
                                                 )
                                               ) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826" ;
    Console.WriteLine("\nUsing Sequence Transform Expressions!");
    await fetchData(client,seq_trn_expr);