$matching with field added with $dateToString doesn't work

Question

In my MongoDB collection I have documents that contain a nested string field, containing a month and year, e.g. '04/2021'. Sample document:

{
    "_id": {
        "$oid": "608ba45cec43c5b24cda034b"
    },
    "status": "pass",
    "stage": 5,
    "priority": 0,
    "payload": {
        "company_id": "8800",
        "company_name": "<MY COMPANY>",
        "target_period": "04/2021"
    },
    "retry_count": 0,
    "build_number": "101",
    "job_name": "P123",
    "createdAt": {
        "$date": "2021-04-30T06:31:56.000Z"
    },
    "updatedAt": {
        "$date": "2021-05-10T03:55:44.686Z"
    }
}

I am trying to write an aggregation pipeline that will dynamically return documents where said field points to the past month. For example, ran this month (May 2021) I would get documents labeled with '04/2021'. From this post I found the oneliner for getting the comparison string: new Date(new Date().getFullYear(), new Date().getMonth(), 1). (I understand that by the virtue of getMonth returning a zero-based index of month, getting the previous month works by accident and has to be solved somehow.)

This pipeline does not work:

[
    {
        $addFields: {
            previous_month: {
                $dateToString: {
                    'date': new Date(new Date().getFullYear(), new Date().getMonth(), 1),
                    'format': '%m/%G'
                }
            }
        }
    },
    {
        $match: {
            "payload.target_period": "$previous_month"
        }
    }
]

With MongoDB Compass I can see that the field previous_month is populated just fine by the $addFields stage (above sample document gets value 04/2021), but the $match stage returns 0 documents. I'm running MongoDB version 4.2.12.

hhharsha36 · Answer 1 · 2021-05-12T05:04:23.247

2

You should use $expr operator while trying to self reference another ket in a document inside $match stage.

[
  {
    $addFields: {
      previous_month: {
        $dateToString: {
          'date': new Date(new Date().getFullYear(), new Date().getMonth(), 1),
          'format': '%m/%G'
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $eq: [ "$payload.target_period",  "$previous_month" ],
      },
    }
  }
]

edited May 12 '21 at 05:04

answered May 10 '21 at 06:55

hhharsha36

3,089
2
12
12

This works, without the extra braces around `$eq`. – Otto Ahonen May 12 '21 at 05:01
Sorry. Thanks for pointing it out. Updated my answer – hhharsha36 May 12 '21 at 05:04

score 0 · Answer 2 · answered May 10 '21 at 06:56

Instead of doing whole process in query, I think you can prepare input date in your client language, (js, nodejs) easily,

have prepared a function zeroFill it will return number with concat 0 if its less than 10,
get previous month date and pick previous month
concat both month and year

function zeroFill(i) { return (i < 10 ? '0' : '') + i; }

var date = new Date();
date.setMonth(date.getMonth() - 1);
let searchDate = zeroFill(date.getMonth() + 1) + "/" + date.getFullYear();

console.log(searchDate); // mm/yyyy

Your query would be just:

[{ $match: { "payload.target_period": searchDate } }]

Playground

score 0 · Answer 3 · answered May 10 '21 at 08:30

0

I would suggest moment.js library, it is much simpler to use:

{ $match: { "payload.target_period": moment().startOf("months").subtract(1, "months").format("MM/YYYY") } }

answered May 10 '21 at 08:30

Wernfried Domscheit

54,457
9
76
110

$matching with field added with $dateToString doesn't work

3 Answers3