CODEWITHSUNDEEP
springspring-bootcouchbasesql++
Shivababa
Shivababa

Reputation: 359

How to use 'IN' opeartor in Spring boot & Couch base -N1QL query

I have a JSON list from which from which I 'm looking up for a particular value using N1QL query. It works when I try only if there is only one value in the list.

But if there are multiple values in the list I'm not getting in response.

Json 

"suppliers_list": [
    "767",
    "300767"
]

@Query("SELECT users.*,  "
+ "META(users).id as _ID, META(users).cas as _CAS FROM #{#n1ql.bucket}"
+ "users WHERE lower(type)='users' AND ANY usersupplist IN users.suppliers_list SATISFIES usersupplist IN [$1] END")
userRepository.findBySupplierList(String List)

usersDefObjList = userRepository.findBySupplierList("767"); //Working

usersDefObjList = userRepository.findBySupplierList("767,300767");  // Not Working

usersDefObjList = userRepository.findBySupplierList("'767','300767'");  //  Not Working

But the same one like works in CouchBase Query window

SELECT users.*,META(users).id as _ID,META(users).cas as _CAS FROM TCI_DATA_MIG users WHERE lower(type)='users' and ANY usersupplist IN users.suppliers_list SATISFIES usersupplist IN ['767','300767'] END

Please let me know your suggestions to get the values in the repository N1QL query to fix this.

Upvotes: 0

Views: 227

Answers (1)

vsr
vsr

Reputation: 7414

In the N1QL right side of IN clause requires ARRAY.

If you know how many elements you can use

usersupplist IN [$1,$2,$3]

If the ARRAY is dynamic and don't know how many elements you can use following and pass in as ARRAY.

usersupplist IN $1
$1 =  [ "767", "300767" ]  i.e. JsonArray.from("767", "300767")

Check this out Couchbase parameterized N1QL query IN statement

Upvotes: 1

Related Questions