Query doesn't show expected results when index exists

SQL++
, ,
1

Hello,

My organization uses Couchbase as our main database for an in-house automation solution we created that ties a bunch of open source projects together. We have a pretty basic understanding of Couchbase and need some help with a behavior I cannot explain.

This query:

SELECT apig_jid FROM `apig` WHERE `status` = "pending" AND `type` = "job"

Does not return the result I am looking for when an index that I created like this exists:

CREATE INDEX idx_pendingjobs ON apig(apig) WHERE type = 'job' AND status = 'pending'

However, it does show up in this query:

 SELECT apig_jid FROM `apig` WHERE `status` = "pending"

When I query the DB for the document that I am trying to retrieve:

SELECT status, type, meta().id FROM `apig` WHERE `apig_jid` = "asp-infrastructure-20200923-172235-319"

I get the result that I expect:

[
  {
    "id": "f81c89a6-8287-4b00-a66a-5f6b54741b04",
    "status": "pending",
    "type": "job"
  }
]

I have no idea why this is going so wrong. In my basic understanding of Couchbase this should work correctly. I have no DBA experience and could be missing something super basic.

Thank you!

2

hello hi - I remade my index a slightly different way this time. we’ll see if it holds.

I would really like some help on this, though. All the index documents I read are over my head!

Thanks!

3 
CREATE INDEX idx_pendingjobs ON apig(status, apig_jid) WHERE type = "job";
CREATE INDEX idx_jid ON apig(apig_jid) WHERE type = "job";

SELECT status, type, meta().id FROM `apig` WHERE  type ="job"  AND `apig_jid` = "asp-infrastructure-20200923-172235-319"

https://index-advisor.couchbase.com/indexadvisor/#1