Mongodb join with an array to string

kikme

248 Points

12 Posts

I want to do a aggregation with localField as an array and foreignField is a scalar element. For example:

db.classes.insert( [
   { _id: 1, memberList: [ "giraffe2", "pandabear", "artie" ]},
   { _id: 2, memberList: [ "giraffe1", "artie" ]}
])

db.members.insert( [
   { _id: 1, name: "artie" },
   { _id: 2, name: "giraffe" },
   { _id: 3, name: "giraffe1" },
   { _id: 4, name: "panda" },
   { _id: 5, name: "pandabear" },
   { _id: 6, name: "giraffe2" }
])

I want output document from above two like following:

{
   "_id" : 1,
   "memberList" : [ "giraffe2", "pandabear", "artie" ],
   "member_info" : [
      { "_id" : 1, "name" : "artie" },
      { "_id" : 5, "name" : "pandabear" },
      { "_id" : 6, "name" : "giraffe2" }
   ]
}
{
   "_id" : 2,
   "memberList" : [ "giraffe1", "artie" ],
   "member_info" : [
      { "_id" : 1, "name" : "artie"},
      { "_id" : 3, "name" : "giraffe1"}
   ]
}

c#.netcore-3.1 mongodb mongodb-driver join

Views: 3087
Total Answered: 2
Total Marked As Answer: 2
Posted On: 20-Aug-2021 00:37

Share:

Answers

Stevan

312 Points

20 Posts

Use following lookup query:

db.classes.aggregate([
   {
      $lookup:
         {
            from: "members",
            localField: "memberList",
            foreignField: "name",
            as: "member_info"
        }
   }
])

Posted On: 21-Aug-2021 08:01

Thanks

- kikme 23-Aug-2021 08:01

Rahul M...

4918 Points

28 Posts

With MongoDb.Driver we can try something like:

IAggregateFluent<ClassesWithMember> queryable = mongoDBService.GetCollection("classes").Aggregate()
            .Lookup("members", "memberList", "name", @as: "member_info")
            .As<ClassesWithMember>();

Where ClassesWithMember as:

public class ClassesWithMember{
  public string _id { get; set; }
  public string[] memberList { get; set; }
  public member_info[] member_info { get; set; }
}

public class member_info{
  public string _id { get; set; }
  public string name{ get; set; }
}

Posted On: 23-Aug-2021 04:42

Thanks

- kikme 23-Aug-2021 08:01