Question 116072 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to group by a part of string?

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)
  }
}
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Alternative solution: If your _id has always the same format (field1*field2*field3*numbers), you can use $split + $slice + $reduce operators

    Explanation:

    $split('field1*field2*field3*numbers', '*')                   -> ['field1', 'field2', 'field3','number']
$slice(['item1', 'item2', 'item3'], 2)                        -> ['item1', 'item2']
$reduce(['field1', 'field2', 'field3'], 'Concatenate with *') -> 'field1*field2*field3'

    db.collection.aggregate([
  {
    $group: {
      _id: {
        $slice: [
          { $split: [ "$_id", "*"] },
          3
        ]
      },
      results: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $project: {
      _id: {
        $reduce: {
          input: "$_id",
          initialValue: "",
          in: { $concat: [ "$$value", "*", "$$this" ] }
        }
      },
      results: 1
    }
  }
])

    MongoPlayground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search