mongodb - MongoDB:需要在查找中匹配两个字段

我已为 nuitregNumber_id 编制索引以获得更快的查询。

您可以找到我不满意的示例数据和解决方案,因为使用 $unwind$lookup 会使其迭代一百万个文档:https://mongoplayground.net/p/jR4Y-9-5As7

这就是我所做的:

db.reservations.aggregate([
    { $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
    { $sort: { _id: -1 } },
    { $limit: 1 },
    {
        $lookup: {
            from: "parks",
            localField: "packageId",
            foreignField: "packages._id",
            as: "park"
        }
    }
])

这是结果:

{
        "_id" : ObjectId("6284c8915a5e7a6a6ec675c1"),
        "nuit" : "400400400",
        "packageId" : ObjectId("627bfee7c528a41f5fd74664"),
        "regNumber" : "aha-720-mp",
        "date" : {
                "entry" : ISODate("2022-05-25T10:20:00Z"),
                "exit" : ISODate("2022-05-29T10:20:00Z")
        },
        "park" : [
                {
                        "_id" : "400400400",
                        "name" : "One Stop",
                        "spaces" : "50",
                        "createdAt" : ISODate("2022-05-11T14:25:44.816Z"),
                        "contacts" : {
                                "mainPhoneNumber" : "800000000",
                                "optionalPhoneNumber" : "800000000",
                                "province" : "1",
                                "district" : "matola a",
                                "avenue" : "filipe",
                                "quarterNumber" : "12",
                                "residenceNumber" : "1",
                                "floorNumber" : "1"
                        },
                        "packages" : [
                                {
                                        "_id" : ObjectId("627bfc95834d42107eeefc93"),
                                        "name" : "Basic One",
                                        "isPeriodic" : 0,
                                        "createdAt" : ISODate("2022-05-11T18:12:37.024Z"),
                                        "articles" : [
                                                {
                                                        "_id" : ObjectId("627e462842a6d1357a37613d"),
                                                        "period" : "00:00:00:01:00",
                                                        "price" : NumberDecimal("100")
                                                },
                                                {
                                                        "_id" : ObjectId("627e4ede3c0615d81a3783b3"),
                                                        "period" : "00:01:00:00:00",
                                                        "price" : NumberDecimal("1700")
                                                },
                                                {
                                                        "_id" : ObjectId("627e4f4f3c0615d81a3783b4"),
                                                        "period" : "00:00:00:01:30",
                                                        "price" : NumberDecimal("200.99")
                                                },
                                                {
                                                        "_id" : ObjectId("627e4f692ffd3313694bf88d"),
                                                        "period" : "00:00:01:20:00",
                                                        "price" : NumberDecimal("300.5")
                                                }
                                        ]
                                },
                                {
                                        "_id" : ObjectId("627bfee7c528a41f5fd74664"),
                                        "name" : "Anual básico",
                                        "isPeriodic" : 0,
                                        "createdAt" : ISODate("2022-05-11T18:22:31.624Z")
                                },
                                {
                                        "_id" : ObjectId("627c16b5b91ad0563d22f8eb"),
                                        "name" : "básico",
                                        "isPeriodic" : 0,
                                        "createdAt" : ISODate("2022-05-11T20:04:05.462Z")
                                },
                                {
                                        "_id" : ObjectId("627c16e028c58a28175e7327"),
                                        "name" : "Anual",
                                        "isPeriodic" : 0,
                                        "createdAt" : ISODate("2022-05-11T20:04:48.863Z")
                                }
                        ]
                }
        ]
}

在将 nuit_id 以及 packageIdreservationsparks 集合中的 packages._id 进行匹配时,我需要的结果分别是:

{
    "_id": ObjectId("6284c8915a5e7a6a6ec675c1"),
    "nuit": "400400400",
    "packageId": ObjectId("627bfee7c528a41f5fd74664"),
    "regNumber": "aha-720-mp",
    "date": {
        "entry": ISODate("2022-05-25T10:20:00Z"),
        "exit": ISODate("2022-05-29T10:20:00Z")
    },
    "park": 
        {
            "packages": 
                {
                    "_id": ObjectId("627bfee7c528a41f5fd74664"),
                    "name": "Anual básico",
                    "isPeriodic": 0,
                    "createdAt": ISODate("2022-05-11T18:22:31.624Z")
                }
        }
}

回答1

根据您提供给我们的数据,您可以执行以下操作:

db.collection.aggregate([
    { $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
    { $sort: { _id: -1 } },
    { $limit: 1 },
    {
        $lookup: {
            from: "parks",
            localField: "nuit",
            foreignField: "_id",
            as: "park"
        }
    },
  {
    $set: {
      park: {
        $map: {
          input: "$park",
          as: "elem",
          in: {
            packages: {
              $filter: {
                input: "$$elem.packages",
                as: "item",
                cond: {$eq: ["$$item._id", "$packageId"]}
              }
            }
          }
        }
      }
    }
  }
])

https://mongoplayground.net/p/PhF3Ph_wFaS

由于 park 是一个数组,而 packages 是一个嵌套数组,并且根据您要求的输出,您希望它保持这种状态,您可以 $map 外部数组(park)并使用 $filter 仅保留元素packages 里面符合你的条件。

编辑:如果保证只有一个预期的 package 结果,正如在问题中编辑的那样,那么这是一个更简单的情况,它不需要 $map 内的 $filter,而只是一个简单的 $arrayElemAt 和一个 $filter

db.collection.aggregate([
 { $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
    { $sort: { _id: -1 } },
    { $limit: 1 },
    {
        $lookup: {
            from: "parks",
            localField: "nuit",
            foreignField: "_id",
            as: "park"
        }
    },
  {$set: {res: {$arrayElemAt: ["$park", 0]}, park: null}},
  {
    $set: {
      "park.packages": {
        $filter: {
          input: "$res.packages",
          as: "item",
          cond: {$eq: [ "$$item._id", "$packageId"]}
        }
      },
      res: null
    }
  },
  {$set: {'park.packages': {$arrayElemAt: [ "$park.packages", 0]}}},
  {$unset: "res", "park.packages.articles"}
])

https://mongoplayground.net/p/I2yVTsnCCGJ

回答2

我最终得到了 https://mongoplayground.net/p/4UwwZrOaTJj。感谢@nimrod,我发现了$filter

因为 packages._idparks 集合中都是唯一的,所以我已经对其进行了索引和过滤。然后,我在js中手动检查了nuitif(nuit === lastInsertedReservation.park._id)。这是我使用 explain(true) 可以获得的最快查询。

db.reservations.aggregate([
  {$match: {nuit: "400400400",regNumber: "aha-720-mp"}},
  {$sort: {_id: -1}},
  {$limit: 1},
  {
    $lookup: {
      from: "parks",
      let: {
        pid: "$packageId"
      },
      localField: "packageId",
      foreignField: "packages._id",
      pipeline: [
        {
          $project: {
            packages: {
              $filter: {
                input: "$packages",
                as: "package",
                cond: {$eq: ["$$package._id","$$pid"]}
              },
            }
          }
        },
        {$project: {packages: {"articles": 0}}
        },
        {$unwind: "$packages"},
      ],
      as: "park"
    }
  },
  {$unwind: "$park"}
])

相似文章

java - 连接MongoDB

早上好。我在连接到MongoDB数据库时遇到问题。我在docker-compose.yml文件中为自己配置了一个数据库和mongoexpress。使用CommandLineRunner,我想将示例数据...

最新文章