mongodb - merge nested object array into one keeping all data

Question

I have 2 collections in mongodb 4.4.0. I have a video collection that contains all the videos and some related fields, and a collection called users where I store all the users and a list of videos they have completed, including the notes they have placed on the individual video. Now I have to get a list of all the videos, combined with the fields of the videos contained in the user collection, only obviously in cases where there is correspondence

Videos

{
  "_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa01"),
  "title": "Video title 1",
  "duration" : 120,
  "author" : "John Doe"
},
{
  "_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa02"),
  "title": "Video title 2",
  "duration" : 180,
  "author" : "Maria Hernandez"
},
{
  "_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa03"),
  "title": "Video title 3",
  "duration" : 75,
  "author" : "Henry Ford"
}

Users (only one for demo purpose)

{
"_id": ObjectId("bbbbbbbbbbbbbbbbbbbbbb01"),
"fullname": "James Smith",
"videos":
 [
  {
    "video_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa01"),
    "views": 12,
    "completed": true,
    "notes": "very fun video",
    "tags": ["fun", "best"]
  },
  {
    "video_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa02"),
    "views": 64,
    "completed": false,
    "notes": "very difficult to understand",
    "tags": ["hard", "training", "sport"]
  }
 ]
}

Desired result merged objects, assuming I'm filtering for user bbbbbbbbbbbbbbbbbbbbbb01:

{
    "_id": ObjectId("bbbbbbbbbbbbbbbbbbbbbb01"),
    "fullname": "James Smith",
    "videos":
     [
      {
        "video_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa01"),
        "title": "Video title 1",
        "duration" : 120,
        "author" : "John Doe"
        "views": 12,
        "completed": true,
        "notes": "very fun video",
        "tags": ["fun", "best"]
      },
      {
        "video_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa02"),
        "title": "Video title 2",
        "duration" : 180,
        "author" : "Maria Hernandez"
        "views": 64,
        "completed": false,
        "notes": "very difficult to understand",
        "tags": ["hard", "training", "sport"]
      },
      {
        "video_id": ObjectId("aaaaaaaaaaaaaaaaaaaaaa03"),
        "title": "Video title 3",
        "duration" : 75,
        "author" : "Henry Ford"
      }
     ]
    }

I searched a lot on google and SO for example: MongoDB - $lookup in complex nested array or Merge two array objects together in Mongodb from $Lookup

I'm trying to use $ lookup and $ mergeObjects but I just can't get the result I want.

Answer 1

You can try,

• $group by null and make users array

db.users.aggregate([
  {
    $group: {
      _id: null,
      users: { $push: "$$ROOT" }
    }
  },

• $lookup with videos collection and get all videos in videos array

  {
    $lookup: {
      from: "videos",
      pipeline: [],
      as: "videos"
    }
  },

• $project to update users.videos array
• $map to to iterate loop of users array, merge current object and videos array that re coming from inner map,
• $map to iterate loop of videos array, that was from videos collection
• $reduce to iterate loop of users.videos loop, match condition if video_id match then return video object and merge with video, otherwise return blank object.

  {
    $project: {
      users: {
        $map: {
          input: "$users",
          as: "user",
          in: {
            $mergeObjects: [
              "$$user",
              {
                videos: {
                  $map: {
                    input: "$videos",
                    as: "video",
                    in: {
                      $mergeObjects: [
                        "$$video",
                        {
                          $reduce: {
                            input: "$$user.videos",
                            initialValue: {},
                            in: {
                              $cond: [
                                { $eq: ["$$video._id", "$$this.video_id"] },
                                "$$this",
                                "$$value"
                              ]
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },

• $unwind deconstruct users array
• $replaceRoot to replace users object in root

  { $unwind: "$users" },
  { $replaceRoot: { newRoot: "$users" } }
])

Playground