I am trying to perform an IN CLAUSE on a JSON String Array
Details": {
“Name”: “Name1”,
“Url”: “Url1”,
“Items”: [
“123”,
“456”,
“789”,
]
}
How to get all the documents where Items contain 123
In your WHERE clause, you can use:
WHERE 123 IN Items;
Thanks. That works like a charm
Hi,
I am trying the same with my java code, but IN statement seems to fail to take in my parameter in List, array, or plan string with [ ] . My failing code is below
sample jsons in mybucket:
{
“date”: “2015-02-15”,
“someField”: “ABC”,
}
{
“date”: “2015-02-15”,
“someField”: “BBC”,
}
Code I am using:
ParameterizedN1qlQuery query1 = N1qlQuery.parameterized("select * from `mybucket` WHERE date = $1 and someField IN $2", placeholderValues);```
If i remove the "someField IN $2" part it works fine. Is there a specific way to handle IN statement in N1QL?Can you try using named parameters?
1 Like
That totally worked !! with below syntax (for future forum browsers)
Edit: That did not work actually, may be my syntax is not right. But it
is not doing what IN statement would do. It is actually working only
with the first where clause i.e date = $1 . Its getting me all docs
which satisfies the date criteria with out heed to IN statement in my
syntax.
ParameterizedN1qlQuery
query1 = N1qlQuery.parameterized("select * from `mybucket` WHERE date =
$1 and $2", placeholderValues);```
**Note that the query structure has slightly changed**Hi @geraldss, How can I query for multiple values. Say, I want to get all the documents which are having either 123 or 456 or 789 etc.
I got to know how to do it @geraldss, by referring to the following link
http://developer.couchbase.com/documentation/server/4.1/sdks/java-2.2/querying-n1ql.html
Snippet to look at:
WHERE ANY child IN tutorial.children SATISFIES child.age >= 12;
1 Like
Was this ever solved? I am trying to do a parameterized query - “select * from location where gpid in $gpid” and it is failing to return any documents. I tried other variants of the query as well like “select * from location where gpid in [$gpid]” but to no effect. I also tried using positional parameters ($1) and passing the parameters as both an array of numbers and a string (1,2,3). Nothing has worked except concatenating strings to create a new query each time.
The query works fine in cbq but how do I do a parameterized query with cbq?
With the current shell we can’t execute a parameterized query. But it can be done with the rest api as follows :
(Using named parameters)
curl -u Administrator:password -v http://localhost:8093/query -d ‘statement=select $gpid; &$gpid=“Value”’
With the new shell that will be available soon, it is possible to pass in parameterized queries. For named parameters :
cbq> \SET -$gpid 1;
cbq> select $gpid;
{
"requestID": "b8d3f9de-320e-4e88-89f0-5e1e9387a528",
"signature": {
"$1": "json"
},
"results": [
{
"$1": 1
}
],
"status": "success",
"metrics": {
"elapsedTime": "426.864µs",
"executionTime": "400.919µs",
"resultCount": 1,
"resultSize": 31
}
}
You can also use positional parameters :
cbq> \SET -args [1, Value];
cbq> \SET -args [1, "Value"];
cbq> select $1, $2;
{
"requestID": "e1a93610-4b67-49d9-811e-8caec09d4039",
"signature": {
"$1": "json",
"$2": "json"
},
"results": [
{
"$1": 1,
"$2": "Value"
}
],
"status": "success",
"metrics": {
"elapsedTime": "527.815µs",
"executionTime": "502.429µs",
"resultCount": 1,
"resultSize": 58
}
}
1 Like
Hi Couchbase team.
I am trying to use execute N1QL query using spring-data and couchbase does not return any results for this:
@Query("#{#n1ql electEntity} use KEYS [ $myKeys ]")
List findAllByKeys(@Param(“myKey”) String myKeys), i also tried to use JsonArray instead of string but it generates single key “[‘key1’ ‘key2’]” and throws an error invalid key, so i am passing the string:
String myKeys= “‘key1’, ‘key2’” and i get 0 results even the request is correct and goes through.
String myKeys= “key1, key2” also does not return any results:
Executing N1QL query: {“scan_consistency”:“statement_plus”,“statement”:“SELECT META(my_bucket).id AS _ID, META(my_bucket).cas AS _CAS, my_bucket.* FROM my_bucket use KEYS [ $myKey ]”,"$myKey":“key1, key2”}
When i try from command line it finds results.
curl -u user:user -v http://localhost:8093/query/service -d ‘statement=SELECT META(my_bucket).id AS _ID, META(my_bucket).cas AS _CAS, my_bucket.* FROM my_bucket use KEYS $myKey&$myKey=[“key1”,“key2”]’
Please suggest how can i get list of documents using documents key using spring-data and N1QL.
Thanks!
The query should work with spring,
the SpEL for the query should be like below
@Query("#{#n1ql.selectEntity} use KEYS #{myKey} WHERE #{#n1ql.filter}")
List findAllByKeys(@Param(“myKey”) String myKey)
You need the filter for spring to filter out particular repository. Alternatively, you can use a direct query interface using CouchbaseTemplate
couchbaseTemplate.findByN1QL(N1qlQuery.parameterized("SELECT META(my_bucket).id AS _ID, META(my_bucket).cas AS _CAS, my_bucket.* FROM my_bucket use KEYS $myKey", JsonObject.create().put("myKey", "key1")))
1 Like
@subhashni, Thanks for your reply.
I tried this n1ql query but got:
EL1008E:(pos 0): Property or field ‘myKeys’ cannot be found on object of type ‘java.lang.Object’ - maybe not public?
Updated:
This one works correctly and returns multiply results:
@Query("#{#n1ql.selectEntity} use KEYS [ #{[0]} ] WHERE #{#n1ql .filter}")
List findAllByKeys(String myKey) //String myKeys = “‘key1’, ‘key2’”
Thanks for the help!
Oh great glad you found a solution, apologies my reply missed a hash sign and quotes as well to format it correctly
@Query("#{#n1ql.selectEntity} USE KEYS \"#{#myKey}\" WHERE #{#n1ql.filter}")
List findAllByKeys(@Param("myKey") String myKey);
1 Like