Index not hitting for array

Hello -

We have a document type that includes an array of “Opportunities”:

{
  "accountNumber": 123456789,
  "facilityCode": "WPL",
  "opportunities": [
       {
         "opportunityType": 1,
         "originalCode": "296",
         "suggestedCode": "283"
      }
   ]
}

We created an index with the following syntax:

CREATE INDEX idx_Opportunities ON cloud_med(accountNumber,opportunities) WHERE (opportunities is not null)

But the following query never hits the index, which we don’t understand. Any ideas?

SELECT opportunities[1].originalCode FROM cloud_med WHERE opportunities IS NOT NULL

Why do you have accountNumber in your index.

The query in my previous message is just a test query. The accountNumber is our unique identifier so we typically include it in indexes to aid in joins. Does including accountNumber somehow break the query engine’s ability to use that index?

Yes, indexes need to match their respective queries.

Thank you that fixed it! One last question: Since “opportunities” is an array, if we create the following index:

CREATE INDEX idx_Opportunities ON cloud_med(opportunities) 
WHERE (opportunities is not null)

Will this allow for an index hit on any members & properties within the array?

thanks,

  • Jeff

Hi Jeff,

Please google “N1QL array indexing”.