Filter results in mongoDb based on status

Question

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?

I'm confused as to what you are asking. What do you mean by latest version? — Dylan L., Apr 15 '22 at 23:24
So `'version': 2`, is latest in your comment example. correct? and when it is marked `'complete'` you only want `'version': 2` to show. correct? — Dylan L., Apr 15 '22 at 23:38
if its marked incomplete then both the latest versions status = incomplete and status = complete should be shown. — snehalg, Apr 15 '22 at 23:40

Takis · Accepted Answer · 2022-04-16T01:08:46.283

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

Filter results in mongoDb based on status

1 Answers1