Question 117919 in Mongodb Question posted in
The official documentation can be found here.

MongoDB: Need to match two fields in a lookup

I’ve indexed nuit, regNumber and _id for a faster query.

You can find sample data and a solution which i’m not happy with because using $unwind and $lookup makes it iterate a million documents: https://mongoplayground.net/p/jR4Y-9-5As7

This is what i did:

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"
        }
    }
])

This is the result:

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

The result i need, while matching nuit with _id, and packageId with packages._id from reservations and parks collections, respectively, is:

{
    "_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")
                }
        }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up with https://mongoplayground.net/p/4UwwZrOaTJj. Thanks to @nimrod, i found out about $filter.

    Because packages._id are all unique in parks collection, i've indexed it and filtered it. Then, i checked the nuit manually in js: if(nuit === lastInsertedReservation.park._id). This was the fastest query i could get using 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"}
])

  2. 0

    From the data you gave us, you can do something like:

    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"]}
              }
            }
          }
        }
      }
    }
  }
])

    mongoDB playground.

    Since park is an array and packages is a nested array, and according to your requested output you want it to stay that way, you can $map the external array (park) and use $filter to keep only elements inside packages that meet your condition.

    Edit:
    If it is guaranteed that there is only one expected package result, as been edited in the question, than this is a simpler case that does not require a a $filter inside a $map, but just a simple $arrayElemAt and a $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"}
])

    Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search