Regex in Mongo query with MBS

Questions
1

Hi all

I'm trying to create an aggregate query using the MBS plugin Mongo functions. It all works fine so long as my 'match' doesn't try to use a regex, but unfortunately for what I'm trying to do I need to use a regex.

In Mongo the aggregate pipeline would be

[
  {
    "$match": {
      "invoice.InvoiceDate": {
        "$regex": /^2025-03-07/
      }
    }
  },
  {
    "$group": {
      "_id": "$invoice.InvoiceDate",
      "count": {
        "$count": {}
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  }
]

However when I try and run this with MBS ( "MongoDB.AggregateCollection"; $mongo ; 0 ; $query ; "" ) then the plugin returns an error because the above isn't (technically) valid JSON. If I wrap the regex in quotes then MBS accepts it, but Mongo doesn't return the expected results. If I escape the backslashes MBS complains about the escape character not being in a string.

Anyone got any ideas on how I can get this to work - I only need to run it about 15,000 times so having to do it manually isn't a great option :wink:

Thanks
Steve

2

Have you tried to add quotes, but remove the slashes? The slashes are basically syntax to say "that's a regex", but here the context says that already.

3

Can you try something like this?

[
  {
    "$match": {
      "invoice.InvoiceDate": {
        "$regex": "^2025-03-07"
      }
    }
  },
  {
    "$group": {
      "_id": "$invoice.InvoiceDate",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$sort": {
      "_id": 1
    }
  }
]