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.

turivishal · Accepted Answer · 2020-11-01 17:21:45Z

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

Thanks turivishal, but your solution considers only the videos that the user has already seen, instead I need to have a list of ALL the videos, joining the fields of the users collection (users.videos) obviously only if present. In practice, if there were 300 videos in the video collection and the user had only seen 10, I need to have the data of all 300 videos, of which 10 will also have that user's custom fields.

Collectives™ on Stack Overflow

mongodb - merge nested object array into one keeping all data

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related