Mongodb join with an array to string

kikme
kikme
34 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"}
   ]
}
Views: 137
Total Answered: 2
Total Marked As Answer: 2
Posted On: 20-Aug-2021 00:37

Share:   fb twitter linkedin
Answers
Stevan
Stevan
60 Points
25 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 Maurya
Rahul M...
Moderator
1314 Points
227 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
 Log In to Chat