title: MongoDB - Aggregate two arrays by nesting object from one collection into array of objects of another collection
body: <p>I'm trying to properly write a query that will join objects from two collections. In one collection (A) I have objects with fields and array of users for example, each one of the user objects has the userDetailsId that I'm going to use when joining/merging each one of the users with each one of his userDetails from Collection B.</p>
  
  <p>Collection A:</p>
  
  <pre><code>{
      "_id": "1",
      "field1": "value1",
      "field2": "value2",
      "users": [
          {
              "userField1": "value3",
              "userField2": "value4",
              "userDetailsId": "100"
          },{
              "userField1": "value5",
              "userField2": "value6",
              "userDetailsId": "200"
          }
      ]
  }
  </code></pre>
  
  <p>Collection B:</p>
  
  <pre><code>{
      "_id": "100",
      "field1": "value1",
      "field2": "value2",
      "object1": {
          "field3": "value3",
          "field4": "value4"
      },
      "object2": {
          "field5": "value5",
          "field6": "value6"
      }
  },
  {
      "_id": "200",
      "field1": "value1",
      "field2": "value2",
      "object1": {
          "field3": "value3",
          "field4": "value4"
      },
      "object2": {
          "field5": "value5",
          "field6": "value6"
      }
  }
  </code></pre>
  
  <p>Preferred result:</p>
  
  <pre><code>{
      "_id": "1",
      "field1": "value1",
      "field2": "value2",
      "users": [
          {
              "userField1": "value3",
              "userField2": "value4",
              "userDetailsId": "100",
              "userDetails":{
                  "_id": "100",
                  "field1": "value1",
                  "field2": "value2",
                  "object1": {
                      "field3": "value3",
                      "field4": "value4"
                  },
                  "object2": {
                      "field5": "value5",
                      "field6": "value6"
                  }
              }
          },{
              "userField1": "value5",
              "userField2": "value6",
              "userDetailsId": "200",
              "userDetails":{
                  "_id": "200",
                  "field1": "value1",
                  "field2": "value2",
                  "object1": {
                      "field3": "value3",
                      "field4": "value4"
                  },
                  "object2": {
                      "field5": "value5",
                      "field6": "value6"
                  }
              }
          }
      ]
  }
  </code></pre>
  
  <p>One of the queries i have written but without getting preferred result:</p>
  
  <pre><code>db.collectiona.aggregate([{$match: {'_id':1}},{$lookup:{from:"collectionb", localField: "users.userdetailsid", foreignField: "_id", as:"userdetails"}}
  </code></pre>
  
  <p>If I run this I will just merge these two collections and will have all the matched userdetails objects from collection B inside the result as an array of userdetails.</p>
  
  <p>Since my goal here is to merge users and their userdetails in user objects I then tried to put <strong>as: "users.userdetails"</strong> but that will replace user with userdetails data (haven't found a possible way to put upsert to true and add whole userdetails object in user when userdetailsid is matched).</p>
  
  <p>After that I then tried to pipe the result of aggregation and tried to put userdetails inside user object with query </p>
  
  <pre><code>db.collectiona.aggregate([{$match: {'_id':1}},{$lookup:{from:"collectionb", localField: "users.userdetailsid", foreignField: "_id", as:"userdetails"},
  {"$addFields":{
      "user.userdetails":{
          "$map":{
              "input": "$shifts",
              "in":{
                  "$mergeObjects":["$$this",{"$arrayElemAt":["$collectionb",{"$indexOfArray":["$collectionb.id","$$this.userdetailsid"]}]}]
                  }
              }
          }
      }}
  }
  </code></pre>
  
  <p>But this was also not good.</p>
  
  <p>Do you have any ideas how could I properly write query to get this kind of a result?</p>