I have this data in MongoDB.

{_id: "abc*hello*today*123", "value": 123},
{_id: "abc*hello*today*999", "value": 999},
{_id: "xyz*hello*tomorrow*123", "value": 123}

What I want is to group by the first part before "*{number}". This is what I want to achieve:

{
    _id: "abc*hello*today",
    results: [
         {_id: "abc*hello*today*123", "value":123},
         {_id: "abc*hello*today*999", "value":999}
     ]
},
{
    _id: "xyz*hello*tomorrow",
    results: [
         {_id: "xyz*hello*tomorrow*123", "value":123}
     ]
}

I tried this:

{$group:{
      "_id":"$_id".slice(0, -4)
}}

CodePudding user response：

You can work with regex as below:

.*(?=\*\d )

The above regex will match and retrieve the value before the * and numbers.

Demo @ Regex 101

1. $set - Set firstPart field.

   1.1. $getField - Get the match value from the object 1.1.1.

   1.1.1. $regexFind - Match the _id with regex.

2. $group - Group by firstPart.

3. $unset - Remove the results.firstPart field.

db.collection.aggregate([
  {
    $set: {
      firstPart: {
        $getField: {
          field: "match",
          input: {
            $regexFind: {
              input: "$_id",
              regex: ".*(?=\\*\\d )"
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$firstPart",
      results: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $unset: "results.firstPart"
  }
])

Demo @ Mongo Playground