populate() incorrectly populates virtual fields in an embedded subdocument array, when using sort options, depending on the number of matches

[sharang-lyric]

Prerequisites

• I have written a descriptive issue title
• I have searched existing issues to ensure the bug has not already been reported

Mongoose version

7.6.3

Node.js version

18.16.1

MongoDB server version

6.0.11

Typescript version (if applicable)

No response

Description

I have a model Store with an array property employees where each element is a nested schema EmployeeSchema. I have another model Shift which contains details of the shifts for every employee. The schema EmployeeSchema has a virtual called mostRecentShift with Shift as a reference. mostRecentShift uses option justOne: true and sort option startedAt: -1.

When calling store.populate('employees.mostRecentShift'), when there is exactly one shift for each employee, mostRecentShift is incorrectly populated with the other employee's shift.

Steps to Reproduce

The following code shows the behavior of populate in two cases:

1. When one employee has 1 shift and the other employee has 2 shifts
2. When both employees have 1 shift each

import mongoose from 'mongoose';

const uri = process.env.MONGO_URI;

const shiftSchema = new mongoose.Schema({
  employeeId: mongoose.Types.ObjectId,
  startedAt: Date,
  endedAt: Date
});

const Shift = mongoose.model('Shift', shiftSchema);

const employeeSchema = new mongoose.Schema({
  name: String,
});

employeeSchema.virtual('mostRecentShift', {
  ref: Shift,
  localField: '_id',
  foreignField: 'employeeId',
  options: {
    sort: { startedAt: -1 },
  },
  justOne: true,
});

const storeSchema = new mongoose.Schema({
  location: String,
  employees: [employeeSchema],
});

const Store = mongoose.model('Store', storeSchema)

mongoose.connect(uri);

const store = await Store.create({
  location: 'Tashbaan',
  employees: [
    { name: 'Aravis' },
    { name: 'Shasta' },
  ],
});

const employeeAravis = store.employees.find(({ name }) => name === 'Aravis');
const employeeShasta = store.employees.find(({ name }) => name === 'Shasta');

Shift.insertMany([
  { employeeId: employeeAravis._id, startedAt: new Date(Date.now() - 57600000), endedAt: new Date(Date.now() - 43200000) },
  { employeeId: employeeAravis._id, startedAt: new Date(Date.now() - 28800000), endedAt: new Date(Date.now() - 14400000) },
  { employeeId: employeeShasta._id, startedAt: new Date(Date.now() - 14400000), endedAt: new Date() },
])

const storeWithMostRecentShifts = await Store.findOne({ location: 'Tashbaan' })
  .populate('employees.mostRecentShift')
  .select('-__v')
  .exec();

// When Aravis has two shifts, mostRecentShift is populated correctly for both employees
console.log('When Aravis has 2 shifts and Shasta has 1 shift')
console.log(JSON.stringify(storeWithMostRecentShifts.toJSON({ virtuals: true }).employees, null, 4));

// Now I remove Aravis's oldest shift, so that each employee has exactly one shift
await Shift.findOne({ employeeId: employeeAravis._id }).sort({ startedAt: 1 }).then((s) => s.deleteOne());

// Re-populating mostRecentShift 
const storeWithMostRecentShiftsNew = await Store.findOne({ location: 'Tashbaan' })
  .populate('employees.mostRecentShift')
  .select('-__v')
  .exec();

// Now, mostRecentShift is populated incorrectly, with Aravis's shift under Shasta's mostRecentShift, and vice-versa
console.log('When both employees have 1 shift each')
console.log(JSON.stringify(storeWithMostRecentShiftsNew.toJSON({ virtuals: true }).employees, null, 4));

Output:
(Note how employee's id does not match mostRecentShift.employeeId when there is exactly one shift for each)

When Aravis has 2 shifts and Shasta has 1 shift
[
    {
        "name": "Aravis",
        "_id": "653fb100b070c8eb99bc0882",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0887",
            "employeeId": "653fb100b070c8eb99bc0882",
            "startedAt": "2023-10-30T05:35:00.277Z",
            "endedAt": "2023-10-30T09:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0887"
        },
        "id": "653fb100b070c8eb99bc0882"
    },
    {
        "name": "Shasta",
        "_id": "653fb100b070c8eb99bc0883",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0888",
            "employeeId": "653fb100b070c8eb99bc0883",
            "startedAt": "2023-10-30T09:35:00.277Z",
            "endedAt": "2023-10-30T13:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0888"
        },
        "id": "653fb100b070c8eb99bc0883"
    }
]
When both employees have 1 shift each
[
    {
        "name": "Aravis",
        "_id": "653fb100b070c8eb99bc0882",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0888",
            "employeeId": "653fb100b070c8eb99bc0883",
            "startedAt": "2023-10-30T09:35:00.277Z",
            "endedAt": "2023-10-30T13:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0888"
        },
        "id": "653fb100b070c8eb99bc0882"
    },
    {
        "name": "Shasta",
        "_id": "653fb100b070c8eb99bc0883",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0887",
            "employeeId": "653fb100b070c8eb99bc0882",
            "startedAt": "2023-10-30T05:35:00.277Z",
            "endedAt": "2023-10-30T09:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0887"
        },
        "id": "653fb100b070c8eb99bc0883"
    }
]

Expected Behavior

mostRecentShift should have been populated correctly when each employee had exactly one shift each, similar to when Aravis had 2 shifts.
Expected output:

When Aravis has 2 shifts and Shasta has 1 shift
[
    {
        "name": "Aravis",
        "_id": "653fb100b070c8eb99bc0882",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0887",
            "employeeId": "653fb100b070c8eb99bc0882",
            "startedAt": "2023-10-30T05:35:00.277Z",
            "endedAt": "2023-10-30T09:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0887"
        },
        "id": "653fb100b070c8eb99bc0882"
    },
    {
        "name": "Shasta",
        "_id": "653fb100b070c8eb99bc0883",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0888",
            "employeeId": "653fb100b070c8eb99bc0883",
            "startedAt": "2023-10-30T09:35:00.277Z",
            "endedAt": "2023-10-30T13:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0888"
        },
        "id": "653fb100b070c8eb99bc0883"
    }
]
When both employees have 1 shift each
[
    {
        "name": "Aravis",
        "_id": "653fb100b070c8eb99bc0882",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0887",
            "employeeId": "653fb100b070c8eb99bc0882",
            "startedAt": "2023-10-30T05:35:00.277Z",
            "endedAt": "2023-10-30T09:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0887"
        },
        "id": "653fb100b070c8eb99bc0882"
    },
    {
        "name": "Shasta",
        "_id": "653fb100b070c8eb99bc0883",
        "mostRecentShift": {
            "_id": "653fb104b070c8eb99bc0888",
            "employeeId": "653fb100b070c8eb99bc0883",
            "startedAt": "2023-10-30T09:35:00.277Z",
            "endedAt": "2023-10-30T13:35:00.277Z",
            "__v": 0,
            "id": "653fb104b070c8eb99bc0888"
        },
        "id": "653fb100b070c8eb99bc0883"
    }
]

[IslandRhythms]

const mongoose = require('mongoose');

const shiftSchema = new mongoose.Schema({
  employeeId: mongoose.Types.ObjectId,
  startedAt: Date,
  endedAt: Date
});

const Shift = mongoose.model('Shift', shiftSchema);

const employeeSchema = new mongoose.Schema({
  name: String,
});

employeeSchema.virtual('mostRecentShift', {
  ref: Shift,
  localField: '_id',
  foreignField: 'employeeId',
  options: {
    sort: { startedAt: -1 },
  },
  justOne: true,
});

const storeSchema = new mongoose.Schema({
  location: String,
  employees: [employeeSchema],
});

const Store = mongoose.model('Store', storeSchema)

async function run() {
  console.log(mongoose.version);
  await mongoose.connect('mongodb://localhost:27017');
  await mongoose.connection.dropDatabase();


  const store = await Store.create({
    location: 'Tashbaan',
    employees: [
      { name: 'Aravis' },
      { name: 'Shasta' },
    ],
  });

  const employeeAravis = store.employees.find(({ name }) => name === 'Aravis');
  const employeeShasta = store.employees.find(({ name }) => name === 'Shasta');

  await Shift.insertMany([
    { employeeId: employeeAravis._id, startedAt: new Date(Date.now() - 57600000), endedAt: new Date(Date.now() - 43200000) },
    { employeeId: employeeAravis._id, startedAt: new Date(Date.now() - 28800000), endedAt: new Date(Date.now() - 14400000) },
    { employeeId: employeeShasta._id, startedAt: new Date(Date.now() - 14400000), endedAt: new Date() },
  ])

  const storeWithMostRecentShifts = await Store.findOne({ location: 'Tashbaan' })
    .populate('employees.mostRecentShift')
    .select('-__v')
    .exec();

  // When Aravis has two shifts, mostRecentShift is populated correctly for both employees
  console.log('When Aravis has 2 shifts and Shasta has 1 shift')
  console.log(JSON.stringify(storeWithMostRecentShifts.toJSON({ virtuals: true }).employees, null, 4));

  // Now I remove Aravis's oldest shift, so that each employee has exactly one shift
  await Shift.findOne({ employeeId: employeeAravis._id }).sort({ startedAt: 1 }).then((s) => s.deleteOne());

  // Re-populating mostRecentShift 
  const storeWithMostRecentShiftsNew = await Store.findOne({ location: 'Tashbaan' })
    .populate('employees.mostRecentShift')
    .select('-__v')
    .exec();

  // Now, mostRecentShift is populated incorrectly, with Aravis's shift under Shasta's mostRecentShift, and vice-versa
  console.log('When both employees have 1 shift each')
  console.log(JSON.stringify(storeWithMostRecentShiftsNew.toJSON({ virtuals: true }).employees, null, 4));

}


run();

[prathamVaidya]

I want to work on this task.