Playground

I have a simple array with 6 documents, one of them has a status of 1. I want to grab the value of a prop from the doc with this status and filter out the others docs with this value but the status 0.

ex.

{ 
    "InviteId": 1,   <================================= Keep
    "InviteStatus": 1,
    "PartnerCompany" : "Monstra",
    "MeetingDate" : "2021-09-05T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
},
{ 
    "InviteId": 2,
    "InviteStatus": 0,
    "PartnerCompany" : "Empresa Teste", 
    "MeetingDate" : "2021-09-05T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
},
{ 
    "InviteId": 3, <================================= Exclude
    "InviteStatus": 0, 
    "PartnerCompany" : "Monstra",
    "MeetingDate" : "2021-09-07T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
},
{ 
    "InviteId": 4,
    "InviteStatus": 0,
    "PartnerCompany" : "Empresa Teste", 
    "MeetingDate" : "2021-09-07T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
},
{ 
    "InviteId": 5,  <================================= Exclude
    "InviteStatus": 0,
    "PartnerCompany" : "Monstra",
    "MeetingDate" : "2021-09-06T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
},
{ 
    "InviteId": 6,
    "InviteStatus": 0,
    "PartnerCompany" : "Empresa Teste", 
    "MeetingDate" : "2021-09-06T15:00:00.000 0000", 
    "MeetingStartTime" : "12:55", 
}

In this example, InviteId: 1 has status: 1, this doc has de partnerName: Monstra. So I need to exclude the others docs where partnerName == Monstra with status == 0.

So the final list would have 4 docs (InviteIds: 1,2,4,6) and not 6 as it´s today.

cheers

CodePudding user response：

If you are using MongoDB 5.0,

You can use $setWindowFields to aggregate a "rank" for the "partition" / "group" (i.e. PartnerCompany in your example) and only choose the document with top rank. The ranking can be defined as InviteStatus: -1 as you want to keep the InviteStatus: 1 record with highest rank.

{
    "$setWindowFields": {
      "partitionBy": "$PartnerCompany",
      "sortBy": {
        "InviteStatus": -1
      },
      "output": {
        "rank": {
          $rank: {}
        }
      }
    }
  }

Here is the Mongo playground for your reference.

If you are using Mongo 3.6

You can do a self-lookup on the PartnerCompany field. In the subpipeline, keep only records that have InviteStatus larger than your source document in lookup.

case 1: source document have InviteStatus: 1; no document will have InviteStatus larger than you, so your lookup will return empty array

case 2: source document have InviteStatus: 0; your lookup will return non-empty array

Filter on the lookup array to keep/prune source document.

{
    "$lookup": {
      "from": "collection",
      let: {
        pc: "$PartnerCompany",
        currStatus: "$InviteStatus"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$pc",
                    "$PartnerCompany"
                  ]
                },
                {
                  $lt: [
                    "$$currStatus",
                    "$InviteStatus"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "checkingResult"
    }
  }

Here is the Mongo playground for your reference

CodePudding user response：

If you have >= Mongodb 5 the $setWindowFields solution looks perfect.

If not MongoDB 5, try the bellow also, because if not Mongodb 5, lookup with pipeline and $expr cant use the index as far as i know so it will be slow, you can test them to be sure.

Query

Test code here

aggregate(
[{"$group": 
    {"_id": "$PartnerCompany",
      "max": {"$max": "$InviteStatus"},
      "docs": {"$push": "$$ROOT"}}},
  {"$set": 
    {"docs": 
      {"$cond": 
        [{"$eq": ["$max", 1]}, [{"$arrayElemAt": ["$docs", 0]}], "$docs"]}}},
  {"$unwind": {"path": "$docs"}},
  {"$replaceRoot": {"newRoot": "$docs"}}])