MongoDB如何使用管道变量生成动态$or条件？

问题背景 #

需要在MongoDB聚合查询的$match阶段，基于当前文档的$offers字段动态生成匹配条件，筛选出可换班的文档——即目标文档的from/to/location/type与当前文档的条件互相兼容。通过外部参数传入offers时，可借助JavaScript的map方法生成$or条件，但在聚合管道内基于文档自身$offers实现该逻辑时，尝试$in、$map、$lookup等操作均未成功。

数据示例 #

[
  {
    _id: ObjectId("id1"),
    from: ISODate("2023-01-21T06:30:00.000Z"),
    to: ISODate("2023-01-21T18:30:00.000Z"),
    matchStatus: 0,
    matchId: null,
    userId: ObjectId("ddbb8f3c59cf13467cbd6a532"),
    organisationId: ObjectId("246afaf417be1cfdcf55792be"),
    location: "Chertsey",
    type: "DCA",
    offers: [
      {
        from: ISODate("2023-01-23T05:00:00.000Z"),
        to: ISODate("2023-01-24T07:00:00.000Z"),
        locations: ["Chertsey", "Walton"],
        types: ["DCA", "SRV"],
      }
    ]
  },
  {
    _id: ObjectId("id2"),
    from: ISODate("2023-01-23T06:30:00.000Z"),
    to: ISODate("2023-01-23T18:30:00.000Z"),
    matchStatus: 0,
    matchId: null,
    userId: ObjectId("d6f10351dd8cf3462e3867f56"),
    organisationId: ObjectId("246afaf417be1cfdcf55792be"),
    location: "Chertsey",
    type: "DCA",
    offers: [
      {
        from: ISODate("2023-01-21T05:00:00.000Z"),
        to: ISODate("2023-01-21T07:00:00.000Z"),
        locations: ["Chertsey", "Walton"],
        types: ["DCA", "SRV"],
      }
    ]
  }
]

预期结果 #

当查询id1对应的文档时，输出应包含id2的文档，因为两者的换班条件互相兼容。

最终实现方案 #

采用自查找（$lookup）结合$anyElementTrue实现双向匹配，完整聚合查询代码如下：

db.swaps.aggregate([
  {
    $match: {},
  },
  {
    $unwind: "$offers",
  },
  {
    $lookup: {
      from: "swaps",
      as: "matches",
      let: {
        parentId: "$_id",
        parentOrganisationId: "$organisationId",
        parentUserId: "$userId",
        parentLocations: "$offers.locations",
        parentTypes: "$offers.types",
        parentOffersFrom: "$offers.from",
        parentFrom: "$from",
        parentTo: "$to",
        parentOffersTo: "$offers.to",
        parentLocation: "$location",
        parentType: "$type",
      },
      pipeline: [
        {
          $match: {
            matchStatus: 0,
            matchId: null,
            $expr: {
              $and: [
                {
                  $ne: ["$_id", "$$parentId"],
                },
                {
                  $ne: ["$userId", "$$parentUserId"],
                },
                {
                  $eq: [
                    "$organisationId",
                    "$$parentOrganisationId",
                  ],
                },
                {
                  $in: ["$location", "$$parentLocations"],
                },
                {
                  $in: ["$type", "$$parentTypes"],
                },
                {
                  $lte: ["$$parentOffersFrom", "$from"],
                },
                {
                  $gte: ["$$parentOffersTo", "$to"],
                },
                {
                  $anyElementTrue: {
                    $map: {
                      input: "$offers",
                      as: "offer",
                      in: {
                        $and: [
                          {
                            $in: [
                              "$$parentLocation",
                              "$$offer.locations",
                            ],
                          },
                          {
                            $in: [
                              "$$parentType",
                              "$$offer.types",
                            ],
                          },
                          {
                            $lte: [
                              "$$offer.from",
                              "$$parentFrom",
                            ],
                          },
                          {
                            $gte: [
                              "$$offer.to",
                              "$$parentTo",
                            ],
                          },
                        ],
                      },
                    },
                  },
                },
              ],
            },
          },
        },
        {
          $lookup: {
            from: "users",
            localField: "userId",
            foreignField: "_id",
            as: "matchedUser",
          },
        },
        {
          $set: {
            matchedUser: {
              $ifNull: [
                {
                  $first: "$matchedUser",
                },
                null,
              ],
            },
          },
        },
      ],
    },
  },
  {
    $group: {
      _id: "$_id",
      doc: {
        $first: "$$ROOT",
      },
      matches: {
        $push: "$matches",
      },
      offers: {
        $push: "$offers",
      },
    },
  },
  {
    $set: {
      matches: {
        $reduce: {
          input: "$matches",
          initialValue: [],
          in: {
            $concatArrays: ["$$value", "$$this"],
          },
        },
      },
    },
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$doc",
          {
            matches: "$matches",
            offers: "$offers",
          },
        ],
      },
    },
  },
  {
    $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "user",
    },
  },
  {
    $set: {
      user: {
        $ifNull: [
          {
            $first: "$user",
          },
          null,
        ],
      },
    },
  },
  {
    $sort: {
      _id: 1,
    },
  },
]);

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