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

Working with Data Developer Tools
,
1

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.

2

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") }

3

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

4

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.

5

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

6

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 ] } } );
7

not this one, i wanted field query example