I have two collections, viz: clib and mp. The schema for clib is : {name: String, type: Number} and that for mp is: {clibId: String}.

Sample Document for clib:

{_id: ObjectId("6178008397be0747443a2a92"), name: "c1", type: 1}
{_id: ObjectId("6178008397be0747443a2a91"), name: "c2", type: 0}

Sample Document for mp:

{clibId: "6178008397be0747443a2a92"}
{clibId:"6178008397be0747443a2a91"}

While Querying mp, I want those clibId's that have type = 0 in clib collection.

Any ideas how this can be achieved?

One approach that I can think of was to use $lookUp, but that doesnt seem to be working. Also, I m not sure if this is anti-pattern for mongodb, another approach is to copy the type from clib to mp while saving mp document.

CodePudding user response：

If I've understood correctly you can use a pipeline like this:

This query get the values from clib where its _id is the same as clibId and also has type = 0. Also I've added a $match stage to not output values where there is not any coincidence.

db.mp.aggregate([
  {
    "$lookup": {
      "from": "clib",
      "let": {
        "id": "$clibId"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": [
                    {
                      "$toObjectId": "$$id"
                    },
                    "$_id"
                  ]
                },
                {
                  "$eq": [
                    "$type",
                    0
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "result"
    }
  },
  {
    "$match": {
      "result": {
        "$ne": []
      }
    }
  }
])

Example here

CodePudding user response：

db.mp.aggregate([
  {
    $lookup: {
      from: "clib",
      let: {
        clibId: "$clibId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [ "$_id", "$$clibId" ],
                }
              ]
            }
          }
        },
        {
          $project: { type: 1, _id: 0 }
        }
      ],
      as: "clib"
    }
  },
  {
    "$unwind": "$clib"
  },
  {
    "$match": {
      "clib.type": 0
    }
  }
])

Test Here

CodePudding user response：

Both the above answers solved my problem. I marked the second one as the answer since I decided to use that. Thank You.