I have one collection that contains some base record data, for example:
[
{
"_id": "one",
"name": "The Enchanted River",
"description": "A mystical journey along a forgotten river that holds secrets of a lost civilization.",
"paperCopy": true,
"digitalCopy": true,
"bestSeller": true,
"isDiscounted": false,
"discountPercentage": 0.0
},
{
"_id": "two",
"name": "Galactic Odyssey",
"description": "An epic space adventure exploring distant galaxies and encountering alien civilizations.",
"paperCopy": true,
"digitalCopy": false,
"bestSeller": true,
"isDiscounted": false,
"discountPercentage": 5.57
},
{
"_id": "three",
"name": "The Last Heir",
"description": "A gripping tale of a young prince fighting to reclaim his throne against all odds.",
"paperCopy": true,
"digitalCopy": true,
"bestSeller": true,
"isDiscounted": false,
"discountPercentage": 27.1
},
{
"_id": "four",
"name": "Quantum Dreams",
"description": "A scientist's groundbreaking discovery challenges the very fabric of reality.",
"paperCopy": true,
"digitalCopy": true,
"bestSeller": true,
"isDiscounted": true,
"discountPercentage": 22.85
},
{
"_id": "five",
"name": "The Forgotten Garden",
"description": "An old gardener unravels the secrets of a mysterious estate with a tragic past.",
"paperCopy": false,
"digitalCopy": true,
"bestSeller": false,
"isDiscounted": false,
"discountPercentage": 0.0
}
]
Admins and users are allowed to make edits where they are stored in a different collection.
[
{
"_id": "one",
"user": "admin",
"patch": {
bestSeller: false
}
},
{
"_id": "one",
"user": "user1",
"patch": {
"bestSeller": true,
"isDiscounted": true,
"discountPercentage": 99,
"frontOfStore": true
}
},
]
I need to write an aggregate to merge the base record data with admin edits and then with user edits, add previously undefined fields, with the option to query on them.
5
-
First
$lookuppatches by admins, as “adminEdits”- if there can be multiple admin edits, add a sort order in the lookup pipeline
-
Then
$lookuppatches by users, as “userEdits”; I’m treating non-admins as users- sort by something if there can be multiple
-
$concatArraysthe admin and user patches, and$mergeObjectsthe resulting array to get one “finalPatch”.- Decide if user patches should overwrite admin patches, set the order in
concatArraysaccordingly. - This stage will work as-is whether is 1 admin patch + 1 user patch or many admin patches + many user patches (any combination of 0, 1 or many patches).
- Decide if user patches should overwrite admin patches, set the order in
-
Merge that into the main document and clear out the extra fields.
db.books.aggregate([
{
// first get admin patches
$lookup: {
from: "edits",
localField: "_id",
foreignField: "book_id",
as: "adminEdits",
pipeline: [
{ $match: { user: "admin" } }
]
}
},
{
// then get user patches (non-admin)
$lookup: {
from: "edits",
localField: "_id",
foreignField: "book_id",
as: "userEdits",
pipeline: [
{ $match: { user: { $ne: "admin" } } }
]
}
},
{
// concat the admin & user patch arrays
// decide if user edits overwrite admin (concat order)
// then merge the array of patches sub-objects
$set: {
finalPatch: {
$mergeObjects: {
$concatArrays: ["$adminEdits.patch", "$userEdits.patch"]
}
}
}
},
{
// merge the result into the doc
$replaceWith: { $mergeObjects: ["$$ROOT", "$finalPatch"] }
},
{
// remove all the extra fields created in this pipeline
$unset: ["adminEdits", "userEdits", "finalPatch"]
}
])
Mongo Playground
Side note: Your collection with the patches repeats the _id for books which is not permitted for the Primary Key. So I’ve called it book_id in the example and pipeline.
1