Why this Mongochart generated aggregation pipeline doesn't work when I implement it

Working with Data
, ,
1

Currently can’t figure out why one pipeline works and the other doesn’t. I got both pipelines from MongoDB charts and they both returned something and displaying charts on MongoDBCharts. However, when I use them in my code, only the first pipeline returns something. I used the same data for all cases. Any suggestions would be greatly appreciated!

The first one doesn’t filter the last 30 days (hard coded by Mongo), both pipelines are copied from Mongodb charts and are not altered.

[
  {
    "$addFields": {
      "trigger_time": {
        "$convert": {
          "input": "$trigger_time",
          "to": "date",
          "onError": null
        }
      }
    }
  },
  {
    "$match": {
      "event_type": {
        "$nin": [
          null,
          "",
          "AC Lost",
          "Device Lost",
          "logged into Database",
          "logged into Nexus Database",
          "logged out of Nexus Database",
          "Low Battery"
        ]
      }
    }
  },
  {
    "$addFields": {
      "trigger_time": {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$type": "$trigger_time"
              },
              "date"
            ]
          },
          "then": "$trigger_time",
          "else": null
        }
      }
    }
  },
  {
    "$addFields": {
      "__alias_0": {
        "hours": {
          "$hour": "$trigger_time"
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "__alias_0": "$__alias_0"
      },
      "__alias_1": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "__alias_0": "$_id.__alias_0",
      "__alias_1": 1
    }
  },
  {
    "$project": {
      "y": "$__alias_1",
      "x": "$__alias_0",
      "_id": 0
    }
  },
  {
    "$sort": {
      "x.hours": 1
    }
  },
  {
    "$limit": 5000
  }
]

The second pipeline

[
  {
    "$addFields": {
      "trigger_time": {
        "$convert": {
          "input": "$trigger_time",
          "to": "date",
          "onError": null
        }
      }
    }
  },
  {
    "$match": {
      "event_type": {
        "$nin": [
          null,
          "",
          "AC Lost",
          "Device Lost",
          "logged into Database",
          "logged into Nexus Database",
          "logged out of Nexus Database",
          "Low Battery"
        ]
      },
      "trigger_time": {
        "$gte": {
          "$date": "2021-03-29T08:35:47.804Z"
        }
      }
    }
  },
  {
    "$addFields": {
      "trigger_time": {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$type": "$trigger_time"
              },
              "date"
            ]
          },
          "then": "$trigger_time",
          "else": null
        }
      }
    }
  },
  {
    "$addFields": {
      "__alias_0": {
        "hours": {
          "$hour": "$trigger_time"
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "__alias_0": "$__alias_0"
      },
      "__alias_1": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "__alias_0": "$_id.__alias_0",
      "__alias_1": 1
    }
  },
  {
    "$project": {
      "y": "$__alias_1",
      "x": "$__alias_0",
      "_id": 0
    }
  },
  {
    "$sort": {
      "x.hours": 1
    }
  },
  {
    "$limit": 5000
  }
]
2

$convert is new in version 4.0
what version are you using in the machine you use when running your code?

3

I have the Mongo community server 4.4 or above. I also use the first pipeline and it works. so I don’t understand why the additional $match

"trigger_time": {
        "$gte": {
          "$date": "2021-03-29T08:35:47.804Z"
        }

would not work

4

@Karen_Cheung
this forum is about basic course about the aggregation framework, so I’m not sure if this is the right place to put this question.
try asking in the Working with data forum or in the Developers Tools forum.
good luck,
Rafael

5

I think the shell syntax is a little bit different.

Rather than { $date : “2021…Z” } you have to use ISODate( “2021…Z” ).

mongo> c.find()
{ "_id" : 1, "date" : ISODate("2021-05-05T13:08:36.217Z") }
mongo> d = { "$date" : "2021-03-29T08:35:47.804Z" }
{ "$date" : "2021-03-29T08:35:47.804Z" }
mongo> q = { "date" : { "$gte" : d } }
{ "date" : { "$gte" : { "$date" : "2021-03-29T08:35:47.804Z" } } }
mongo> c.find( q )
mongo> d = ISODate( "2021-03-29T08:35:47.804Z" )
ISODate("2021-03-29T08:35:47.804Z")
mongo> q = { "date" : { "$gte" : d } }
{ "date" : { "$gte" : ISODate("2021-03-29T08:35:47.804Z") } }
mongo> c.find( q )
{ "_id" : 1, "date" : ISODate("2021-05-05T13:08:36.217Z") }
6

Hi Steevej,

I am using the pipeline in my node.js, so I’ll have to use something else other than ISODate(), but I have tried changing it to ISODate like new Date(“2021-03-29T08:35:47.804Z”) in javaScript. However, it still doesnt work.

7

Thank you… I’ll try posting there as well.

8

I end up solving my own problem. After a bit of digging and asking. Apparently, Node.js does some funny things with Mongodb when it comes to using ‘$date’, that’s why the pipeline didn’t work.

The resolve is to remove ‘$date’ and pass in a date object. For my case,

"trigger_time": {
              "$gte": new Date("2021-03-29T08:35:47.804Z"),
              }

Hope it helps other people

9

For the benefit of all of us, what has changed between

and

and

10

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.