MongoDB聚合：筛选子文档条目并返回全部活跃Kennel数据

我已接近实现需求，但仍有偏差。

我需要返回所有活跃的Kennel，若某Kennel存在指定年份（2024）、指定dayOfYear数组（如[100,101]）及指定房间数组（如[1,2]）的预订，则返回符合条件的预订条目；若无符合条件的预订，则预订数组为空。

当前返回结果 #

[
        {
            "_id": "65ef79a2331ab6aef4fae5d4",
            "identifier": "1.1.6x10",
            "active": true,
            "partition": "1",
            "length": 10,
            "width": 6,
            "bookings": [
                {
                    "_id": "65ef88f444e7d6607498ac2e",
                    "year": 2024,
                    "dayOfYear": 100,
                    "duration": 6
                },
                {
                    "_id": "65f0ca69f2667460e600a46a",
                    "year": 2024,
                    "dayOfYear": 107,
                    "duration": 1
                }
            ]
        }
]

期望返回结果 #

所有活跃Kennel，其中identifier为"1.1.6x10"的Kennel仅显示2024年dayOfYear为100的单条预订（注：仅该Kennel存在符合条件的预订）

[
        {
            "_id": "65ef79a2331ab6aef4fae5d4",
            "identifier": "1.1.6x10",
            "active": true,
            "partition": "1",
            "length": 10,
            "width": 6,
            "bookings": [
                {
                    "_id": "65ef88f444e7d6607498ac2e",
                    "year": 2024,
                    "dayOfYear": 100,
                    "duration": 6
                }
            ]
        },
        {
            "_id": "65ef79a2331ab6aef4fae5d5",
            "identifier": "1.2.6x10",
            "active": true,
            "partition": "2",
            "length": 10,
            "width": 6,
            "bookings": []
        },
        {
            "_id": "65ef79a3331ab6aef4fae5e5",
            "identifier": "2.1.4x10",
            "active": true,
            "partition": "1",
            "length": 10,
            "width": 4,
            "bookings": []
        },
        {
            "_id": "65ef79a3331ab6aef4fae5e6",
            "identifier": "2.2.4x10",
            "active": true,
            "partition": "2",
            "length": 10,
            "width": 4,
            "bookings": []
        },
        {
            "_id": "65ef79a3331ab6aef4fae5e7",
            "identifier": "2.3.4x10",
            "active": true,
            "partition": "3",
            "length": 10,
            "width": 4,
            "bookings": []
        }
    ]

当前使用的聚合管道 #

let pipeline = [
    {
      $lookup: {
        from: 'rooms',
        localField: 'REF_RoomID',
        foreignField: '_id',
        as: 'room',
      },
    },
    {
      $unwind: {
        path: '$room',
        preserveNullAndEmptyArrays: false,
      },
    },
    {
      $lookup: {
        from: 'bookings',
        localField: '_id',
        foreignField: 'REF_KennelID',
        as: 'bookings',
      },
    },
    {
      $match: {
        $and: [
          { active: true },
          { 'room.number': { $in: [1,2] } },
          //
          // 这是我的问题所在区域
          //{ 'bookings.year': { $eq: 2024 } },
          //{ 'bookings.dayOfYear': { $eq: 100 } },
        ],
      },
    },
    {
      $project: {
        REF_RoomID: 0,
        room: 0,
        'bookings.REF_KennelID': 0,
        'bookings.__v': 0,
      },
    },
  ];

相关数据 #

Rooms集合 #

{
  "_id":  "65ef799f331ab6aef4fae5ba",
  "number": "1",
  "length": "10",
  "width": "12"
},
{
  "_id": "65ef79a0331ab6aef4fae5be",
  "number": "2",
  "length": "10",
  "width": "12"
}

Kennels集合 #

{
  "_id": "65ef79a2331ab6aef4fae5d4",
  "REF_RoomID":  "65ef799f331ab6aef4fae5ba",
  "identifier": "1.1.6x10",
  "active": true,
  "partition": "1",
  "length": 10,
  "width": 6
},
{
  "_id": "65ef79a2331ab6aef4fae5d5",
  "REF_RoomID": "65ef799f331ab6aef4fae5ba",
  "identifier": "1.2.6x10",
  "active": true,
  "partition": "2",
  "length": 10,
  "width": 6
},
{
  "_id": "65ef79a3331ab6aef4fae5e5",
  "REF_RoomID": "65ef79a0331ab6aef4fae5be",
  "identifier": "2.1.4x10",
  "active": true,
  "partition": "1",
  "length": 10,
  "width": 4
},
{
  "_id": "65ef79a3331ab6aef4fae5e6",
  "REF_RoomID": "65ef79a0331ab6aef4fae5be",
  "identifier": "2.2.4x10",
  "active": true,
  "partition": "2",
  "length": 10,
  "width": 4
},
{
  "_id": "65ef79a3331ab6aef4fae5e7",
  "REF_RoomID": "65ef79a0331ab6aef4fae5be",
  "identifier": "2.3.4x10",
  "active": true,
  "partition": "3",
  "length": 10,
  "width": 4
}

Bookings集合 #

{
  "_id": "65ef88f444e7d6607498ac2e",
  "REF_KennelID": "65ef79a2331ab6aef4fae5d4",
  "year": 2024,
  "dayOfYear": 100,
  "duration": 6
},
{
  "_id": "65f0ca69f2667460e600a46a",
  "REF_KennelID": "65ef79a2331ab6aef4fae5d4",
  "year": 2024,
  "dayOfYear": 107,
  "duration": 1
}

以下是可实现需求的聚合管道：

let pipeline = [
    {
      $match: {
        active: true,
      },
    },
    {
      $lookup: {
        from: "rooms",
        localField: "REF_RoomID",
        foreignField: "_id",
        as: "room",
      },
    },
    {
      $match: {
        "room.number": {
          $in: ["1","2"],
        },
      },
    },
    {
      $unwind: {
        path: "$room",
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $lookup: {
        from: "bookings",
        localField: "_id",
        foreignField: "REF_KennelID",
        pipeline: [
          {
            $match: {
              year: {
                $in: [2024],
              },
              dayOfYear: {
                $in: [100],
              },
            },
          },
        ],
        as: "bookings",
      },
    },
    {
      $unwind: {
        path: "$bookings",
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $group: {
        _id: "$_id",
        identifier: {
          $first: "$identifier",
        },
        active: {
          $first: "$active",
        },
        partition: {
          $first: "$partition",
        },
        length: {
          $first: "$length",
        },
        width: {
          $first: "$width",
        },
        bookings: {
          $push: "$bookings",
        },
      },
    },
    {
      $project: {
        "bookings.REF_KennelID": 0,
        "bookings.__v": 0,
      },
    },
    {
      $sort: {
        identifier: 1,
      },
    },
  ];

内容的提问来源于stack exchange，提问作者MrLister