Grouping function problem after $lookup

Working with Data
1

if i need to perform qyery
Number of female and male patient with covid

the two collection are patients ( fname, lname, gender) and encounters (patient, reasondescription)

what is wrong with this query it retrieve the result but without grouping

db.encounters.aggregate([

{$match:{REASONDESCRIPTION:"COVID-19"}},

{

    $lookup: {

        from: "patients",

        let: { patientId:"$PATIENT" },

        pipeline: [

            {

                $match: {

                    $expr: {

                        $and: [

                            { $eq: ["$Id", "$$patientId"] },

                            

                        ],

                    },

                },

            },

            {$project: {GENDER :1}},

        ],

        as: "patientgender",

    },

},

{$group:{_id:"$patientgender", gendercount:{$sum:1} }},

{$project:{gendercount:1}}

])

another query to get the Number of encounters conducted by every provider

db.encounters.aggregate([

{

    $lookup: {

        from: "providers",

        let: { providerid: "$PROVIDER" },

        pipeline: [

            {

                $match: {

                    $expr: {

                        $and: [

                            { $eq: ["$Id", "$$providerid"] },

                        ],

                    },

                },

            },

        

         { $project: { Id:1,

                    NAME: 1} 

                        

         },

            

        ],        



        as: "PROVIDERNAMES",

    }, },

        {$group:{_id:"$PROVIDERNAMES",  PROVIDERARR:  {  $addToSet: "$Id"}}},

        {$project:{

            

            PROVIDERCOUNT: {$size : "$PROVIDERARR"} }

}

])

can any one help me where is the problem

2

Hello @Aya_Gamal, welcome to the MongoDB Community forum!

Your issue about:

what is wrong with this query it retrieve the result but without grouping

is because the variable value created from the as field of the $lookup (for example, the field "patientgender") is of type array. You need to introduce another aggregate stage before you can apply the $group on this field - and this is the $unwind stage. You unwind on the array field and then perform grouping on that field. Please try this and tell how it works.

It is the case with both the aggregate queries you had posted.

2 Likes
3

i had modified the query and the grouping

db.encounters.aggregate([

{$match:{REASONDESCRIPTION:"COVID-19"}},

{

    $lookup: {

        from: "patients",

        let: { patientId:"$PATIENT" },

        pipeline: [

            {

                $match: {

                    $expr: {

                        $and: [

                            { $eq: ["$Id", "$$patientId"] },

                            

                        ],

                    },

                },

            },

            {$project: {GENDER :1}},

        ],

        as: "patientgender",

    },

},

{ $unwind: {

    path: "$patientgender",

    preserveNullAndEmptyArrays: true

}

},

{$group:{_id:"$patientgender", gendercount:{ $addToSet:"$PATIENT"} }},

{$project:{PATIENT:1,



    TOTALGENDERCOUNT: {

        $size: "$gendercount"



                      }

}

}

])

but it has the same result , it give me the is and gender without grouping

for the second query it run for long time with no result . i think the problem is with grouping but i dont know what is it exaxtly

4

Please post a sample document output as it looks after the $lookup stage, so that I can get a better picture about what the issue is.

1 Like
5

i had modified the query and it work correctly thanks alot

db.encounters.aggregate([

{$match:

    {REASONDESCRIPTION:"COVID-19"}

  }, 

  {$project: { PATIENT:1}},

   {$lookup:  {from: "patients",

              let: { patientId:"$PATIENT" },

              pipeline: [

                  {

                      $match: {

                          $expr: {

                              $and: [

                                  { $eq: ["$Id", "$$patientId"] },

        

                                                              ],

                          },

                      },

                  },

                  {$project: {GENDER :1}},

              ],

              as: "patientgender",

        

                           }

  

  }, {$unwind: {

  

      path: "$patientgender",

  

      preserveNullAndEmptyArrays: true

  

  }}, 

      {$group: {_id:"$patientgender.GENDER", gendercount:{$sum:1} }},

{$project:{gendercount:1}}

])