N1QL query with IN Clause - not working

SQL++
, ,
1

I am trying to run N1QL query from my code and I am getting below observations:

The query syntax is like below : select * from table1 where guid is not missing and doc_type=‘xyz’ and guid in $guid_list

When i print it before making call to couchbase , the query statement looks like this :

{“statement”:“select * from table1 where guid is not missing and doc_type=‘xyz’ and guid in $guid_list”,"$guid_list":[“123”,"234]}

From my Java code - i pass the guid_list - as a List

This query never return any result.

But if i put the values directly - it just works fine. Can someone help - why it will behave like this ?

select * from table1 where guid is not missing and doc_type=‘xyz’ and guid in [“123”,“234”]

When i print it before making call to couchbase , the query statement looks like this :

{“statement”:"select * from table1 where guid is not missing and doc_type=‘xyz’ and guid in [“123”,“234”]}

This works perfectly fine.
Can someone explain - why does it not execute propery when it is passed in as parameter through list?

2

There us no quote at the end. guid is not missing is not required. guid IN … make the missing predicate implicit

curl -v http://localhost:8093/query/service -H “Content-Type: application/json” -d ‘{“statement”:“select * from table1 where doc_type="xyz" AND guid in $guid_list;”,“$guid_list”:[“123”,“234”]}’

3

It was a typo in my original question. Below is what i have observed.

My query looks like this -

String query = select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in $guid_list);

I have my guid_list as below -
List listOfGuid = new ArrayList<>();
listOfGuid.add(“a1b3594f-0b76-4c54-8206-db2c16286320”);

com.couchbase.client.java.document.json.JsonObject placeHolders = com.couchbase.client.java.document.json.JsonObject.create()
.put(“guid_list”, JsonArray.from(listOfGuid));

N1qlQuery statement = N1qlQuery.parameterized(query,placeHolders);

This doesnt work. But if i try to pass the guid_list with single quote and hardcode the list in my query it works fine. Not sure why it doesnt work when i pass it as list. Is it because when i pass as list it goes in as double quotes [“a1b3594f-0b76-4c54-8206-db2c16286320”] instead of [‘a1b3594f-0b76-4c54-8206-db2c16286320’].

I saw same issue was reported by someone on different thread.

4

Hi @parth.j.shah,

Does it work using the curl method posted by @vsr1 above. What response do you get when you query through the java sdk?

You can try printing the query generated
N1qlQuery q = N1qlQuery.parameterized(statement, placeHolders); System.out.println(q.toString());
and compare if the results are similar with curl

5

@subhashni : Unfortunately i am not able to connect to remote server using curl. But below is my observation:

First thing,
String Query = "select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in $guid_list);"
N1qlQuery statement = N1qlQuery.parameterized(Query,placeholders) where my Query is like above - passed in as String.

System.out.println(“Query Statement :”+statement.n1ql().toString());

I get below result
{“statement”:“select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in $guid_list)”,"$guid_list":[“a1b3594f-0b76-4c54-8206-db2c16286320”]}

This doesnt return any result.

When i hard code the values in the query it self my String query looks like below and it return the expected result

String query = “select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in [“a1b3594f-0b76-4c54-8206-db2c16286320”])”;

N1qlQuery statement = N1qlQuery.simple(query);

now when i print - System.out.println(“Query Statement :”+statement.n1ql().toString());

Query Statement :{“statement”:“select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in [“a1b3594f-0b76-4c54-8206-db2c16286320”])”}

Also, i tried to concatenate string with single quotes using Joiner and that works too. For some reason only when i pass the list in my parameterized query - it doesnt work.

6

Can you try to change doc_type=‘xyz’ ==> doc_type=“xyz”

Is it possible to post your Java program.

7

Hi @vsr1 i dont see any problems with that. The only problem i see is - when i pass the list in parameterized query - it goes as “$guid_list”:[“a1b3594f-0b76-4c54-8206-db2c16286320”] - which seems to be having some problem.

Do you have any example - how to pass list in N1QL parameterized query ?
As mentioned above my query looks like below
select * from table1 where guid is not missing and doc_type=‘xyz’ and (guid in $guid_list)

where my list will have List…

8

From N1QL side the following works.

insert into default values("01",{"doc_type":"xyz","guid":"a1b3594f-0b76-4c54-8206-db2c16286320"});
CREATE PRIMARY INDEX ON default;

curl -v http://localhost:8093/query/service -H “Content-Type: application/json” -d ‘{“statement”:“select * from default where doc_type=“xyz” and (guid in $guid_list)”,"$guid_list":[“a1b3594f-0b76-4c54-8206-db2c16286320”],“creds”:[{“user”: “XXX”, “pass”:“XXX”}]}’

9

Does that work form Java SDK ? I am trying to run it on local query service , mean while can you check if that works through Java SDK ?

10

Yes, it does, I tried the same query using java sdk 2.4.5 and couchbase server version 4.6.0. Which versions of the sdk and server are you using?

11

Hi @subhashni , @geraldss
I thought to make a post on the same issue.

i’m facing the same problem and literally stuck. its would be a great help if acknowledge the issue to us.
FYI m using the same version of CB and java sdk

Thanks

12

@subhashni : I tried using java sdk on the default bucket on my local - it works fine . I inserted the row and created index on default bucket and from my Java program i tried to run the query - it just worked fine.

But when i run the query against my actual bucket in my staging environment it just doesnt return any result .however as i said if i put the values hardcoded in my query it just works fine.

My understanding is, if it has to be any problem with index - even putting the hardcoded values it shouldn’t work , right ?
If its working putting the values directly then it should not have any index problem. Because the only difference between putting direct value vs. parameterized query is - we pass the list in parameter instead of values directly.

13

Hi @rkumar, I did not acknowledge an issue, instead I answered it works for me. Can you share the specific query you are trying to execute. Here is the code I had used for @parth.j.shah’s query

try {
		String statement = "select * from default where doc_type=\"xyz\" and (guid in $guid_list)";
		JsonObject placeHolders = JsonObject.create()
				.put("guid_list", JsonArray.from(Collections.singletonList("a1b3594f-0b76-4c54-8206-db2c16286320")));
		N1qlQuery q = N1qlQuery.parameterized(statement, placeHolders);

		for(N1qlQueryRow row : bucket.query(q)) {
			System.out.println("row " + row);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
14

Hi @parth.j.shah,

So parameterized query does work on your local environment. Is there a difference in your index definition, @vsr1 can probably help out there.

15

Hi @parth.j.shah,
Add EXPLAIN keyword before SELECT and post the output.

Also you can try this in cbq shell.
PREPARE p1 AS SELECT * FROM default WHERE doc_type=‘xyz’ AND guid IN $guid_list;
\set -$guid_list [“a1b3594f-0b76-4c54-8206-db2c16286320”] ;
execute p1;
If that doesn’t work post
EXPLAIN SELECT * FROM default WHERE doc_type=‘xyz’ AND guid IN $guid_list;

16

Hi,
Below is the query it make(no index except primary is created)

select * from default AS d
unnest d.avatar AS a
unnest d.source AS s
where d.time between $startDate and $endDate
and a.id IN $avatars
and s.id IN $sources
and d.client= xxx
ORDER BY d.name ASC
and limit 100 offset 10

and after printing N1qlQuery statement ,below is the result it shown:

ParameterizedN1qlQuery{statement=select * from default AS d unnest d.avatar AS a unnest d.source AS s where d.event between $startDate and $endDate and a.id IN $avatars and s.id IN $sources and d.client= xxx ORDER BY d.name ASC limit 100 offset 10, params={“sources”:[[“111111”]],“endDate”:" 2017-05-20T23:59:59.000Z",“startDate”:"2017-05-01T00:00:00.000Z ",“avatars”:[[“2222222”]]}}

and below is the java code to create the laceholder.
JsonObject placeHolders = JsonObject.create()
.put(“avatars”, JsonArray.from(Collections.singletonList(new ArrayList(avatarIds))))
.put(“sources”, JsonArray.from(Collections.singletonList(new ArrayList(sourceIds))))
.put(“startDate”, fromDate)
.put(“endDate”, toDate)

as avatarIds and sourceIds are set ,so converting from set to list inside.

thanks

17

you are supplying “avatars”:[[“2222222”]] is your document has “avatar”:{“id”:[“2222222”]} i.e id as array of string If not you need to supply right value

18

Hi @vsr1,

let me show the JSON as below.

select * from default d limit 1, give the below

 {
        "d": {
            "active": true,
            "avatar": [
                {
                    "id": 2222222
                },
                {
                    "id": 872457
                },
                {
                    "id": 872460
                }
            ],
            "category": [],
            "client": "importtest",
            "content": "New data with Physician data only",
            "engagement_status": null,
            "event": "2017-05-17T00:00:00.000Z",
            "feedback_score": null,
            "feedback_score_sentiment": "unscored",
            "id": 20611555,
            "location_id": 0,
            "location_name": null,
            "location_profile_picture_url": null,
            "provider_gender": null,
            "provider_id": 568905,
            "provider_name": "John Doe",
            "provider_profile_picture_url": null,
            "source": [
                {
                    "id": 1111111
                },
                {
                    "id": 100032
                },
                {
                    "id": 100001
                },
                {
                    "id": 100000
                }
            ],
            "source_id": 100140,
            "source_name": "ABC",
            "source_score": null,
            "survey_type": "FEEDBACK",
            "task_escalated": false,
            "task_id": null,
            "task_open_for_days": null,
            "task_owner_id": null,
            "task_owner_name": null,
            "task_response_type": null,
            "task_status_code": null
        }
    }
],
"status": "success",
"metrics": {
    "elapsedTime": "1.8077445s",
    "executionTime": "1.807682746s",
    "resultCount": 1,
    "resultSize": 1884
} .

please guide me.

19

The parameter binding needs to be as follows.

source,avatars can’t be array of arrays
The data you have integers and you are supplying strings that will not match.

params={“sources”:[111111],“endDate”:" 2017-05-20T23:59:59.000Z",“startDate”:"2017-05-01T00:00:00.000Z ",“avatars”:[2222222]}}

Also not sure what exactly you want to project. If predicate matches if you want whole document the following right query. It can be done without UNNEST. You can consider once actual problem is resolved.

SELECT d.* FROM default AS d
WHERE d.time BETWEEN $startDate AND $endDate AND d.client= xxx AND
ANY a IN d.avatar SATISFIES a.id IN $avatars END AND
ANY s IN d.source SATISFIES s.id IN $sources END AND
ORDER BY d.name ASC LIMIT 100 OFFSET 10

20

I had used Collections.singletonList for that query since there is one object. For your query it should be
.put("avatars", JsonArray.from(Collections.singletonList("2222222")));