Java implementation of N1qlQuery.parameterized(query, json object)

Java SDK
,
1

I am trying to run a query from java with the couchbase sdk. The query below has fields with “annotation” to signify insert through a JsonObject. When I run one parameter through a JsonObject it works fine, but when I increase the number of parameters it does not seem to work. Can anyone help? NOTE: teh query runs sucessfully, but it returns empty values, if I hard code the values it returns a result set.

String query = " SELECT t.Number AS acct, t.sCode AS ps, t.AcctBasedInfo.HtusCode AS hs,
t.tusCode AS ss, t.Cat AS pcode, COUNT(1) AS cnts
FROM testBucket AS t
WHERE t.Number IN [“00100GH123”]
AND ((t.Date BETWEEN $startDate AND $endDate) OR t.Date = “1900-01-01”)
GROUP BY t.tusCode, t.Number, t.Cat,
t.HtusCode, t.tusCode";

JsonObject docVals = JsonObject.create().put(“endDate”,endDate).put(“startDate”,startDate);
getParamQueriedDoc(query2, docVals)

public N1qlQueryResult getParamQueriedDoc(String query, JsonObject objects){
Bucket bucket = getCouchbaseBucket();
N1qlQueryResult result = bucket.query(N1qlQuery.parameterized(query, objects));
return result;
}

2

@ks900
Below is my way of parameterized query. I am using spring data

StringBuffer update = new StringBuffer("UPDATE `users` USE KEYS $userId SET home=$home, office=$office ");
	System.out.println(update);
	n1ql = N1qlQuery.parameterized(update.toString(), JsonObject.create().put("userId", userId).put("home", home).put("office", office));
	couchbaseTemplate.queryN1QL(n1ql);
3

I was able to get the code to work, It seems like I was including an extra set of unnecessary quotes.

Another Question though, How can we parameterize the SELECT part of the query for example instead of

SELECT t.Number AS acct, t.sCode AS ps, t.AcctBasedInfo.HtusCode AS hs,
t.tusCode AS ss, t.Cat AS pcode

Could we have

SELECT $number AS acct, $code AS ps, $status AS hs,
$sub AS ss

When I do this and send in values from the Json Object as noted above, it seems to insert the string with the quote around the field so like:

SELECT "t.Number " AS acct

This in turn does not execute the query properly I want it to look like the original. Is this at all possible?

Thanks ahead of time

4

IIRC, N1QL does not allow parameters for the projection. You can only parameterize the predicate. @vsr1 or @keshav_m can probably confirm.

1 Like
5

The paths can’t be parameterized any where in query. The only values can be parametrized any where in the query.

If really need this you should try this.

SELECT t.[$number]  AS acct, t.[$code] AS ps, t.[$info].[$hcode]  AS hs

$number = "Number"
$code = "sCode"
$info = "AcctBasedInfo"
$hcode = "HtusCode "

In above case t.[$number] (As you note after dot array bracket starts no filed name) . The expression inside the array bracket is evaluated and must result in string. The result is converted into fieldname so it becomes t.Number then it evaluate as t.Number

This allows indirectly reference dynamic field

 SELECT RAW t.[t.name] FROM [{"name":"firstname", "firstname":"fname"}, {"name":"lastname", "lastname":"lname"}] AS t;
"results": [
    "fname",
    "lname"
    ]
1 Like
6

Yeah tried this approach out in the code and it works well. Thanks!