How to add a property as an object in grouped array (mongodb aggregation)?-CodePudding

I'm new in mongoose and stuck with some problem. I have 2 instances: Patient and Appointment. They look like:

const AppointmentSchema = new mongoose.Schema({
  patient: { type: Schema.Types.ObjectId, ref: 'Patient' },
  dentNumber: Number,
  diagnosis: String,
  price: Number,
  date: String,
  time: String
}, {
  timestamps: true
});

const PatientSchema = new mongoose.Schema({
  fullname: String,
  phone: String,
}, {
  timestamps: true
});

PatientSchema.virtual('Appointments', {
  ref: 'Appointment',
  localField: '_id',
  foreignField: 'patient',
  justOne: false
})

Appointments data looks like this:

{
    "success": true,
    "data": [
        {
            "_id": "61cb1cad610963c75f7e41a2",
            "patient": {
                "_id": "61c045bcb0c7e68c96e6ba0e",
                "fullname": "test 126",
                "phone": " 7 123 54 55",
                "createdAt": "2021-12-20T08:58:36.776Z",
                "updatedAt": "2021-12-20T08:58:36.776Z",
                "__v": 0
            },
            "dentNumber": 33,
            "diagnosis": "String3",
            "price": 500,
            "date": "18-10-2021",
            "time": "15:50",
            "createdAt": "2021-12-28T14:18:21.537Z",
            "updatedAt": "2021-12-28T14:24:29.576Z",
            "__v": 0
        },
        {
            "_id": "61cc26bd7d1e9476e52c4b35",
            "patient": {
                "_id": "61c045bcb0c7e68c96e6ba0e",
                "fullname": "test 126",
                "phone": " 7 123 54 55",
                "createdAt": "2021-12-20T08:58:36.776Z",
                "updatedAt": "2021-12-20T08:58:36.776Z",
                "__v": 0
            },
            "dentNumber": 4,
            "diagnosis": "String3",
            "price": 1600,
            "date": "17-10-2021",
            "time": "16:50",
            "createdAt": "2021-12-29T09:13:33.575Z",
            "updatedAt": "2021-12-29T09:13:33.575Z",
            "__v": 0
        },
        {
            "_id": "61cc3663970d00f9129d0456",
            "patient": {
                "_id": "61c045bcb0c7e68c96e6ba0e",
                "fullname": "test 126",
                "phone": " 7 123 54 55",
                "createdAt": "2021-12-20T08:58:36.776Z",
                "updatedAt": "2021-12-20T08:58:36.776Z",
                "__v": 0
            },
            "dentNumber": 5,
            "diagnosis": "String4",
            "price": 1700,
            "date": "18-10-2021",
            "time": "17:50",
            "createdAt": "2021-12-29T10:20:19.257Z",
            "updatedAt": "2021-12-29T10:20:19.257Z",
            "__v": 0
        },
     ]
}

I need to sort Appointments by date in this way:

  await Appointment
    .aggregate([
      { $group: {
        _id: "$date",
          data: { $push: {
            id: "$_id",
            patientId: "$patient",
            //patient: { patient: await Patient.findById(mongoose.Types.ObjectId.fromString("$patient")) },
            dentNumber: "$dentNumber",
            diagnosis: "$diagnosis",
            price: "$price",
            date: "$date",
            time: "$time"
          } }
      } },
      { $sort: { _id: 1 } },
    ])

After this I get an answer in proper form:

{
    "success": true,
    "data": [
        {
            "_id": "17-10-2021",
            "data": [
                {
                    "id": "61cb1cad610963c75f7e41a2",
                    "patientId": "61c045bcb0c7e68c96e6ba0e",
                    "dentNumber": 33,
                    "diagnosis": "String3",
                    "price": 500,
                    "date": "17-10-2021",
                    "time": "15:50"
                },
                {
                    "id": "61d1651b25ab055c5cc913ac",
                    "patientId": "61be3e6b51b968aa92d5e248",
                    "dentNumber": 5,
                    "diagnosis": "String4",
                    "price": 1750,
                    "date": "17-10-2021",
                    "time": "19:35"
                }
            ]
        },
        {
            "_id": "18-10-2021",
            "data": [
                {
                    "id": "61cc26bd7d1e9476e52c4b35",
                    "patientId": "61c045bcb0c7e68c96e6ba0e",
                    "dentNumber": 4,
                    "diagnosis": "String3",
                    "price": 1600,
                    "date": "18-10-2021",
                    "time": "16:50"
                },
            ]
        },

But instead of property "patientId" I need somehow get a full object Patient. To make the data look like this:

{
    "success": true,
    "data": [
        {
            "_id": "17-10-2021",
            "data": [
                {
                    "id": "61cb1cad610963c75f7e41a2",
                    "dentNumber": 33,
                    "diagnosis": "String3",
                    "price": 500,
                    "date": "17-10-2021",
                    "time": "15:50",
                    "patient": {
                        "_id": "61c045bcb0c7e68c96e6ba0e",
                        "fullname": "test 126",
                        "phone": " 7 123 54 55",
                        "createdAt": "2021-12-20T08:58:36.776Z",
                        "updatedAt": "2021-12-20T08:58:36.776Z",
                        "__v": 0
                    }
                },
                {
                    "id": "61d1651b25ab055c5cc913ac",
                    "dentNumber": 5,
                    "diagnosis": "String4",
                    "price": 1750,
                    "date": "17-10-2021",
                    "time": "19:35",
                    "patient": {
                        "_id": "61be3e6b51b968aa92d5e248",
                        "fullname": "test 421",
                        "phone": " 7 123-11-22",
                        "createdAt": "2021-12-18T20:02:51.177Z",
                        "updatedAt": "2021-12-28T14:41:45.948Z",
                        "__v": 0
                    }
                }
            ]
        },
        {
            "_id": "18-10-2021",
            "data": [
                {
                    "id": "61cc26bd7d1e9476e52c4b35",
                    "dentNumber": 4,
                    "diagnosis": "String3",
                    "price": 1600,
                    "date": "18-10-2021",
                    "time": "16:50",
                    "patient": {
                        "_id": "61c045bcb0c7e68c96e6ba0e",
                        "fullname": "test 126",
                        "phone": " 7 123 54 55",
                        "createdAt": "2021-12-20T08:58:36.776Z",
                        "updatedAt": "2021-12-20T08:58:36.776Z",
                        "__v": 0
                    }
                }
            ]
        },
    ]
}

CodePudding user response：

You can not execute another query by passing the internal field in any query, You need to do a lookup operation before the group,

$lookup with patient collection, pass patient as localField and pass _id as foreignField
$group stage, use $first to get first element from lookup result in patient

await Appointment.aggregate([
  {
    $lookup: {
      from: "patient",
      localField: "patient", // change to your exact collection name
      foreignField: "_id",
      as: "patient"
    }
  },
  {
    $group: {
      _id: "$date",
      data: {
        $push: {
          id: "$_id",
          patientId: { $first: "$patient._id" },
          patient: { $first: "$patient" },
          dentNumber: "$dentNumber",
          diagnosis: "$diagnosis",
          price: "$price",
          date: "$date",
          time: "$time"
        }
      }
    }
  },
  { $sort: { _id: 1 } }
])

Playground