javascript - Mongoose: $project after $lookup don't show a field

I have two models user.js and schedule.js and I have a query (aggregate) that i need to use $lookup to "join" these models. After the $lookup i'm using a $project that select the fields that i want to show in the result of my query, but the fields scheduleStart and scheduleEnd don't show in my result.

User.js (model)

  name: {
        type: String,
        required: true
    },
    firstName: {
        String
    },
    lastName: {
        String
    },
    storeKey: {
        type: String,
        required: true
    },
    avatar: String,
    birthday: String,
    phone: {
        type: String
    },
    doc: String,
    email: {
        type: String
    },...

Schedule.js (model)

 service: {
    id: {
      type: String
    },
    name: {
      type: String,
      required: true
    },
    filters: [String]
  },
  info: {
    channel: {
      type: String,
      required: true,
      default: 'app'
    },
    id: String,
    name: String
  },
  scheduleDate: {
    type: String,
    required: true
  },
  scheduleStart: {
    type: String,
    required: true
  },
  scheduleEnd: {
    type: String,
    required: true
  },

My query

 User.aggregate([{
      $match: {
        storeKey: req.body.store,     
      }
    },
    {
      $group: {
        _id: {
          id: "$_id",
          name: "$name",
          cpf: "$cpf",      
          phone: "$phone",
          email: "$email",
          birthday: "$birthday",
          lastName: "$lastname"      
        },
        totalServices: {
          $sum: "$services"
        },    
      }
    },
    {
      $lookup: {
        from: "schedules",
        localField: "_id.phone",
        foreignField: "customer.phone",
        as: "user_detail"
      }  
    },  
    {
      $project: {
        _id: 1,
        name: 1,
        name: 1,
        cpf: 1,      
        phone: 1,
        email: 1,
        birthday: 1,
        totalServices: 1,
        totalValue: { "$sum": "$user_detail.value" },
        scheduleStart: 1,
        scheduleEnd: 1,
        count: {
          $sum: 1
        }
      }
    }   
  ])...

Result of my query:

count: 1
totalServices: 89
totalValue: 2374
_id:{
birthday: "1964-03-18",
cpf: "319335828",
email: "jdoe@gmail.com.br",
id: "5b1b1dcce1ab2a8eb580f",
name: "Jonh Doe",
phone: "11996370565"
}

1 Answer

  1. Kevin- Reply

    2019-11-13

    You can use below $project stage with $arrayElemAt aggregation

    { '$project': {
      '_id': 1,
      'name': 1,
      'cpf': 1,      
      'phone': 1,
      'email': 1,
      'birthday': 1,
      'totalServices': 1,
      'totalValue': { '$sum': '$user_detail.value' },
      'scheduleStart': { '$arrayElemAt': ['$user_detail.scheduleStart', 0] },
      'scheduleEnd': { '$arrayElemAt': ['$user_detail.scheduleEnd', 0] },
      'count': { '$sum': 1 }
    }} 
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>