Filter out Subdocuments where array is empty in mongo db aggregation-CodePudding

    Input 
    {
        "_id" : ObjectId("62b2beb8bfe272962f274942"),
        "CDF" : {
            "UTILITYTYPE" : {
                "D1" : {
                    "G1" : "12387835",
                    "G2" : "02-06-2022 13:03:23",
                    "G22" : {
                        "NAME" : "LARSEN AND TOUBRO LIMITED"
                    }
                },
                "D5" : {
                    "EVENT" : [
                        {
                            "CODE" : "14",
                            "TIME" : "23-05-2022 09:23:39",
                            "STATUS" : "0",
                            "SNAPSHOT" : [
                                {
                                    "PARAMCODE" : "P1-2-1-1-0",
                                    "VALUE" : "218.39",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P1-2-2-1-0",
                                    "VALUE" : "243.67",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P1-2-3-1-0",
                                    "VALUE" : "241.37",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P2-1-1-1-0",
                                    "VALUE" : "0.456",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P2-1-2-1-0",
                                    "VALUE" : "0",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P2-1-3-1-0",
                                    "VALUE" : "0",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P4-1-1-0-0",
                                    "VALUE" : "-0.99",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P4-2-1-0-0",
                                    "VALUE" : "0",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P4-3-1-0-0",
                                    "VALUE" : "0",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P7-1-5-2-0",
                                    "VALUE" : "2383.03",
                                    "UNIT" : "k"
                                }
                            ]
                        },
                        {
                            "CODE" : "14",
                            "TIME" : "23-05-2022 10:40:49",
                            "STATUS" : "1",
                            "SNAPSHOT" : [
                                {
                                    "PARAMCODE" : "P1-2-1-1-0",
                                    "VALUE" : "227.58",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P1-2-2-1-0",
                                    "VALUE" : "236.78",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P1-2-3-1-0",
                                    "VALUE" : "236.78",
                                    "UNIT" : "V"
                                },
                                {
                                    "PARAMCODE" : "P2-1-1-1-0",
                                    "VALUE" : "0",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P2-1-2-1-0",
                                    "VALUE" : "0",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P2-1-3-1-0",
                                    "VALUE" : "0",
                                    "UNIT" : "A"
                                },
                                {
                                    "PARAMCODE" : "P4-1-1-0-0",
                                    "VALUE" : "0",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P4-2-1-0-0",
                                    "VALUE" : "0",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P4-3-1-0-0",
                                    "VALUE" : "0",
                                    "UNIT" : "NA"
                                },
                                {
                                    "PARAMCODE" : "P7-1-5-2-0",
                                    "VALUE" : "2383.16",
                                    "UNIT" : "k"
                                }
                            ]
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "22-05-2022 11:44:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "22-05-2022 11:49:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "22-05-2022 20:57:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "22-05-2022 21:01:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "23-05-2022 13:49:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "23-05-2022 13:52:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "25-05-2022 06:26:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "25-05-2022 06:31:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "26-05-2022 06:51:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "26-05-2022 06:56:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "28-05-2022 07:02:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "28-05-2022 07:28:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "28-05-2022 18:43:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "28-05-2022 18:54:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "29-05-2022 13:26:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "29-05-2022 14:41:00",
                            "STATUS" : "1"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "31-05-2022 10:13:00",
                            "STATUS" : "0"
                        },
                        {
                            "CODE" : "13",
                            "TIME" : "31-05-2022 10:18:00",
                            "STATUS" : "1"
                        }
                    ]
                }
            }
        }
    }

Output :

{
    "_id" : ObjectId("62b2beb8bfe272962f274942"),
    "CDF" : {
        "UTILITYTYPE" : {
            "D1" : {
                "G1" : "12387835",
                "G2" : "02-06-2022 13:03:23",
                "G22" : {
                    "NAME" : "LARSEN AND TOUBRO LIMITED"
                }
            },
            "D5" : {
                "EVENT" : [
                    {
                        "CODE" : "14",
                        "TIME" : "23-05-2022 09:23:39",
                        "STATUS" : "0",
                        "SNAPSHOT" : [
                            {
                                "PARAMCODE" : "P1-2-1-1-0",
                                "VALUE" : "218.39",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P1-2-2-1-0",
                                "VALUE" : "243.67",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P1-2-3-1-0",
                                "VALUE" : "241.37",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P2-1-1-1-0",
                                "VALUE" : "0.456",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P2-1-2-1-0",
                                "VALUE" : "0",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P2-1-3-1-0",
                                "VALUE" : "0",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P4-1-1-0-0",
                                "VALUE" : "-0.99",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P4-2-1-0-0",
                                "VALUE" : "0",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P4-3-1-0-0",
                                "VALUE" : "0",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P7-1-5-2-0",
                                "VALUE" : "2383.03",
                                "UNIT" : "k"
                            }
                        ]
                    },
                    {
                        "CODE" : "14",
                        "TIME" : "23-05-2022 10:40:49",
                        "STATUS" : "1",
                        "SNAPSHOT" : [
                            {
                                "PARAMCODE" : "P1-2-1-1-0",
                                "VALUE" : "227.58",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P1-2-2-1-0",
                                "VALUE" : "236.78",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P1-2-3-1-0",
                                "VALUE" : "236.78",
                                "UNIT" : "V"
                            },
                            {
                                "PARAMCODE" : "P2-1-1-1-0",
                                "VALUE" : "0",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P2-1-2-1-0",
                                "VALUE" : "0",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P2-1-3-1-0",
                                "VALUE" : "0",
                                "UNIT" : "A"
                            },
                            {
                                "PARAMCODE" : "P4-1-1-0-0",
                                "VALUE" : "0",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P4-2-1-0-0",
                                "VALUE" : "0",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P4-3-1-0-0",
                                "VALUE" : "0",
                                "UNIT" : "NA"
                            },
                            {
                                "PARAMCODE" : "P7-1-5-2-0",
                                "VALUE" : "2383.16",
                                "UNIT" : "k"
                            }
                        ]
                    }
  
                ]
            }
        }
    }
}

Above document contains D5.EVENT.SNAPSHOT in some events how can i
Remove Subdocuments where "SNAPSHOT" Array is EMPTY

CodePudding user response：

$set - Set CDF.UTILITYTYPE.D5.EVENT field

1.1. $filter - Filter the document(s) in CDF.UTILITYTYPE.D5.EVENT array with condition:

1.2. $gt - Value in 1.3 is greater than 0.

1.3. $size - Get the size of the array from 1.4.

1.4. $ifNull - Set the default value as [] empty array when the current document's SNAPSHOT field is null / not existed. Otherwise, remained SNAPSHOT array.

db.collection.aggregate([
  {
    $set: {
      "CDF.UTILITYTYPE.D5.EVENT": {
        $filter: {
          input: "$CDF.UTILITYTYPE.D5.EVENT",
          cond: {
            $gt: [
              {
                $size: {
                  $ifNull: [
                    "$$this.SNAPSHOT",
                    []
                  ]
                }
              },
              0
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground