CODEWITHSUNDEEP
mongodbaggregation-framework
user3381520
user3381520

Reputation: 87

Nested mongo $add and $multiply resulting in can't convert from BSON type NULL to Date error

As part of the project stage of an aggregation, I'm trying to add a timezone offset to a date so I can then group by the modified date.

The docs look like:

{
  "_id": ObjectId("59ca593a9a94192d4f9d4452"),
  "updatedAt": new Date(1506433338535),
  "createdAt": new Date(1506433338535),
  "pubTimezone": "Europe/London",
  "text": "Buhzado fi apewem tuglikler mo lokwa pu luv aluar aghesgu li kelesipo bi.",
  "pubDate": new Date(1506433338052),
  "pubTimezoneOffsetHours": 0
}

Relevant code:

[
  { 
    $project: {
      _id: '$_id',       
      text: '$text',
      pubTimezone: '$pubTimezone',
      pubTimezoneOffsetHours: '$pubTimezoneOffsetHours',
      pubDate: {
        $add: [ '$pubDate', {
          $multiply: [ '$pubTimezoneOffsetHours', 60, 60, 1000 ]
        }]
      }
    }
  },
  {
    $group: {
      _id: {
        year: { $year: '$pubDate' },
        month: { $month: '$pubDate' },
        day: { $dayOfMonth: '$pubDate' }
      },
      count: { $sum: 1 },
      items: {
        $push: {
          _id: '$_id',
          updatedAt: '$updatedAt',
          createdAt: '$createdAt',
          text: '$text',
          pubTimezone: '$pubTimezone',
          pubTimezoneOffsetHours: '$pubTimezoneOffsetHours',
          pubDate: {
            $subtract: [ '$pubDate', {
              $multiply: [ '$pubTimezoneOffsetHours', 60, 60, 1000 ]
          }]
        }
      }
    }
  }
]

This results in Error: can't convert from BSON type NULL to Date

I have searched through the data for { pubTimezoneOffsetHours: null } and updated all entries that had null values to have an integer value.

When I remove '$pubTimezoneOffsetHours' from the $multiply array, the error disappears.

Where am I going wrong?

Upvotes: 0

Views: 289

Answers (1)

Buzz Moschetti
Buzz Moschetti

Reputation: 7578

I'm not sure about your $project/$push example because that's a syntax error but focusing on your goal, you probably have a null somewhere in pubTimezoneOffsetHours (still) and that's making the adjusted date null because a null in the $multiply array will force the whole thing to null. Try this on for size:

db.foo.aggregate([
  {$addFields: {adjDate: {$add: [
    "$pubDate",
    {$multiply: [ {$ifNull: ['$pubTimezoneOffsetHours',0]}, 60, 60, 1000 ]} 
    ] } 
}}
                ]);

You can then {$group: {_id:"$adjDate"}}any way you like.

Upvotes: 1

Related Questions