MongoDB: $lookup returns empty array [duplicate]

Question

I have two models, user and schedule, and i want to combine these two using $lookup and mongoose.

User (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
    },
    password: {
        passwordHash: String,
        salt: String
    },
    active: {
        type: Boolean,
        default: true
    },
    deleted: {
        type: Boolean,
        default: false
    },
    generalObservations: {
        type: String
    },
    from: {
        type: String
    },
    services: {
        type: Number,
        default: 0
    },
    no_shows: {
        type: Number,
        default: 0
    },
    // campos para integração
    integrationId: String
}, {
    timestamps: true

Schedule (model)

 store: {
    type: String,
    required: true
  },
  customer: {
    id: {
      type: ObjectId
    },
    name: {
      type: String,
      required: true
    },
    avatar: String,
    phone: {
      type: String
    },
    doc: {
      type: String
    },
  },
  employee: {
    id: {
      type: String,
      required: true
    },
    name: {
      type: String,
      required: true
    },
    avatar: String,
  },
  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
  },
  value: {
    type: Number,
    required: true
  },
  comissionType: {
    type: String,
    default: '$'
  },
  comissionValue: {
    type: Number,
    default: 0
  },
  status: {
    type: Number,
    required: true
  },
  observation: String,
  paymentMethod: {
    type: Number,
    default: 0
  },
  paymentValue: String,
  paymentChange: String
}, {
  timestamps: {
    createdAt: 'created',
    updatedAt: 'updated'
  }

And now my query using mongoose:

User aggregate

  User.aggregate([{
      $match: {
        storeKey: req.body.store,    
      }
    },
    {
      $group: {
        _id: {
          id: "$_id",
          name: "$name",
          cpf: "$cpf",      
          phone: "$phone",
          email: "$email",
          birthday: "$birthday"      
        },     
        totalServices: {
          $sum: "$services"
        }
      }
    },
    {
      $lookup: {
        from: "schedule",
        localField: "_id.id",
        foreignField: "customer.id",
        as: "user_detail"
      } 
    }

the result of my query is a empty array(user_detail) like this:

Result of the query:

{
        "_id": {
            "id": "5bdb5b16ee9b7a4aa810bc62",
            "name": "Jonas com aniversário",
            "phone": "11984798494",
            "email": "[email protected]",
            "birthday": "Thu Nov 01 2018 16:59:18 GMT-0300 (Hora oficial do Brasil)"
        },
        "totalServices": 0,
        "user_detail": []
    }

I don't know why, but the query result is a empty array and i was trying to using $unwind and $match but doesn't work too.

EDIT:

Collection of user

{
        "_id": "5b1b1dcce1ab9a12a8eb580f",
        "password": {
            "salt": "d095f2",
            "passwordHash": "b24881ef4c43d28e93bcff5da2ce32e4287aabf77540d2465482a435a5929a63f2ba9fb7e1cc14fa4e8183d83e33854ec6153fbbb872e65a9e3f188892bf56cc"
        },
        "name": "Anderson Zanardi",
        "cpf": "31933765828",
        "phone": "11996370565",
        "birthday": "1984-03-18",
        "email": "[email protected]",
        "storeKey": "5b16cceb56a44e2f6cd0324b",
        "createdAt": "2018-06-09T00:22:36.464Z",
        "updatedAt": "2018-11-06T13:51:37.261Z",
        "__v": 0,
        "doc": "31933765828",
        "active": true,
        "from": "app",
        "deleted": false,
        "services": 80
    },

Collection of schedule

{
        "_id": "5b1c20d8fc76f904849712c9",
        "customer": {
            "id": "789456",
            "name": "Gabriel Barreto",
            "phone": "11995274098",
            "cpf": "40735255814"
        },
        "employee": {
            "id": "5b16cebd29bcf613f02b6fb4",
            "name": "Anderson Zanardi",
            "avatar": ""
        },
        "service": {
            "filters": [
                "corte_simples",
                "corte_masculino"
            ],
            "id": "service_id",
            "name": "Corte Masculino"
        },
        "store": "5b16cceb56a44e2f6cd0324b",
        "scheduleDate": "2018-06-07",
        "scheduleStart": "2018-06-28 13:00",
        "scheduleEnd": "2018-06-28 13:30",
        "status": 1,
        "value": 50,
        "created": "2018-06-09T18:47:52.862Z",
        "updated": "2018-06-09T18:47:52.862Z",
        "__v": 0
    },

Ashh · Accepted Answer · 2018-11-12 17:38:32Z

14

Mongoose pluralize the collection name at the time of creation. So instead of schedule you should use schedules

{ "$lookup": {
  "from": "schedules",
  "localField": "_id.id",
  "foreignField": "customer.id",
  "as": "user_detail"
}}

or either import the collection and extract the collection name from it

const Schedule = require('/schedules')

{ "$lookup": {
  "from": Schedule.collection.name,
  "localField": "_id.phone",
  "foreignField": "customer.phone",
  "as": "user_detail"
}}

answered Nov 12, 2018 at 17:38

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AbdulRehman Over a year ago

pluralization worked for me. You saved my day

Collectives™ on Stack Overflow

MongoDB: $lookup returns empty array [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related