Aggregation issue

Working with Data
,
1

I have 3 tables name doctor, weekdays, hospitals,

in the doctor table 
[
{_id:"610d0f36a793342c08b49b0a",
hospitals:[
{_id:"6166c2ea807d823f20722d52", hospital:"6166c2ea807d823f20722d4f"},
{_id:"61cd446d20c97e302c667e89", hospital:"61cd446d20c97e302c667e87"}
]}
]

Weekdays table 
[
{_id:"6166c2ea807d823f20722d4f", hospitalId:"615d4ebc5521472af0aae53d", fee:"800"},
{_id:"61cd446d20c97e302c667e87", hospitalId:"615d4fdb21df0d32c011adbd", fee:"1000"}

]

hospitals table
[
{_id:"615d4ebc5521472af0aae53d", hospitalName:"ABC"},
{_id:"615d4fdb21df0d32c011adbd", hospitalName:"ABC"}
]

I have used this aggregation

const data = await Doctor.aggregate([
       {
          $lookup: {
            from: "weekdays",
            localField: "hospitals.hospital",
            foreignField: "_id",
            as: "hospitals"
          },
    },
     
        { $project: { hospitals: 1 } },

      ]);

But did not got my desired results.

All I want is to fetch doctors with hospitals with a lte and gte fee range
Please guide me how I can achieve the desired to fetch the doctors with hospitals with fee range filter.

2

How to fetch the doctors with fee range?

3

Hi @Muhammad_Shaheer, welcome to the community.
Have you tried using the expressive $lookup?
Here’s the syntax for the same:

{
   $lookup:
      {
         from: <joined collection>,
         let: { <var_1>: <expression>, …, <var_n>: <expression> },
         pipeline: [ <pipeline to run on joined collection> ],
         as: <output array field>
      }
}

Therefore you will be able to join the collections as well as you will be able to filter the results using $match stage inside the pipeline.

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha

4

I have tried this

{  $lookup: {
            from: "weekdays",
            as:"hospitals",
            let:{hospitals:"hospitals"},
            pipeline:[],
      }, },
   { $project: { hospitals: 1 } },
   ]);

But this returns data like that
{_id:“610d0f36a793342c08b49b0a”,

hospitals:{
hospital:[
{fee:’'800"},
{fee:“1000”}
]

}

}

5

How to achieve the result like that
{
_id:“610d0f36a793342c08b49b0a”,
hospitals:[
hospital:{fee:“800”},
hospital:{fee:“1000”}
]

}

6

Your pipeline is empty, please add the condition to match the documents between both the collection and also add that fee condition as well.

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha

7

It returns All records in the weekdays for each doctor hospitals field
when pipeline is empty.

8

The pipeline should not be empty, please follow this:

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha

9

Which condition do I need to add to fetch the hospitals related to a doctor.

const data = await Doctor.aggregate([
    { $match: { location: location, specialization: specialization } },

        {
          $lookup: {
            from: "weekdays",
            as:"hospitals",
            let:{hospitals:"$hospitals"},
            pipeline:[{$match:{fee:{$eq:"800"}}}],
          },
        },
        { $project: { hospitals: 1 } },

      ]);
10

You need to add the condition to match the doctor’s hospital field with weekdays’ hospitalId field.

const data = await Doctor.aggregate([
  {
    $unwind: "$hospitals"
  },
  {
    $lookup: {
      from: "weekdays",
      let: {hospId: "hospitals.hospital"},
      pipeline: [
        {
          $match: {
            hospitalId: "$$hospId",
            fee: "800"
          }
        }
      ],
      as: "hospitals"
    },
  }
]);

@Muhammad_Shaheer, I would recommend you to take the M121: The MongoDB Aggregation Framework course if you want to get a better understanding.

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha,
Curriculum Services Engineer

11

I have data hospitals:[{_id:‘123’, hospitall:‘222’},{_id:‘124’,hospital:‘3333’}]
I have used the aggregation

agregation([{$lookup:{from:"weekdays",localField:"hospitals.hospital", foreignField:"_id" , as :"hospitals"}}])
it returns data like this
[
{
_id:123,
fields from other table
},
{
_id:124,
fields from other table
}]

how to get data like this

[
{
_id:123,
hospital:{
fields from other table
}
},
{
_id:124,
hospital:{fields from other table}
}]
12

Hi @Muhammad_Shaheer, seems like you data model is not in its optimal state as per your requirements.
Have you considered improving your data model?
I would recommend taking up the M320: Data Modeling to get a better understanding of improving your data model to optimize your queries.

In case you have any doubts, please feel free to reach out to us.

Thanks and Regards.
Sourabh Bagrecha,
Curriculum Services Engineer

13

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.