Create view from 2 different collections in mongoDB

user_1683172 0
Viewed 134

I want to create a view from 2 different collections(info1, info2) in mongodb. I need to have LoginId, FirstName,LastName,Email from info1 collection and GroupName,Type,MachName from info2 collection. Connecting field between info1 and info2 collections are "GroupName" and "Group" respectively

Below the collection content:

info1:

{ 
    "GroupName" : "TEST Group1", 
    "LoginId" : "login1", 
    "FirstName" : "John", 
    "LastName" : "deo", 
    "Email" : "john.deo@xyz.com"
}
{ 
    "GroupName" : "TEST Group2", 
    "LoginId" : "login1", 
    "FirstName" : "John", 
    "LastName" : "deo", 
    "Email" : "john.deo@xyz.com"
}
{ 
    "GroupName" : "TEST Group2", 
    "LoginId" : "login2", 
    "FirstName" : "Mark", 
    "LastName" : "Clan", 
    "Email" : "mark.clan@xyz.com"
}

info2:

{
    "Group" : "TEST Group1", 
    "Type" : "DEV", 
    "ActiveFlag" : "True",
    "MachName" : "group1.xyz.net",
}
{
    "Group" : "TEST Group2", 
    "Type" : "DEV", 
    "ActiveFlag" : "True",
    "MachName" : "group2.xyz.net",
}
{
    "Group" : "TEST Group1", 
    "Type" : "UAT", 
    "ActiveFlag" : "True",
    "MachName" : "group1.xyz.net",
}
{
    "Group" : "TEST Group2", 
    "Type" : "UAT", 
    "ActiveFlag" : "True",
    "MachName" : "group2.xyz.net",
}

I want to have output as below from the view. Expected output:

{ 
    "GroupName" : ["TEST Group1", "TEST Group2"]
    "LoginId" : "login1", 
    "FirstName" : "John", 
    "LastName" : "deo", 
    "Email" : "john.deo@xyz.com",
    "Type" : "DEV", 
    "MachName" : ["group1.xyz.net","group2.xyz.net"]
},
{ 
    "GroupName" : ["TEST Group1", "TEST Group2"],
    "LoginId" : "login1", 
    "FirstName" : "John", 
    "LastName" : "deo", 
    "Email" : "john.deo@xyz.com",
    "Type" : "UAT", 
    "MachName" : ["group1.xyz.net","group2.xyz.net"]
},
{ 
    "GroupName" : ["TEST Group2"],
    "LoginId" : "login2", 
    "FirstName" : "Mark", 
    "LastName" : "Clan", 
    "Email" : "mark.clan@xyz.com",
    "Type" : "DEV", 
    "MachName" : ["group2.xyz.net"]
},
{ 
    "GroupName" : ["TEST Group2"]
    "LoginId" : "login2", 
    "FirstName" : "Mark", 
    "LastName" : "Clan", 
    "Email" : "mark.clan@xyz.com",
    "Type" : "UAT", 
    "MachName" : ["group2.xyz.net"]
}

I have tried with below but not able to get the expected output. Can someone please help me to have view which should give the expected output from the 2 collections(info1,info2)?

Tried code(not working):

db.getCollection("info1").aggregate(
    [
        { 
            "$lookup" : { 
                "from" : "info2", 
                "localField" : "GroupName", 
                "foreignField" : "Group", 
                "as" : "g"
            }
        }  , 
        { $match: { $and: [ {"g.ActiveFlag" : "True"} ] } },
        { 
            "$project" : { 
                "LoginId" : "$LoginId", 
                 "FirstName" : "$FirstName", 
                "LastName" : "$LastName", 
                "Email" : "$Email", 
                "machName" : "$g.MachName"
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);
aggregation-framework mongodb-query mongodb view json
1 Answers

Demo - https://mongoplayground.net/p/SX3xH1v_2wQ

Use $group

$first

$push

Groups input documents by the specified _id expression and for each distinct grouping, outputs a document. The _id field of each output document contains the unique group by value. The output documents can also contain computed fields that hold the values of some accumulator expression.

db.info1.aggregate([
  {
    "$lookup": {
      "from": "info2",
      "localField": "GroupName",
      "foreignField": "Group",
      "as": "g"
    }
  },
  {
    $match: {
      $and: [
        {
          "g.ActiveFlag": "True"
        }
      ]
    }
  },
  {
    $group: {
      _id: null, // you can goup by LoginId if you want
      GroupName: { $push: "$GroupName" },
      MachName: { $push: { $first: "$g.MachName" } },
      LoginId: { $first: "$LoginId" },
      FirstName: { $first: "$FirstName" },
      Email: { $first: "$Email" },
      Type: { $first: { $first: "$g.Type" } },
      
    }
  }
])

Update

Updated Demo - https://mongoplayground.net/p/0AgT3FJIB6P

Use $unwind on g after lookup pipeline

{ $unwind: "$g" }

Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

Demo - https://mongoplayground.net/p/IGzTzEbgfl0

$group: {
      _id: {
        Type: "$g.Type",
        LoginId: "$LoginId"
      }
     // ....
}
user_22242650
Related