Question 117424 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to use substring for comparison in mongo db

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    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();

  2. 0

    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" } }
   ]
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search