CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
Naruto
Naruto

Reputation: 109

Mongodb lookup aggregation not getting all field values

I have two collections which are of schema like

driver

_id:5f9c1d897ea5e246945cd73a
agent_name:"Ratnabh Kumar Rai"
agent_email:"[email protected]"
agent_phone:"70****63331"

and

reviews

_id:5f9d54cb3a3ee10c6829c0a4
order_id:"5f9d40f096e4a506e8684aba"
user_id:"5f9bcb66f7a5bf4be0ad9973"
driver_id:"5f9c1d897ea5e246945cd73a"
rating:3
text:""

so i want to calculate the avg driver rating . I tried to use lookup aggregation so that i can get all details and then later calculate the sum...what i did was

let doc = await db
        .collection("drivers")
        .aggregate([
        
          {
            $project: {
              _id: {
                $toString: "$_id",
              },
            },
          },
          {
            $lookup: {
              from: "reviews",
              localField: "_id",
              foreignField: "driver_id",
              as: "driver_info",
            },
          },
          {
            $project: {
              agent_email: 1,
              orderReview: "$driver_info",
            },
          },
        ])
        .toArray();

But i am getting result as

 {
    _id: '5f9d63eb8737e82fbc193dd9',
     orderReview: [ [Object], [Object], [Object], [Object], [Object] ]
   }

which is partially correct as i also need to get details from my localfield collection that is drivers field, as of now you can see i am only getting id of driver in my projection i also did "agent_email:1" but not getting email

Upvotes: 1

Views: 802

Answers (1)

ibrahimijc
ibrahimijc

Reputation: 341

You're actually only projecting _id in the first pipeline and hence only _id is passed to further pipelines, If you need email in further pipelines you need to project it too

    let doc = await db.collection("drivers").aggregate([
  {
    $project: {
      _id: {
        $toString: "$_id",
        
      },
      agent_email: "$agent_email"
    },
    
  },
  {
    $lookup: {
      from: "reviews",
      localField: "_id",
      foreignField: "driver_id",
      as: "driver_info",
      
    },
    
  },
  {
    $project: {
      agent_email: 1,
      orderReview: "$driver_info",
      
    },
    
  },
  
])

MongoDB PlayGround : https://mongoplayground.net/p/h7D-tYJ7sLU

[Update]

I realized that you're doing this for getting average and if you need it to be done in a single aggregate query, here it is how you can do it.

Using unwind operator you can flat the reviews array as objects and then group by _id and use the $avg aggregation operator

db.collection("drivers").aggregate([
  {
    $project: {
      _id: {
        $toString: "$_id",
        
      },
      agent_email: "$agent_email"
    },
    
  },
  {
    $lookup: {
      from: "reviews",
      localField: "_id",
      foreignField: "driver_id",
      as: "driver_info",
      
    },
    
  },
  // Makes the driver info flat with other information
  {
    "$unwind": "$driver_info"
  },
  {
    // Now you can group them 
    $group: {
      _id: "$_id",
      // Calculates avg on rating field
      avg: {
        "$avg": "$driver_info.rating"
      },
      // To make email field appear in next pipeline.
      // You can do it for other fields too like Name, etc
      agent_email: {
        $first: "$agent_email"
      }
    }
  },
  {
    $project: {
      // select the fields you want to display
      agent_email: 1,
      avg: 1
    },
    
  },
  
])

MonogoDb playground Link

Upvotes: 1

Related Questions