Joining a collection to the same collection name. (MongoDB Query)

Question

Is it possible to join a collection to itself with a different $match used?

I have below the same used case

Data:

{ "_id" : 1, "transactioncode" : "testtransactAA", "staffcode" : "staffA1", "isactive" : true }
{ "_id" : 2, "transactioncode" : "testtransactBB", "staffcode" : "staffB1", "isactive" : true }
{ "_id" : 3, "transactioncode" : "testtransactCC", "staffcode" : "staffC1", "isactive" : false }
{ "_id" : 4, "transactioncode" : "testtransactCC", "staffcode" : "staffC2", "isactive" : false }
{ "_id" : 5, "transactioncode" : "testtransactDD", "staffcode" : "staffD1", "isactive" : true  }
{ "_id" : 6, "transactioncode" : "testtransactEE", "staffcode" : "staffE1", "isactive" : true  }
{ "_id" : 7, "transactioncode" : "testtransactEE", "staffcode" : "staffE2", "isactive" : false }

I want to display all unique transaction code and in the staff code there will be a condition that:

• If isactive is true, show the staffcode
• If a transaction code consist of 1 or more staffcode, show only staffcode who has isactive: true
• If a transaction code consist of 1 or more staffcode that have both isactive : false, make it null

Output will be:

{ "transactioncode" : "testtransactAA", "staffcode" : "staffA1"}
{ "transactioncode" : "testtransactBB", "staffcode" : "staffB1"}
{ "transactioncode" : "testtransactCC", "staffcode" : null}
{ "transactioncode" : "testtransactDD", "staffcode" : "staffD1"}
{ "transactioncode" : "testtransactDD", "staffcode" : "staffE1"}

My code here is i am trying to rejoin the same table name but it is only getting documents that have isactive:true and ignoring those documents that have staffcode with both false status

{
runCommand: {
    aggregate: "operationsmonitoring",
    pipeline: [


{ $lookup: { from: 'operationsmonitoring', localField:'_id', foreignField: '_id', as: 'getIsactive' } },
     { $unwind: { path: '$getIsactive', preserveNullAndEmptyArrays: true } },
     {$match:{'getIsactive.isactive':true } },

                {
    $project:{
"transactioncode":"$transactioncode",
"staffcode":"$staffcode"

            }
                }
],

"cursor": {batchSize:100000}

} }

Thank you in advance!

Answer 1

You can use sub-pipeline in $lookup to lookup isActive:true record only.

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {
        rawId: "$_id"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$rawId",
                    "$_id"
                  ]
                },
                {
                  $eq: [
                    "$isactive",
                    true
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "getIsactive"
    }
  },
  {
    $unwind: {
      path: "$getIsactive",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      "transactioncode": "$transactioncode",
      "staffcode": {
        "$ifNull": [
          "$getIsactive.staffcode",
          null
        ]
      }
    }
  },
  {
    $group: {
      _id: "$transactioncode",
      staffcode: {
        $first: "$staffcode"
      }
    }
  }
])

Here is the Mongo Playground for your reference.