MongoDB.live, free & fully virtual. Register Now MongoDB.live, free & fully virtual. Register Now

Wrong index from indexOfArray for relation query with duplicated id

The following examples use the collection orders that contains the following documents:

{ _id: 1, itemIds:   [ObjectId("1"),   ObjectId("2"),   ObjectId("2"),   ObjectId("3"), ObjectId("4") }

and collection items contains documents

{ "_id" : ObjectId("1")}
{ "_id" : ObjectId("2")}
{ "_id" : ObjectId("3")}
{ "_id" : ObjectId("4")}

for the following query:

db.getCollection('orders').aggregate(
     [ { '$match': { _id: ObjectId('1') } },
  { '$limit': 1 },
  { '$lookup':
     { from: 'items',
       let: { itemIds: '$itemIds'},
       pipeline:
        [ { '$match': { '$expr': { '$in': [ '$_id', '$itemIds' ] } } },
          { '$addFields': { sortIdx: { '$indexOfArray': [ '$itemIds', '$_id' ] }, ids: '$itemIds' } },
          { '$sort': { sortIdx: 1 } }
        ],
       as: 'items' } },
  { '$project': { items: 1, itemIds: 1, _id: 0 } } ]
 )

got result

{
    "itemIds" : [ 
        ObjectId("1"), 
        ObjectId("2"), 
        ObjectId("2"), 
        ObjectId("3")
    ],
    "items" : [ 
        {
            "_id" : ObjectId("4"),
            "sortIdx" : -1,
            "ids" : [ 
                ObjectId("1"), 
                ObjectId("2"), 
                ObjectId("2"), 
                ObjectId("3"),
                ObjectId("4")
            ]
        }, 
        {
            "_id" : ObjectId("1"),
            "sortIdx" : 0,
            "ids" : [ 
                ObjectId("1"), 
                ObjectId("2"), 
                ObjectId("2"), 
                ObjectId("3"),
                ObjectId("4")
            ]
        }, 
        {
            "_id" : ObjectId("2"),
            "sortIdx" : 1,
            "ids" : [ 
                ObjectId("1"), 
                ObjectId("2"), 
                ObjectId("2"), 
                ObjectId("3"),
                ObjectId("4")
            ]
        },
       {
            "_id" : ObjectId("3"),
            "sortIdx" : 2,
            "ids" : [ 
                ObjectId("1"), 
                ObjectId("2"), 
                ObjectId("2"), 
                ObjectId("3"),
               ObjectId("4")
            ]
        }
    ]
}

item ObjectId("4") should not be the first one in items.

the reason is the item ObjectId("4") got a sortIdx -1 (means not found). but it should be the last one.
why it happened? and what’s the correct solution for sort relations?

database information
version: 4.4.1
platform: MacOS 10.13.6 (17G14033)