MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

How to convert string date to new Date() using shell or Compass?

I have JSON in following format:

{
    "_id": {
        "$oid": "5e5ddf423874fe28e899d912"
    },
    "nodeMapping": [],
    "Parentnode": [
        {
            "LEVEL1": "Civil and Structural"
        },
        {
            "LEVEL2": "Cluster 2"
        },
        {
            "LEVEL3": "Parking Area"
        }
    ],
    "nodeHierarchy": [
        {
            "LEVEL1": "Civil and Structural"
        },
        {
            "LEVEL2": "Cluster 2"
        },
        {
            "LEVEL3": "Parking Area"
        },
        {
            "LEVEL4": "Compaction"
        }
    ],
    "leafNode": "Compaction",
    "weightage": "40",
    "dateFrom": "10 Apr 2020",
    "dateTo": "10 Jan 2021",
    "scope": "2600",
    "IsLeafNode": true,
    "uniqueId": "26ed3117-41d5-4252-a772-832fa8454587",
    "isInternalNode": false,
    "wsName": "PROJECT2",
    "wsId": "PROJECT2"
}

Now how can I sort or filter on date range using shell or Compass?

Basically i want function which can convert it into real date.

Pls send query. It will be helpful.

An example document with input string date field can be converted to a date object with an aggregation operator $dateFromString. But, this operator takes a month value in numeric format, not as alphabet characters like “Mar”. So, get the alpha month converted to a number to convert as a date object.

{ dt: "10 Apr 2020" }

MONTHS_ARR = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ]

db.coll.aggregate( [
  { 
      $addFields: { 
          dt: { 
              $dateFromString: {
                  dateString: { 
                      $concat: [
                          { $substrCP: [ "$dt", 0, 2 ] }, " ",
                          { $toString: { $add: [ { $indexOfArray: [ MONTHS_ARR, { $substrCP: [ "$dt", 3, 3 ] } ] }, 1 ] } }, " ",
                          { $substrCP: [ "$dt", 7, 4 ] }
                      ] 
                  },
                 format: "%d %m %Y"
              }
          }
      } 
  },
] )

The converted string date to Date object. This can be used for your sort or comparison operations.

{ "dt" : ISODate("2020-04-10T00:00:00Z") }

agrregate is the only way to do it or some other functions are there which could be applied with out agrregates pipelne?

If you want to use the converted date in an aggregation query, that is the way; for comparison, sorting and date arithmetic this is the only way, I think.

It is possible to use a date field in converted form in a find query filter, using the $expr operator. $expr allows using aggregation operators (e.g., $dateFromString) in a find query filter only.; this means using with comparison operators and not for sorts on the date field.

can i get one eample, i new and there is time limit, so i cannot read.

The same document as in the previous exmple and the MONTHS_ARR variable are used to compare a supplied date dateToCompare with the document’s string date field dt. Note the usage of the $expr operator.

dateToCompare = ISODate("2021-03-11T00:00:00Z");

queryCond = {
    $lt: [
        {
           $dateFromString: {
                dateString: { 
                    $concat: [
                          { $substrCP: [ "$dt", 0, 2 ] }, " ",
                          { $toString: { $add: [ { $indexOfArray: [ MONTHS_ARR, { $substrCP: [ "$dt", 3, 3 ] } ] }, 1 ] } }, " ",
                          { $substrCP: [ "$dt", 7, 4 ] }
                    ] 
                },
               format: "%d %m %Y"
            }
        },
        dateToCompare
    ]
};

db.coll.find( { $expr: { $eq: [ queryCond, true ] } } );

not this one, i wanted field query example