How to use substring for comparison in mongo db-CodePudding

Below is my aggregation

db.customers.aggregate([{
    $match: {
        "CDF.UTILITYTYPE.D1.G1" : "12387835"
    }   
},  {
        $project: {
         _id:0, 
         "CDF.UTILITYTYPE.D1.G22.NAME":1, 
         "CDF.UTILITYTYPE.D1.G1":1,
         "CDF.UTILITYTYPE.D5.EVENT": {
            $filter: {
               input: "$CDF.UTILITYTYPE.D5.EVENT",
               as: "item",
               cond: { $eq: [ "$$item.TIME", "12-04-2018 15:46:02" ] }
            }
         }
      }
    }
]).pretty();

i am comparing TIME field here i actually want to compare "06-2022" as a substring instead of "12-04-2018 15:46:02" this whole date and time format

CodePudding user response：

You never store date/time values as string, it's a design flaw. Store always proper Date objects.

Once you corrected the data type, e.g. with

{
   $set: {
      TIME: {
         $dateFromString: {
            dateString: "$TIME",
            format: "%d-%m-%Y %H:%M:%S"
         }
      }
   }
}

you can filter by using for example

cond: {
   $eq: [
      { $dateTrunc: { date: "$TIME" unit: "month" } },
      ISODate("2022-06-01")
   ]
}

or supposingly

cond: {
   $eq: [
      { $dateTrunc: { date: "$TIME" unit: "month" } },
      { $dateTrunc: { date: "$$NOW" unit: "month" } }
   ]
}

CodePudding user response：

db.customers.aggregate([{
    $match: {
        "CDF.UTILITYTYPE.D1.G1" : "12387835"
    }   
},  {
        $project: {
         _id:0, 
         "CDF.UTILITYTYPE.D1.G22.NAME":1, 
         "CDF.UTILITYTYPE.D1.G1":1,
         "CDF.UTILITYTYPE.D5.EVENT": {
            $filter: {
               input: "$CDF.UTILITYTYPE.D5.EVENT",
               as: "item",
               cond: { $regexMatch: { input:"$$item.TIME", regex: "05-2022"}}
            }
         }
      }
    }
]).pretty();