Hi Team,
We have a dashboard where we want to fetch all the documents based on status (COMPLETED / FAILED). Right now we are using N1Sql with index created. We are fetching only particular fields and not on all the fields from the document.
The Problem is every time if we want to fetch any new fields or if we want to add new fields in where clause, we need to engage our CB support team to update the indexes.
Our support team suggesting to use select meta().id from mybucket query to fetch id list first then using the id list fetch all the documents.
Could you please help on this or Is there any best way to fetch using N1SQL without changing the index every time.?
Couchbase Version : 6.6
SDK Version : 3.1.2
Current N1SQL for Sample :
select id,status,subStatus,interaction,startTimestamp,endTimestamp,traceID,accountNumberToken,customerID,pcn,parentJourneyId,childJourneyIds,resumeTime from mybucket where type= “JRN” and status = ‘COMPLETED’ and startTimestamp BETWEEN ‘2021-07-10T08:22:11.835Z’ and ‘2021-07-20T08:22:11.835Z’ order by startTimestamp desc offset 0 limit 100
Current Index :
CREATE INDEX my_index ON mybucket(startTimestamp DESC,subStatus,endTimestamp,failureReason,id,interaction,traceID,accountNumberToken,customerID,pcn,parentJourneyId,childJourneyIds) WHERE ((type = “JRN”) and (status = “COMPLETED”)) WITH { “nodes”:[ “xxx.xxx.xxx.com:8091”,“xxx.xxx.xxxx.com:8091” ], “num_replica”:1 }