How to create index for the pair of startDate and endDate saved in the nested document

Couchbase Server
1

In our database every document is related to an user. Every user can have multiple appointments, and each appointment has a startDate and an endDate. I want to do a query on the documents to return the users that has an appointment today. I wonder if you can please suggest a way to make the query run fast.
{
“firstName”:“my first name”,
“lastName”:“my last name”,
“appointments”:
[
{
“startDate”:“01/01/2017”,
“endDate”:“01/03/2017”
}
{
“startDate”:“01/10/2016”,
“endDate”:“02/05/2016”
}
]
}

I tried to create the following index:
create index startDate_endDate_index on myBucket
(DISTINCT ARRAY [a.startDate, a.endDate] FOR a IN appoinments)
where type=“user”

But that index is not use in my query as the following:
select * from myBucket where (ANY a IN appointments SATISFIES a.startDate<=today and a.endDate>today end)
and type=“user”

Not sure if any other index/view would work…

Our couchbase version is 4.5.

Thanks a lot for your help!

2

Try this.

CREATE INDEX idx_start ON myBucket( DISTINCT ARRAY s.startDate FOR s IN appointments END) WHERE type = "user";

CREATE INDEX idx_end ON myBucket( DISTINCT ARRAY e.startEnd FOR e IN appointments END) WHERE type = "user";


select *
from myBucket
where
    (ANY s IN appointments SATISFIES s.startDate<=today end)
AND
   (ANY e IN appointments SATISFIES e.endDate>today end)
AND
(ANY a IN appointments SATISFIES a.startDate<=today and a.endDate>today end)
and type="user"
3

Hi Geraldss,

I tried that and I did an explain on the select query you gave to me. Looks like only idx_start is being used…. Ideally, I want both indices to be used. I wonder if there is any way we can do it with the current couchbase server release.

Thanks!
Helen

4

Hi @helen,

It should use both indexes. There might be a typo somewhere. Try using only the endDate index by using a USE INDEX clause. After you get that working, remove the USE INDEX clause so that it uses both indexes.

The variable names in query, s and e, must match variable names s and e in the CREATE INDEX statements,