Filter results in mongoDb based on status-CodePudding

I have an array like this

[
{
    "name": "CAMP-1",
    "status": "incomplete",
    "version": 3,
  },
  {
    "name": "CAMP-1",
    "status": "complete",
    "version": 2,
  },
  {
    "name": "CAMP-1",
    "status": "complete",
    "version": 1,
  },
  {
    "name": "CAMP-2",
    "status": "complete",
    "version": 2,
  },
{
    "name": "CAMP-2",
    "status": "incomplete",
    "version": 1,
  }
]

if the status of latest version is incomplete then both the latest incomplete and complete versions should be returned.

if the status of the latest version is complete then only that version should be returned.

I tried to group by name and status which gives the latest version of incomplete and complete object

db.collection.aggregate({
  "$sort": {
    "version": -1
  }
},
{
  "$group": {
    "_id": {
      "content": "$name",
      "status": "$status"
    },
    "status": {
      "$first": "$$ROOT"
    },
    "content": {
      "$first": "$$ROOT"
    }
  }
},
{
  "$replaceRoot": {
    "newRoot": "$content"
  }
})

The output which I get is

[
{
    "name": "CAMP-1",
    "status": "incomplete",
    "version": 3,
  },
{
    "name": "CAMP-1",
    "status": "complete",
    "version": 2,
  },
  {
    "name": "CAMP-1",
    "status": "complete",
    "version": 1,
  },
{
    "name": "CAMP-2",
    "status": "incomplete",
    "version": 2,
  }
]

But the expected output is

[
{
    "name": "CAMP-1",
    "status": "incomplete",
    "version": 3,
  },
{
    "name": "CAMP-1",
    "status": "complete",
    "version": 2,
  },
{
    "name": "CAMP-2",
    "status": "complete",
    "version": 2,
  }
]

Can anyone please help on how to filter the data based on status?

CodePudding user response：

Query

group by name
find the max-complete version
filter to keep the completed with the same version and the uncompleted with bigger version
unwind and replace root

*For example for 1 name, if you have incomplete version 5 6 7 3 and complete 2 3 4 , you will get 5 6 7 incompletes and 4 complete.
If its not what you want exactly maybe with small changes you can get what you need.

Playmongo (to see what each stage does put the mouse on its end)

aggregate(
[{"$group": {"_id": "$name", "docs": {"$push": "$$ROOT"}}},
 {"$set": 
   {"max-complete": 
     {"$let": 
       {"vars": 
         {"c": 
           {"$filter": 
             {"input": "$docs",
              "cond": {"$eq": ["$$this.status", "complete"]}}}},
        "in": {"$max": "$$c.version"}}}}},
 {"$set": 
   {"docs": 
     {"$filter": 
       {"input": "$docs",
        "cond": 
         {"$or": 
           [{"$and": 
               [{"$gt": ["$$this.version", "$max-complete"]},
                 {"$eq": ["$$this.status", "incomplete"]}]},
             {"$and": 
               [{"$eq": ["$$this.version", "$max-complete"]},
                 {"$eq": ["$$this.status", "complete"]}]}]}}}}},
 {"$unwind": "$docs"},
 {"$replaceRoot": {"newRoot": "$docs"}}])