How to query nested document using adaptive index


#1

We have documents like

{

“datemodified”: “2017-11-23 03:27:49”,
“doctype”: “inspection”,
“guid”: “-j0ko1I0-5UcCh1H0Je7CAX”,
“hoursdetail”: [
{
“comment”: “Test”,
“company”: “company1”,
“guid”: “BD500FB4-D46B-4590-8F57-C95ED0C6739A”,
“hours”: “Regular”,
“inventory”: “No”,
“jobcode”: “Sick”,
“workedhours”: 28800
},
{
“comment”: “”,
“company”: “company2”,
“guid”: “ABC9F21C-55B7-494F-87CE-C77AC1CCDE71”,
“hours”: “OverTime”,
“jobcode”: " Lunch",
“inventory”: “No”,
“workedhours”: 3600
}
],
“imagedetails”: [],
“status”: “Completed”,
“syncchannel”: “channel1”,

}

we have created a index like.
CREATE INDEX adaptive_test1
ON geoviewermobilesync(DISTINCT PAIRS({hoursdetail.jobcode,datemodified, doctype, syncchannel ,meta().id }))
where SUBSTR(meta().id, 0, 5) <> ‘_sync’ and syncchannel = channel1

I need to query all the docs which jobcode is Sick.
select * from geoviewermobilesync use index(adaptive_test1) where
SUBSTR(meta().id, 0, 5) <> ‘_sync’ and syncchannel = 'channel1’
and doctype = inspection and hoursdetail.jobcode = ‘Sick’

When I tried to query this I am getting empty result.

please let us know how do i query these nested doc using adaptive index, since we have multiple index’s i am using index name in my query.


#2

There is no field hoursdetail.jobcode hoursedetail is array. That is why it gives no results.

Try following index and query

CREATE INDEX ixtest1
ON geoviewermobilesync(syncchannel,  DISTINCT ARRAY v.jobcode FOR v IN  hoursdetail END) WHERE  doctype = "inspection" AND SUBSTR(meta().id, 0, 5) <> "_sync";
select * from geoviewermobilesync where
SUBSTR(meta().id, 0, 5) <> "_sync" and syncchannel = "channel1"
and doctype = "inspection" and ANY v IN hoursdetail SATISFIES v.jobcode = "Sick" END;

#3

Thanks for the replay, The jobcode field exist in the hoursdetail array, please refer field which is marked in bold

if we create index as suggest by you, then we might able to query only the ‘Sick’ jobcode types, what if we wanted to query other types like lunch etc… ? are we need to create a separate index for each type which we don’t wanted to do. since jobcode field is pouplated with 25 to 30 unique values.

{

“datemodified”: “2017-11-23 03:27:49”,
“doctype”: “inspection”,
“guid”: “-j0ko1I0-5UcCh1H0Je7CAX”,
hoursdetail”: [
{
“comment”: “Test”,
“company”: “company1”,
“guid”: “BD500FB4-D46B-4590-8F57-C95ED0C6739A”,
“hours”: “Regular”,
“inventory”: “No”,
“jobcode”: “Sick”,
“workedhours”: 28800
},
{
“comment”: “”,
“company”: “company2”,
“guid”: “ABC9F21C-55B7-494F-87CE-C77AC1CCDE71”,
“hours”: “OverTime”,
“jobcode”: " Lunch",
“inventory”: “No”,
“workedhours”: 3600
}
],
“imagedetails”: [],
“status”: “Completed”,
“syncchannel”: “channel1”,

}


#4

When you say hoursdetail.jobcode means hoursdetail is object. In your case it is array of objects.
In case of array of objects you need to give array index or required to array expression constructs.

The above index will work all jobcodes.

Same index can be used for all these queries

select * from geoviewermobilesync where
SUBSTR(meta().id, 0, 5) <> "_sync" and syncchannel = "channel1"
and doctype = "inspection" and ANY v IN hoursdetail SATISFIES v.jobcode = "lunch" END;

select * from geoviewermobilesync where
SUBSTR(meta().id, 0, 5) <> "_sync" and syncchannel = "channel1"
and doctype = "inspection" and ANY v IN hoursdetail SATISFIES v.jobcode IN [ "lunch", "Sick"]  END;

#5

Thanks and it is working


#6

Is there a way to define a DISTINCT PAIRS within an array index.
Let say in the above example I want this query on workedhours to work

select * from geoviewermobilesync where
SUBSTR(meta().id, 0, 5) <> "_sync" and syncchannel = "channel1"
and doctype = "inspection" and ANY v IN hoursdetail SATISFIES v.workedhours = 28800 END;

Basically, I dont want to create a new array index with the workedhours field. Is there a way to define a single generic index to support this


#7

If you want adaptive index you need to do on whole document DISTINCT PAIRS(self).

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/adaptive-indexing.html