N1QL query with IN Clause - not working

SQL++
, ,
41

It looks like you have too many entries for doc_type = “token”. As i mentioned earlier named parameters /query parameters in IN can’t push the values to indexer.

The option you have don’t use query parameter in IN clause generate query text dynamically and try adhoc query for better performance.

42

@vsr1 Yes we do have more than million documents.
So youre suggesting only option i have to trigger such query is to generate dynamically the part which goes in IN clause i.e. guid_list - dynamically generate the guid_list and append into the string query ?

Also, why does the query run perfectly fine when i replace the $guid_list with hardcoded values ?
Still the whole query is same and uses IN clause - still it works just fine. Can you explain me to understand ?

43

Yes.

The following is other option but you have million documents may not perform better. try it out.

CREATE INDEX idx_dp_token_accnt_id2 ON dp_token(accnt_guid) WHERE doc_type=‘token’;
SELECT d.* FROM (SELECT RAW META().id FROM dp_token USE INDEX(idx_dp_token_accnt_id2) WHERE doc_type=‘token’ AND accnt_guid = $guid_list) AS q JOIN dp_token d ON KEY
S q;

Subquery uses covered index and produces qualified documents and join fetches them.

44

FYI: You have many documents and your queries using IntersectScans. This can impact performance and scalability.
https://blog.couchbase.com/n1ql-practical-guide/
Design Index for Query in Couchbase N1QL chapter provides step by step instructions.

45

When you use hard coded values during prepare time query knows what values you are looking push those values to indexer. “Understanding Index Scans in Couchbase N1QL Query” chapter in the book will give details.

46

Hi @vsr1

Thank you for support,now i’m getting the proper results.
one think i need to clarify about array of indexing.
below is my JSON document in couchbase;

{
“active”: true,
“avatar”: [
{
“id”: 89000
},
{
“id”: 872457
},
{
“id”: 872460
}
],
“category”: [
{
“id”: 1111
},
{
“id”: 2222
},
{
“id”: 3333
}],
“client”: “importtest”,
“content”: “New data with Physician data only”,
“engagement_status”: null,
“event_time”: “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”: 100140
},
{
“id”: 100032
},
{
“id”: 100001
},
{
“id”: 100000
}
],
“source_id”: 100140,
“source_name”: “Amazon”,
“source_score”: null,
“survey_type”: “FEEDBACK”,
“task_escalated”: false,
“task_id”: null,
“task_open_for_days”: null,
“task_owner_id”: null,
“task_owner_name”: “admin@bfountain.com”,
“task_response_type”: null,
“task_status_code”: null
}
and i’m create a secondary index as below:

CREATE INDEX idx2 ON default (client,feedback_score_sentiment,task_owner_name,ALL DISTINCT ARRAY v.id FOR v IN
[avatar_hierarchy,source_hierarchy,category_hierarchy] END)
where event_time between “1990-01-01” and “3000-01-01”;

ca you please help me that is the right way to create index using ‘IN’

47

Hi @rkumar,
This is unrelated to original post Is it possible to open as separate post.

I don’t see any of these in original document. avatar_hierarchy,source_hierarchy,category_hierarchy. Could you provide a query you need or what you want query so that i will provide index definition.