[input_mongodb] Bloblang query is not working with ISODate field

[lytuan99]

Input_mongodb can not query with ISODate field of MongoDB connection.

I've tried many ways in configuring input below, all of those command can not get data.

    query: |
      root.createdAt = {"$lte": now()}
## in history queries mongodb: "$lte": "2024-09-22T08:58:00.139917+07:00"
##
    query: |
      root.createdAt = {"$lte": timestamp_unix() - 90 * 86400}
## in history queries mongodb: "$lte": new NumberInt("1597405526")
##
    query: |
      root.createdAt = {"$gte": (timestamp_unix() - 90 * 86400).ts_format()}
## in history queries mongodb: "$gte": "2024-06-24T08:58:55+07:00"
## .....

In my IDE, I execute the query below, and it works.

db.orders.find({
  createdAt: {
    $gte: new Date("2024-02-02"),
  },
});

Did I miss something or the RedPanda-connect should add a new feature for ISODate object?

[mihaitodor]

Hey @lytuan99 👋 Try root.created_at = {"$gte": "2024-02-02".ts_strptime("%Y-%m-%d")} for example. Also, this should work: root.created_at = {"$gte": now().ts_parse("2006-01-02T15:04:05Z07:00")}. The 2006-01-02T15:04:05Z07:00 format is the reference format used for RFC3339 in Go as documented here: https://pkg.go.dev/time#pkg-constants.

PS: Converting this to a discussion as mentioned in #2026.