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)