multiple nested filter in Mongoose

Question

My json will be like this :

 {
      "proj_id" : objectId1
      "author": {},
      "message": "This is post1",
      "comments": [
        {
          "insId" : objectId1
          "message": "Hii",
        "location": [
                     {"select":"Y",id:1},
             {"select":"N",,id:2}
            ]

    },
    {
      "insId" : objectId2
        "message": "Hii2",
        "location": [
                 {"select":"Y",id:1},
         {"select":"N",,id:2}
        ]
    },

  ]
},
{
  "proj_id" : objectId2
  "author": {},
  "message": "This is post2",
  "comments": [
    {
      "insId" : objectId1
      "message": "welcome2",
      location: [
                 {"select":"Y",id:1},
         {"select":"N",,id:2}
        ]

    },

    {
    "insId" : objectId2
        "message": "welcome4",
        "location": [
                 {"select":"Y",id:1},
         {"select":"N",,id:2}
        ]

    }
  ]
}

Expected Output :

{
  "proj_id" : objectId1
  "author": {},
  "message": "This is post1",
  "comments": [
    {
      "insId" : objectId1
      "message": "Hii",
    "location": [
                 {"select":"Y",id:1},
        ]

    }
  ]
}

Have to filter based upon inspection Id and inside inspection dat I have to list only the location which having status "Y"

I tried the following code by filtering with inspection Id

mongo.inspection.aggregate([
    {$match: {"comments.insId": ObjectId(req.body.insId),"projectID":ObjectId(req.body.proj_id) }},
    {$addFields : {"comments":{$filter:{ // We override the existing field!
    input: "$comments",
    as: "comments",
    cond: {$eq: ["$$comments.insId", ObjectId(req.body.insId)]}

    }}}}

],function(err,response){
console.log(response);
}

Output :

{
  "proj_id" : objectId1
  "author": {},
  "message": "This is post1",
  "comments": [
    {
      "insId" : objectId1
      "message": "Hii",
    "location": [
                 {"select":"Y",id:1},
         {"select":"N",id:1}, // But it should not be list
        ]

    }
  ]
}

So I have to filter the object based upon comments.insId and from that single object I have to remove the object inside the location based upon status . How do i get this result in mongoose

Answer 1

Try the following query. Here I am unwinding the objects after filtering out. Then filtering the objects again and grouping them to get in original format.

mongo.inspection.aggregate([
  {$match: {"proj_id": "objectId1", "comments.insId": " objectId1", "comments.location.select": "Y"}},
  {$unwind:{path: "$comments", preserveNullAndEmptyArrays: false}},
  {$match: {"comments.insId": " objectId1", "comments.location.select": "Y"}},
  {$unwind:{path: "$comments.location", preserveNullAndEmptyArrays: false}},
  {$match: {"comments.location.select": "Y"}},

  {$group: {
      _id: {proj_id : "$proj_id", ins_id: "$comments.insId"}, 
      author : {$first: "$author"}, 
      message : {$first: "$message"},
      comm_message:{$first: "$comments.message"}, 
      location:{$push: "$comments.location"} 
  }},
  {$group: {
      _id: "$_id.proj_id",
      author : {$first: "$author"},
      message : {$first: "$message"}, 
      comments:{$push:{message: "$comm_message", location: "$location", insId: "$_id.ins_id"}}

  }}

])

Use this way if you are having a lot of entries in the comments and location arrays. otherwise use some aggregate array operators like filters and maps.

Answer 2

Use $map with $filter.

Something like

{
  "$addFields":{
    "comments":{
      "$map":{
        "input":{
          "$filter":{
            "input":"$comments","as":"commentsf","cond":{"$eq":["$$commentsf.insId", ObjectId(req.body.insId)]}
          }
        },
        "as":"commentsm",
        "in":{
          "insId":"$$commentsm.insId",
          "message":"$$commentsm.message",
          "location":{"$filter":{"input":"$$commentsm.location","as":"location","cond":{"$eq":["$$location.select","Y"]}}}
        }
      }
    }
  }
}