I have the following object containing one string field

{
  _id: '>Books>IT>C#>Cloud computing>'
}

Using aggregation, I need to return the following object. (_id)

{
  _id: 'Cloud Computing',
  Path: '>Books>IT>C#>'
}

Please note, _id should not have any > characters and Path should start and end with >. Also when using $concat or $reduce to concatenate string values of the $split, I need to append > to the end of each element.

I have tried the following, but I have the following errors:

1. Path field starts with two >, instead of one

2. $reduce does not exclude the last element of the split

3. Path field does not end with > character

   db.collection.aggregate([
   {
       '$project': {
           'Split': {
               '$filter': {
                   'input': {
                       '$split': [
                           '$_id', '>'
                       ]
                   }, 
                   'as': 'categories', 
                   'cond': {
                       '$ne': [
                           '$$categories', ''
                       ]
                   }
               }
           }
       }
   }, {
       '$project': {
           '_id': {
               '$last': '$Split'
           }, 
           'Path': {
               '$reduce': {
                   'input': '$Split', 
                   'initialValue': '>', 
                   'in': {
                       '$concat': [
                           '$$value', {
                               '$cond': [
                                   {
                                       '$eq': [
                                           '$$value', ''
                                       ]
                                   }, '', '>'
                               ]
                           }, '$$this'
                       ]
                   }
               }
           }
       }
   }
   ])
   

Resulting in:

{
  _id: 'Cloud Computing',
  Path: '>>Books>IT>C#>Cloud Computing'
}

CodePudding user response：

Few small adjustments:

1. $splice the array at the second step.
2. No need for $cond, just concat the >

db.collection.aggregate([
  {
    $project: {
      Split: {
        $filter: {
          input: {"$split": ["$_id", ">"]},
          cond: {$ne: [ "$$this",  ""]}
        }
      },
      _id: 0
    }
  },
  {
    $project: {
      _id: {$last: "$Split"},
      Path: {
        $reduce: {
          input: {
            $slice: ["$Split", 0, {$subtract: [{$size: "$Split"}, 1]}]
          },
          initialValue: ">",
          in: {$concat: ["$$value", "$$this", ">"]}
        }
      }
    }
  }
])

See how it works on the playground example