How to join multiple colletion as one colletion with own shape of final document?-CodePudding

I have 3 collections

Users

{
 "_id": "P4SpYVd1KjBaF4SKyVw0E",
  "login": "User-01",
  "name": "John",
  "lastName": "Doe",
}

Moods
which is time series collection

{
 "source": {
     "userId": "P4SpYVd1KjBaF4SKyVw0E",
  },
  "timestamp": "2022-06-11T12:44:13.333Z",
  "mood": "bad",
  "_id": "62a352b83859aaf975c6332d",
}

contactRequest
Also is time series collection

{
  "timestamp": "2022-06-11T15:25:13.333Z",
  "source" : {
     "userId":"P4SpYVd1KjBaF4SKyVw0E"
   },
  "resolve": false,
  "_id": "62a351ff3859aaf975c63329"
}

I want to achieve the final document which will looks like this:
Final

{
 "_id": "P4SpYVd1KjBaF4SKyVw0E",
  "login": "User-01",
  "name": "John",
  "lastName": "Doe",
  "calendar": [
      {
        "timestamp": "2022-06-11T12:44:13.333Z"       //which is moods.timestamp,
        "mood": {
            "source": {
              "userId": "P4SpYVd1KjBaF4SKyVw0E",
             },
             "timestamp": "2022-06-11T12:44:13.333Z",
             "mood": "bad",
             "_id": "62a352b83859aaf975c6332d",
         },
         "contactRequest": [
             {
               "timestamp": "2022-06-11T15:25:13.333Z", //contactRequest is grouping by date with moods
               "source" : {
                 "userId":"P4SpYVd1KjBaF4SKyVw0E"
               },
               "resolve": true,
               "_id": "62a351ff3859aaf975c63329"
             },
             {
               "timestamp": "2022-06-11T18:23:13.333Z", 
               "source" : {
                 "userId":"P4SpYVd1KjBaF4SKyVw0E"
               },
               "resolve": false,
               "_id": "62a351ff3859aaf975c63329"
             },
         ]
        }
      }
   ]
}

I've tried aggregate it with $lookup parameter and it gave me two separate fields in User document, which is unsatisfying.
I've also used $project parameter, but problem is with group results by date.
Does it possible to achieve that, aggregate multiply collection in final own shape document ? Was someone trying to do it in the past??

Here's a mongoplayground.net attempt that doesn't return everything correctly.

CodePudding user response：

Using the question's mongoplayground as a starting point, I modified it a bit to return the document as requested.

db.users.aggregate([
  {
    "$match": {
      // id comes from param
      "_id": "P4SpYVd1KjBaF4SKyVw0E"
    }
  },
  {
    "$lookup": {
      "from": "moods",
      "localField": "_id",
      "foreignField": "source.userId",
      "pipeline": [
        {
          "$lookup": {
            "from": "contactRequests",
            "localField": "source.userId",
            "foreignField": "source.userId",
            "let": {
              // truncate timestamp to start of day
              "moodsDate": { "$dateTrunc": { "date": "$timestamp", "unit": "day" } }
            },
            "pipeline": [
              {
                "$match": {
                  "$expr": {
                    "$eq": [
                      "$$moodsDate",
                      { // truncate timestamp to start of day
                        "$dateTrunc": { "date": "$timestamp", "unit": "day" }
                      }
                    ]
                  }
                }
              }
            ],
            "as": "contactRequests"
          }
        },
        {
          // don't return mood doc if no requests
          "$match": {
            "$expr": { "$gt": [ { "$size": "$contactRequests" }, 0 ] }
          }
        }
      ],
      as: "calendar"
    }
  }
])

Try it on mongoplayground.net.