Stack Exchange
Array index on ArangoDb
My collection in ArangoDb have objects with this schema (simplified):
{
    "userName": "Billy",
    "email": "Billy@test.com",
    "logins": [
      {
        "authenticationTokens": [],
        "loginProvider": "Facebook",
        "providerKey": "123",
        "providerDisplayName": null
      }
    ],
    "roles": [],
    "claims": []
}

That's ASPNetCore.Identity implementation on ArangoDb by BorderEast aspnetcore-identity-arangodb
PROBLEM:
To auth using Facebook it uses AQL query
for u in IdentityUser for l in u.logins filter l.loginProvider == "Facebook" && l.providerKey == "123" return u

which works well but does not use any index
Indexes used:
    none

I have tried indices:
db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].loginProvider", "logins[*].providerKey" ] });
db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].loginProvider" ] });
db.IdentityUser.ensureIndex({ type: "hash", fields: [ "logins[*].providerKey" ], unique: true });

None of them is used.
Can someone advise how the index should look like for that query?

Pushed new nuget package to support your index. At some point I'll put the queries in a config to make this easier :)

The issue lies with they query. It needs to be re-written and then it will work. It has to do with using the in comparison operator as discussed in this answer:

"Still the query in 2.8 won't use that index because the array indexes  are only used for the IN comparison operator."

So if we change the query we get this:
Query string:
 for u in IdentityUser 
  let l = u.logins[*].loginProvider
  let p = u.logins[*].providerKey
  filter  "google"  in l and "googlekey" in p
  return u

Execution plan:
 Id   NodeType          Est.   Comment
  1   SingletonNode        1   * ROOT
  8   IndexNode            1     - FOR u IN IdentityUser   /* persistent index scan */
  9   CalculationNode      1       - LET #7 = ("googlekey" in u.`logins`[*].`providerKey`)   /* simple expression */   /* collections used: u : IdentityUser */
  6   FilterNode           1       - FILTER #7
  7   ReturnNode           1       - RETURN u

Indexes used:
 By   Type         Collection     Unique   Sparse   Selectivity   Fields                          Ranges
  8   persistent   IdentityUser   false    false            n/a   [ `logins[*].loginProvider` ]   ("google" in u.`logins`[*].`loginProvider`)

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   remove-unnecessary-calculations
  3   use-indexes
  4   remove-filter-covered-by-index


@Ruslan, as the auther of that repo, I'll update the code and push out a new nuget asap.
