I have mongodb version 3.6 on server. And my server sources are in nodeJS
I have some documents like this:
{"title": "test1", sort: 1, parent: 1},
{"title": "test2", sort: 7, parent: 1},
{"title": "test3", sort: 5, parent: 1},
{"title": "test4", sort: 1, parent: 2},
{"title": "test5", sort: 7, parent: 2},
{"title": "test6", sort: 5, parent: 2}
Now I want to update one group of documents (For example documents with parent=1) and set sort field values of this group with new values by the pattern like previous value plus 10 starting the group with number 1.
So I want the result below:
{"title": "test1", sort: 1, parent: 1},
{"title": "test2", sort: 21, parent: 1},
{"title": "test3", sort: 11, parent: 1},
{"title": "test4", sort: 1, parent: 2},
{"title": "test5", sort: 7, parent: 2},
{"title": "test6", sort: 5, parent: 2}
What is the best way and with the best performance to perform this?
Using only operations available in Mongo 3.6:
Since you want to update only one parent at a time, the last stage of this is a $merge. And the docs only have the fields for the original _id and the new sort.
db.collection.aggregate([
{
// match on the parent you want
$match: { "parent": 1 }
},
{
// sort the docs under that parent
$sort: { "sort": 1 }
},
{
// create a group for that one parent which is
// currently all docs due to first $match stage
$group: {
_id: null,
docs: {
// docs will be already sorted in this array
$push: "$$ROOT"
}
}
},
{
// set each doc `sort` field to the value it should be
// based on its index in docs
$set: {
docs: {
$map: {
input: "$docs",
in: {
"_id": "$$this._id",
"sort": {
$add: [
1,
{
$multiply: [
{ $indexOfArray: ["$docs", "$$this"] },
10
]
}
]
}
}
}
}
}
},
{ $unwind: "$docs" },
{ $replaceRoot: { newRoot: "$docs" } },
{ $merge: "collection" }
])
If you want to see what the full resulting docs would be, see this similar pipeline. The main difference is the $map part of the $set stage which includes all the doc fields. You can also add a final { $merge: "collection" } stage to use this pipeline as-is. But since you asked about performance, then the merge should only have the updated fields and not the full doc.
Btw, for performance, it's better to apply the update to ALL docs without setting the parent each time. ie Update the sort value for all docs per parent group.
For bulk updating all docs in the collection, using the sort per parent, replace the first three stages with these two instead:
db.collection.aggregate([
{
// sort the docs under that parent
$sort: {
"parent": 1,
"sort": 1
}
},
{
// create a group for that one parent which is
// currently all docs due to first $match stage
$group: {
_id: "$parent",
docs: {
// docs will be already sorted in this array
$push: "$$ROOT"
}
}
},
// everything else remains the same, paste that here
])
Mongo Playground to update all docs in the collection
If you were on Mongo 5.0+ and $setWindowFields was available, then this applies:
The change needed in this answer, when compared to your related previous question is quite minor, wrt my answer there.
Use the $rank function of $setWindowFields to provide ranks 1, 2, 3, etc. which will be based on the existing sort value.
partitionBy for the parent so that each partition (group) is separated by parent.The final new-sort will be (rank - 1) x 10 + 1
Use the $out operation to write all the updated docs back to the same collection.
The only change is the addition of partitionBy: "$parent" in $setWindowFields:
db.collection.aggregate([
{
$setWindowFields: {
partitionBy: "$parent",
sortBy: { sort: 1 },
output: {
sort: { $rank: {} }
}
}
},
{
$set: {
sort: {
$add: [
1,
{
$multiply: [
{ $subtract: ["$sort", 1] },
10
]
}
]
}
}
},
{ $out: "collection" }
])