[SERVER-76000] $indexOfArray (aggregation) returns incorrect index Created: 12/Apr/23  Updated: 27/Oct/23  Resolved: 18/Apr/23

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 4.4.19, 6.0.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kevin Smith Assignee: Backlog - Query Execution
Resolution: Works as Designed Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2023-04-12-09-33-01-021.png     PNG File image-2023-04-12-09-38-47-007.png     PNG File image-2023-04-12-14-44-10-726.png     PNG File image-2023-04-12-14-45-58-724.png    
Assigned Teams:
Query Execution
Operating System: ALL
Steps To Reproduce:

If we insert the following record to a collection

```javascript

db.classes.insert({
    name: "Class A",
    students: [
     

{         firstName: "Joe",         middleName: "Harry",         lastName: "Bar"       }

,
     

{         firstName: "Mary",         lastName: "Molly"       }

,
     

{         firstName: "Katie",         middleName: "Morgan",         lastName: "Smith"       }

    ]
  })

```

Then run the following aggregation pipeline

```javascript

db.collection.aggregate([
  {
    "$project": {
      studentIndex:

{         "$indexOfArray": [           "$students.middleName",           "Morgan"         ]       }

    }
  }
])

```

We'd expect that the following is returned

```javascript

[
 

{     "studentIndex": 2   }

]

```

however the following is actually returned

```javascript

[
 

{     "studentIndex": 1   }

]

```

As you can see the index is off by 1.

 

https://mongoplayground.net/p/pCmXbXUkBOo

Participants:

 Description   

We've recently been writing a query to get back the index of an array item, however, it took us a while to realize that the index being returned was incorrect.

It seems that if the expression that you give to `$indexOfArray` has none existent values then the array index will be off by 1.

I'm not sure if this is by design but there's nothing in the documentation around this behavior. 



 Comments   
Comment by Kyle Suarez [ 18/Apr/23 ]

kev_bite@msn.com, I am going to close this ticket as "Works as Designed". Please let us know if you have other questions.

Cheers,
Kyle

Comment by Kyle Suarez [ 14/Apr/23 ]

kev_bite@msn.com, I believe the behavior of $indexOfArray is correct, but I can see why the output is confusing. If you evaluate the results of projecting $students.middleName, you will see it is an array with two items. The middle element in the original array doesn't contribute to the result of that expression.

test> db.classes.findOne()
{
  _id: ObjectId("6436b48933251f4661b06cc3"),
  name: 'Class A',
  students: [
    { firstName: 'Joe', middleName: 'Harry', lastName: 'Bar' },
    { firstName: 'Mary', lastName: 'Molly' },
    { firstName: 'Katie', middleName: 'Morgan', lastName: 'Smith' }
  ]
}
test> db.classes.aggregate({
...     $project: {
.....         middleNames: "$students.middleName"
.....     }
... });
[
  {
    _id: ObjectId("6436b48933251f4661b06cc3"),
    middleNames: [ 'Harry', 'Morgan' ]
  }
]

You could use, say, $map with $ifNull to guarantee that you always have an element even when the middle name is missing:

test> db.classes.aggregate({
...     $project: {
.....         middleNames: {
.......             $map: {
.........                 input: "$students",
.........                 in: {"$ifNull": ["$$this.middleName", ""]}
.........              }
.......         }
.....     }
... });
[
  {
    _id: ObjectId("6436b48933251f4661b06cc3"),
    middleNames: [ 'Harry', '', 'Morgan' ]
  }
]

Comment by Kevin Smith [ 12/Apr/23 ]

Hey chris.kelly@mongodb.com, yeah once you put a middle name in then it works, however, not all students have middle names.

One thing to note if you project that expression too you'll only get 2 values which kinda makes sense and I'm guessing that's what it's doing internally.

It just seems a little odd behavior as if I do the same in node I get the expected output

 

Comment by Chris Kelly [ 12/Apr/23 ]

Thanks for your report kev_bite@msn.com!

I tested your repro and I also observe this on MongoDB 4.4.19 and 6.0.5. 

It looks like this is because one of the entries does not have a middleName. Once that's added, it works as expected:

{
  "_id": {
    "$oid": "6436b438481abe390b7a6e44"
  },
  "name": "Class A",
  "students": [
    {
      "firstName": "Joe",
      "middleName": "Harry",
      "lastName": "Bar"
    },
    {
      "firstName": "Mary",
      "middleName": "test",
      "lastName": "Molly"
    },
    {
      "firstName": "Katie",
      "middleName": "Morgan",
      "lastName": "Smith"
    }
  ]
}

Generated at Thu Feb 08 06:31:34 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.