CODEWITHSUNDEEP
mongodb
Ramadhoni Suryo
Ramadhoni Suryo

Reputation: 13

How to use distinct with aggregation

i have a problem to aggregate my data . i have a NOSQL mongodb like this :

db.m_menu_access.aggregate([
            { $lookup: { from: "m_role", localField: "m_role_id", foreignField: "_id", as: "role" }}, 
            { $lookup: { from: "m_menu", localField: "m_menu_id", foreignField: "_id", as: "menu" }}, 
            { $unwind: "$role" },
            { $unwind: "$menu" }, 
            { $project: {
                    "_id": 1,
                    "code": 1,
                    "is_delete" : 1,
                    "m_role_id": 1,
                    "createDate" : 1,
                    "createBy" : 1,
                    "role.code": 1,
                    "role.name": 1,
                    "role.description": 1,
                    "m_menu_id" : 1,
                    "menu.code" : 1,
                    "menu.name" :1, 
                    "menu.controller"  : 1
                }
            }
        ])

then, the result of the NOSQL is look like ths : 

{ 
    "_id" : ObjectId("5b7101bf2df1bb210c4b3a45"), 
    "m_role_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"), 
    "m_menu_id" : ObjectId("5b6bac237b69b3d4e3aedfeb"), 
    "role" : {
        "code" : "RO0001", 
        "name" : "Administrator", 
        "description" : "Admin Mar-Kom"
    }, 
    "menu" : {
        "code" : "ME0001", 
        "name" : "Master Menu", 
        "controller" : "menu_controller"
    }
}
{ 
    "_id" : ObjectId("5b7101bf2df1bb210c4b3a46"), 
    "m_role_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"), 
    "m_menu_id" : ObjectId("5b6bac477b69b3d4e3aedfec"), 
    "role" : {
        "code" : "RO0001", 
        "name" : "Administrator", 
        "description" : "Admin Mar-Kom"
    }, 
    "menu" : {
        "code" : "ME0002", 
        "name" : "Master User", 
        "controller" : "user_controller"
    }
}
{ 
    "_id" : ObjectId("5b7101bf2df1bb210c4b3a47"), 
    "m_role_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"), 
    "m_menu_id" : ObjectId("5b6bac607b69b3d4e3aedfed"), 
    "role" : {
        "code" : "RO0001", 
        "name" : "Administrator", 
        "description" : "Admin Mar-Kom"
    }, 
    "menu" : {
        "code" : "ME0003", 
        "name" : "Master Company", 
        "controller" : "company_controller"
    }
}

i want the NOSQL result is like this :

{       
        "_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"), 
        "code" : "RO0001", 
        "name" : "Administrator", 
        "description" : "Admin Mar-Kom"

        "menu" : 
        [
        {
            "m_menu_id" : ObjectId("5b6bac237b69b3d4e3aedfeb"),
            "code" : "ME0001", 
            "name" : "Master Menu", 
            "controller" : "menu_controller"
        },
        {
            "m_menu_id" : ObjectId("5b6bac477b69b3d4e3aedfec"), 
            "code" : "ME0002", 
            "name" : "Master User", 
            "controller" : "user_controller"
        },
        {
            "m_menu_id" : ObjectId("5b6bac607b69b3d4e3aedfed"), 
            "code" : "ME0003", 
            "name" : "Master Company", 
            "controller" : "company_controller"
        }
        ]
    }

so the "_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"), "code" : "RO0001", "name" : "Administrator", "description" : "Admin Mar-Kom" is from collection "m_role"

Can someone help me for solve this problem ?? thankyouu

Upvotes: 1

Views: 58

Answers (1)

dsharew
dsharew

Reputation: 10675

Add these stages to your query: 

{
   $addFields:{
      "menu.m_menu_id":"$m_menu_id"
   }
},
{
   $group:{
      _id:"$m_role_id",
      code:{
         $first:"$role.code"
      },
      name:{
         $first:"$role.name"
      },
      description:{
         $first:"$role.description"
      },
      menu:{
         $push:"$menu"
      }
   }
}

Output:

/* 1 */
{
    "_id" : ObjectId("5b6baba57b69b3d4e3aedfe9"),
    "code" : "RO0001",
    "name" : "Administrator",
    "description" : "Admin Mar-Kom",
    "menu" : [ 
        {
            "code" : "ME0001",
            "name" : "Master Menu",
            "controller" : "menu_controller",
            "m_menu_id" : ObjectId("5b6bac237b69b3d4e3aedfeb")
        }, 
        {
            "code" : "ME0002",
            "name" : "Master User",
            "controller" : "user_controller",
            "m_menu_id" : ObjectId("5b6bac477b69b3d4e3aedfec")
        }, 
        {
            "code" : "ME0003",
            "name" : "Master Company",
            "controller" : "company_controller",
            "m_menu_id" : ObjectId("5b6bac607b69b3d4e3aedfed")
        }
    ]
}

Upvotes: 1

Related Questions