Mongodb 如何限制筛选子文档中的条目

Answer is at bottom of my question个

我就快得到我想要的了.但就是有点不对劲.

我想要返回的是所有活动的狗舍，如果有任何狗舍有预订(S)，特定年份(2024年)，一组日期(如[100,101,102,103])和一组房间(如[1，2])

这是一张Mongo playground美元的钞票

这就是我现在得到的

[
        {
            "_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
                }
            ]
        }
]

这是我like要得到的-所有狗舍，AND标识符为"1.1.6x10"的狗舍应该显示2024-like年的单一预订，(for clarity - bookings for any Kennel with a booking on 2024 - 100 should also be returned, but for this example data, only the 1.1.6x10 kennel has a booking)

[
        {
            "_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": []
        }
    ]

以下是我正在使用的MongoDB管道，它聚合在‘kennels’上

  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] } },
          //
          // this is my problem area
          //{ '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
}

谢谢你！

ANSWER

这就是管用的管道--感谢https://stackoverflow.com/users/1431750/aneroid推动我走上正确的道路！

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,
      },
    },
  ];