Question
How to compare sorting with lookup in mongodb 4.4?
I'm new to mongodb, I'm on version 4.4, no update possible.
I have a collection items
with an attribute categories
(array). I would like to "join" ($lookup) the collection categories
to it - where each category has a sortOrder
attribute - in order to compare if categories
in items
has a different sorting than in the collection categories
.
My approach so far:
[
{
$addFields: {
categoriesWithIndex: {
$map: {
input: {$range: [0, {$size: "$categories" }]},
as: "index",
in: {
index: "$$index",
value: {$arrayElemAt: ["$categories", "$$index"]}
}
}
}
}
},
{
$unwind: "$categoriesWithIndex"
},
{
$lookup: {
from: "categories",
localField: "categoriesWithIndex.value",
foreignField: "_id"
as: "categoryDetails"
}
},
{
$addFields: {
"categoriesWithIndex.sortOrder": {
$arrayElemAt: ["$categoryDetails.sortOrder", 0]
}
}
},
{
$group: {
_id: "$_id",
categories: {
$push: {
$mergeObjects: [
{
index: "$categoriesWithIndex.index",
sortOrder: "$categoriesWithIndex.sortOrder",
value: "$categoriesWithIndex.value"
}
]
}
}
}
},
]
now the document looks like this:
_id: "1234"
categories: Array (3)
0: Object
index: 0
sortOrder: "2"
value: "catX"
1: Object
index: 1
sortOrder: "3"
value: "catY"
2: Object
index: 2
sortOrder: "1"
value: "catZ"
now it feels like I'm almost there, but I'm not sure how to continue. Unfortunately I don't have $sortArray
...
could you please point me to the right direction?