MongoDB aggregate merging fields

Question

I have a mongo Database I'll like to "join" two of them and then merge some other fields: Let's see the schemas:

Students Schema (and data):

{
    "_id": ObjectId("5fbd564981b1313de790b580"),
    "name": "John Doe",
    "age": "21",
    "image": "https://XXXX/481.png",
    "subjects": [
        {
            "_id": ObjectId("5fbd4e6881b1313de790b56b"),
            "passed": true,
        },
        {
            "_id": ObjectId("5fcb63fa8814d96876c687bf"),
        }
    ],
    "__v": NumberInt("1"),
}

and Subject schema:

{
    "_id": ObjectId("5fbd4e6881b1313de790b56b"),
    "course": 3,
    "teacher": "John Smith",
    "name": "Math",

},
{
    "_id": ObjectId("5fcb63fa8814d96876c687bf"),
    "name": "IT",
    "course": 8,
    "teacher": "John Peter",

}

What I'll like to make a query with the subjects (all info) of a student, also if the student have additional fields in subject like passed add it to the subject subdocument.

Here is my query till now:

db.students.aggregate([
{
    $match: 
        {
            _id : ObjectId('5fbd564981b1313de790b580')
        }
},
{
    $lookup : 
        {
            from : "subjects",
            localField : "subjects._id",
            foreignField : "_id", 
            as : "FoundSubject"
        }
}
]);

which correctly make the "join" but the merge is still missing, I got as result:

{
    "_id": ObjectId("5fbd564981b1313de790b580"),
    "name": "John Doe",
    "age": "21",
    "image": "https://XXXX/481.png",
    "subjects": [
        {
            "_id": ObjectId("5fbd4e6881b1313de790b56b"),
            "passed": true,
        },
        {
            "_id": ObjectId("5fcb63fa8814d96876c687bf"),
        }
    ],
    "__v": NumberInt("1"),
    "FoundSubject": [
        {
            "_id": ObjectId("5fbd4e6881b1313de790b56b"),
            "course": 3,
            "teacher": "John Smith",
            "name": "Math"
        },
        {
            "_id": ObjectId("5fcb63fa8814d96876c687bf"),
            "name": "IT",
            "course": 8,
            "teacher": "John Peter"
        }
        
    ]
}

but I'll like to have:

{
    "_id": ObjectId("5fbd564981b1313de790b580"),
    "name": "John Doe",
    "age": "21",
    "image": "https://XXXX/481.png",
    "subjects": [
        {
            "_id": ObjectId("5fbd4e6881b1313de790b56b"),
            "course": 3,
            "teacher": "John Smith",
            "name": "Math",
            "passed": true,
        },
        {
            "_id": ObjectId("5fcb63fa8814d96876c687bf"),
            "name": "IT",
            "course": 8,
            "teacher": "John Peter"
        }
    ],
    "__v": NumberInt("1"),
}

with merged data and field "passed" added. How can accomplish that? I'm new to MongoDB coming from MySQL.

Thanks

turivishal · Accepted Answer · 2020-12-09 13:37:18Z

You need to merge both objects, add below stage after $lookup,

MongoDB Version From 3.4

$map to iterate loop of students array
$reduce to iterate loop of FoundSubject array, check condition if condition match then return required fields otherwise return initial value
$project to remove FoundSubject from result

  {
    $addFields: {
      subjects: {
        $map: {
          input: "$subjects",
          as: "s",
          in: {
            $reduce: {
              input: "$FoundSubject",
              initialValue: {},
              in: {
                $cond: [
                  { $eq: ["$$s._id", "$$this._id"] },
                  {
                    _id: "$$this._id",
                    course: "$$this.course",
                    name: "$$this.name",
                    teacher: "$$this.teacher",
                    passed: "$$s.passed"
                  },
                  "$$value"
                ]
              }
            }
          }
        }
      }
    }
  },
  { $project: { FoundSubject: 0 } }

Playground

MongoDB Version From 4.4

$map to iterate loop of students array,
$filter to get matching document from FoundSubject array and $first to get first object from array returned by filter
$mergeObjects to merge current objects with found result object from filter
remove FoundSubject using $$REMOVE

  // skipping your stages
  {
    $addFields: {
      FoundSubject: "$$REMOVE",
      subjects: {
        $map: {
          input: "$subjects",
          as: "s",
          in: {
            $mergeObjects: [
              "$$s",
              {
                $first: {
                  $filter: {
                    input: "$FoundSubject",
                    cond: { $eq: ["$$s._id", "$$this._id"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }

Playground

Works fine on the playground, but on my database got that error: > [Error] Unrecognized expression '$mergeObjects' Could be databse version problem?
What is the version of your mongodb? and where you executing this query?
MongoDB shell version v3.4.24. Direcly from mongo shell : assert: command failed: { "ok" : 0, "errmsg" : "Unrecognized expression '$mergeObjects'", "code" : 168, "codeName" : "InvalidPipelineOperator" } : aggregate failed _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:16:14 DBCollection.prototype.aggregate@src/mongo/shell/collection.js 2020-12-09T13:08:32.619+0000 E QUERY [thread1] Error: command failed: { "errmsg" : "Unrecognized expression '$mergeObjects'",
Okay $mergeObjects support from version 3.6 and also $first start from 4.4, i will update my answer for 3.4 version soon.
@MarcoMartin I have updated the answer for version 3.4 you can check.

Collectives™ on Stack Overflow

MongoDB aggregate merging fields

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related