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

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!

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"

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

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,