How to get two collections output in a single document in MongoDb in c#?-CodePudding

Lets say I am having two collections named CollectionA and CollectionB, Both collection have different fields. CollectionA will have multiple documents with Same field, CollectionB contain only one document

Example

CollectionA
    {
       "UniqeId" :1,
       "Hobbies" : "Eating"
    },
    {
       "UniqeId" :2,
       "Hobbies" : "Sleeping"
    },
    {
       "UniqeId" :3,
       "Hobbies" : "Walking"
    }


CollectionB
    {
       "UserName" :"Sukuna",
       "UserType" : "Villan"
    }

I want output like this

{
   "UniqeId" :1,
   "Hobbies" : "Eating",
   "UserName" :"Sukuna",
   "UserType" : "Villan"
}

Consider All the documents in a CollectionA will contain same fields And you can see there is no Unique fields between the two collection, and you can see we need to apply filter in CollectionA ie) UniqeId=1

I am using C#, and I can able do two DB request to get those collection details (One req for CollectionA output and another one for CollectionB output) and manage to combine both in API level to get the desired output, but I want to do in DB level itself,

I don't want two DB calls, that is eating the API performance, so is there anyway to achieve this in a single DB call or by using any aggregate pipeline?. Thanks in Advance

CodePudding user response：

lookup with localField 1 and foreignField 1

db.a.aggregate([
  {
    $lookup: {
      from: "b",
      localField: "1",
      foreignField: "1",
      as: "docs"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          { $first: "$docs" }
        ]
      }
    }
  },
  {
    $unset: [ "docs", "_id" ]
  },
  {
    $group: {
      _id: "$UserName",
      doc: { $first: "$$ROOT" }
    }
  },
  {
    $replaceWith: "$doc"
  }
])

mongoplayground

CodePudding user response：

Finally after lots of trail and error and playing with pipeline, I can able to do that in aggregate pipeline, and I am using unionWith and group. Here is the C# code

var pipeline1= new BsonDocument("$unionWith", 
    new BsonDocument
        {
            { "coll", "CollectionB" }, 
            { "pipeline", 
    new BsonArray
            {
                new BsonDocument("$match", 
                new BsonDocument("UniqeId", 1))
            } }
        });

var pipeline2 = new BsonDocument("$group", 
    new BsonDocument
        {
            { "_id", 0 }, 
            { "merged", 
    new BsonDocument("$push", "$$ROOT") }
        });

var pipeline3 = new BsonDocument("$replaceRoot", 
    new BsonDocument("newRoot", 
    new BsonDocument("$mergeObjects", "$merged")));

var pipeline4 = new BsonDocument("$project", 
    new BsonDocument("_id", 0));


BsonDocument[] pipeline = new BsonDocument[] { pipeline1, pipeline2, pipeline3, pipeline4 };

var dbResponse = await collection.Aggregate<BsonDocument>(pipeline).ToListAsync();