Query with Date operation

Working with Data
,
1

Hi There,

I want to create a pipeline aggregate by comparing current date with some date.
But my need is to operate addDate (day) to my date in datebase.

Here y aggregate pipeline

db.customer.aggregate()
    .match
    (
        {
            $and:
            [
                {"audiences":{$size:1}},
                { $$NOW: 
                    {
                        lt:
                        [
                            {
                                $addDate: 
                                { 
                                    startDate: "audiences.startDate" ,
                                    unit: "day",
                                    amount: "audiences.audience.membershipDuration"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    )
    .limit(100)

And here the data stored in mongodb

{
   "_id":{
      "$oid":"62e932661c0f2e018fe6be44"
   },
   "buCode":"LMIT",
   "clientNumber":"222222",
   "audiences":[
      {
         "audience":{
            "audienceId":"45379",
            "name":"OFFER_Marketing_Cible_Parcours_Confort_SCORE_1",
            "createdDate":null,
            "updatedDate":null,
            "maximumNumberOfUseDuringMembership":1,
            "membershipDuration":1,
            "category":"LOYALTY",
            "description":"test longeurs chaines hsdfsqdfsqfdhsqkdjf sdfsjdfsqdfkl sdfSDFSDF SDFSQDFSQ sdfsqdfsq",
            "buCode":"LMIT"
         },
         "startDate":{
            "$date":"2022-07-28T11:59:33.000Z"
         },
         "remainingNumberOfUse":1,
         "lastEntryDate":{
            "$date":"2022-08-02T14:19:19.106Z"
         }
      }
   ]
}

Thanks
Mo

2

Hi Mo,

I’m assuming you’re having an error, but you don’t state what that was. Based on playing around with the code you posted above I was getting the following error:

MongoServerError: PlanExecutor error during aggregation :: caused by :: $dateAdd requires startDate to be convertible to a date

If that’s what you’re running into, I was able to resolve that by $unwinding the audiences array. It seem like the array is causing issues in the $dateAdd call.

If you’re having other issues let us know what they are so we can more quickly help you out.

3

Hi Doug_Duncan,

In my mongodb collection, startDate is a date.
Here’s the message error in my case:

{
	"message" : "unknown top level operator: $$NOW. If you have a field name that starts with a '$' symbol, consider using $getField or $setField.",
	"ok" : 0,
	"code" : 2,
	"codeName" : "BadValue",
	"$clusterTime" : {
		"clusterTime" : Timestamp(1660636531, 8),
		"signature" : {
			"hash" : "TglED2lvQswahQMyU5G7aIEsQBA=",
			"keyId" : NumberLong("7067609856571604994")
		}
	},
	"operationTime" : Timestamp(1660636531, 8)
}
4

If you look at the documentation of $addDate you will see that both startDate and amount requires an <expression>. As such to access your field values you need to use the dollar sign. So it should be

startDate: "$audiences.startDate"

rather than

startDate: "audiences.startDate"

The syntax for $match is

fieldName : ValueOrComparator

The $$NOW variable is not a field name. I am not too sure about this one but I think you need to be inside $expr to be able to use it.

5

You could also use $addFields and set a new field to the value of $$NOW:

[
    {
        "$addFields": {"currentTime": "$$NOW"}
    }
]

Which one is the best depends on if the value of $$NOW is needed only once in the pipeline.

1 Like
6

Hi Doug,

Thanks for the reply.
but i don’t know the correct syntax to use in my query below

[
    {
        "$addFields": {"currentTime": "$$NOW"}
    }
]

Here’s my query

db.customer.aggregate()
    .match
    (
        {
            $and:
            [
                {"audiences":{$size:1}},
                { $$NOW: 
                    {
                        lt:
                        [
                            {
                                $addDate: 
                                { 
                                    startDate: "audiences.startDate" ,
                                    unit: "day",
                                    amount: "audiences.audience.membershipDuration"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    )
    .limit(100)

I need help
Thanks

7

I was not able to get $dateAdd to work in a $match stage, but I could have been doing something wrong.

Something like this should work, or at least be a starting place for you:

db.customer.aggregate(
  [
    {
      $unwind: "$audiences"
    },
    {
      $addFields: {
        qualify: {
          $cond: {
            if: {
              $gte: [
                {
                  $dateAdd: {
                    startDate: "$audiences.startDate",
                    unit: "day",
                    amount: "$audiences.audience.membershipDuration"
                  }
                },
                "$$NOW"
              ]
            },
            then: true,
            else: false
          }
        }
      }
    },
    {
      $match: {
        qualify: true
      }
    }
  ]
)

Note that you will want to put a $match to filter out any documents that you don’t want to work with to lessen the load on the server.

1 Like
8

Thank you to much @Doug_Duncan