Question 116156 in Mongodb Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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();

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search