MongoDB aggregation pipeline - matching $regex pattern of _id and setting as value of another field

Question

Prior to the projection stage of my pipeline, my document ids are of the type:

"manchester_10:2016-09-28 09"
"burnley_10:2016-09-28 09"

In the projection stage, I'm trying to project new fields 'location' and 'date_hour' but having problems trying to regex match from the _id value and effectively splitting them by the : into two seperate key-value pairs such as this:

location: "manchester_10"
date_hour: "2016-09-28 09"

So far I've tried a number of things, with my $project query as this:

'location': { '$_id': { $regex: /.+?(?=:)/}},

The regex pattern is correct to match anything upto the colon, but I'm stumped as to how exactly to write the query...any ideas?

Answer 1

Try This: Assuming it is for single line string

/([^:]*):(\d{4}-\d{2}-\d{2} \d{2})/g

Here group 1 is location and group 2 is date