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