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);