MongoDB $indexOfArray在数组嵌套对象中无法正常工作的问题排查

我有如下结构的MongoDB文档：

{"_id":"640311ab0469a9c4eaf3d2bd","id":4051,"email":"manoj123@gmail.com","password":"SomeNew SecurePassword","about":null,"token":"7f471974-ae46-4ac0-a882-1980c300c4d6","country":"India","location":null,"lng":0,"lat":0,"dob":null,"gender":0,"userType":1,"userStatus":1,"profilePicture":"Images/9b291404-bc2e-4806-88c5-08d29e65a5ad.png","coverPicture":"Images/44af97d9-b8c9-4ec1-a099-010671db25b7.png","enablefollowme":false,"sendmenotifications":false,"sendTextmessages":false,"enabletagging":false,"createdAt":"2020-01-01T11:13:27.1107739","updatedAt":"2020-01-02T09:16:49.284864","livelng":77.389849,"livelat":28.6282231,"liveLocation":"Unnamed Road, Chhijarsi, Sector 63, Noida, Uttar Pradesh 201307, India","creditBalance":130,"myCash":0,"data":{"name":"ThisIsAwesmoe","arr":[1,100,3,4,5,6,7,8,9],"hobies":{"composer":["anirudh",{"co_singer":["rakshitha","divagar"]},"yuvan","rahman"],"music":"helo"}},"scores":[{"subject":"math","score":100},{"subject":"physics","score":85},{"subject":"chemistry","score":95}],"fix":1,"hello":1,"recent_views":[200],"exam":"","subject":"","arr":{"name":"sibidharan","pass":"hello","score":{"subject":{"minor":"zoology","major":"biology","others":["evs",{"name":"shiro","inarr":[200,2,3,{"sub":"testsub","newsu":"aksjdad","secret":"skdjfnsdkfjnsdfsdf"},4,12]}]},"score":40,"new":"not7","hello":{"arr":[5,2]}}},"name":"Manoj Kumar","d":[1,3,4,5],"score":{},"hgf":5}

尝试用$indexOfArray查找路径data.hobies.composer.1.co_singer中'divagar'的索引，使用的聚合查询管道如下：

[{"$match":{"id":4051}},{"$project":{"index":{"$indexOfArray":["$data.hobies.composer.1.co_singer","divagar"]}}]

通过以下PyMongo代码执行查询后返回-1：

result = list(self._collection.aggregate(pipeline))
return result[0]["index"]

但在无嵌套数组的场景下$indexOfArray可正常工作，请问问题出在哪？

问题出在直接用点索引data.hobies.composer.1.co_singer作为$indexOfArray的数组参数时，MongoDB（尤其是旧版本）无法正确解析这个嵌套路径为数组。因为composer是数组，索引1的位置是一个对象，直接通过点索引访问该对象的co_singer数组，在聚合表达式中可能无法被$indexOfArray正确识别。

正确的做法是先用$arrayElemAt明确取出composer数组中索引为1的对象，再提取其co_singer数组，然后传入$indexOfArray。

修改后的聚合管道（兼容MongoDB 4.4+） #

[
  { "$match": { "id": 4051 } },
  {
    "$project": {
      "index": {
        "$indexOfArray": [
          { "$getField": { "field": "co_singer", "input": { "$arrayElemAt": ["$data.hobies.composer", 1] } } },
          "divagar"
        ]
      }
    }
  }
]

更简洁的写法（MongoDB 5.0+支持） #

[
  { "$match": { "id": 4051 } },
  {
    "$project": {
      "index": {
        "$indexOfArray": [
          { "$arrayElemAt": ["$data.hobies.composer", 1] }.co_singer,
          "divagar"
        ]
      }
    }
  }
]

更清晰的分步写法 #

如果觉得上面的写法太紧凑，也可以先通过$addFields提取目标数组，再执行索引查找：

[
  { "$match": { "id": 4051 } },
  {
    "$addFields": {
      "targetArray": { "$getField": { "field": "co_singer", "input": { "$arrayElemAt": ["$data.hobies.composer", 1] } } }
    }
  },
  {
    "$project": {
      "index": { "$indexOfArray": ["$targetArray", "divagar"] }
    }
  }
]

修改后执行查询，就能正确返回divagar在co_singer数组中的索引1了。

内容的提问来源于stack exchange，提问作者Sibidharan