CODEWITHSUNDEEP
arraysmongodbmongoosemongodb-queryaggregation-framework
bc110402922
bc110402922

Reputation: 457

MongoDB - How to remove part of string in field

I have several documents, I want to remove the alphabet from the data field, based on the 1st _ in the data field, please see the following output. and if the data is empty the get message "Not Auto found".

[
  {
    "data": "ABCS_auto"
  },
  {
    "data": "TRQWEY_car_2"
  },
  {
    "data": "ALPHAB1234RAVO_Garie_2__4_22__11"
  },
  {
    "data": ""
  }
]

Expected Output:

[
  {
    "data": "auto"
  },
  {
    "data": "car_2"
  },
  {
    "data": "Garie_2__4_22__11"
  },
  {
    "data": "No Auto found"
  }
]

Upvotes: 1

Views: 495

Answers (3)

Yong Shun
Yong Shun

Reputation: 51220

  1. $set - Set split_data field:

    1.1. If data not empty string, then split by "_" and return array.

    1.2. Else return empty array.

  2. $project - Decorate output document.

    2.1. If split_data is not empty array, then

    2.1.1. $trim starting "_" from the result 2.1.1.1.

    2.1.1.1. With $reduce, concatenate string with "_" from the result remove the first item from split_data.

    2.2. Else default message.

db.collection.aggregate([
  {
    $set: {
      split_data: {
        $cond: {
          if: {
            $not: {
              $or: [
                {
                  "$eq": [
                    "$data",
                    null
                  ]
                },
                {
                  $eq: [
                    "$data",
                    ""
                  ]
                }
              ]
            }
          },
          then: {
            $split: [
              "$data",
              "_"
            ]
          },
          else: []
        }
      }
    }
  },
  {
    $project: {
      data: {
        "$cond": {
          "if": {
            $not: {
              $eq: [
                "$split_data",
                []
              ]
            }
          },
          "then": {
            "$trim": {
              "input": {
                $reduce: {
                  input: {
                    "$slice": [
                      "$split_data",
                      1,
                      {
                        $size: "$split_data"
                      }
                    ]
                  },
                  initialValue: "",
                  in: {
                    $concat: [
                      "$$value",
                      "_",
                      "$$this"
                    ]
                  }
                }
              },
              "chars": "_"
            }
          },
          "else": "No auto found"
        }
      }
    }
  }
])

Sample Mongo Playground

Another approach I think is much easier is with $regexFind:

  1. $set - Create data_regex field with regex match object by searching "_ .*" (Mean string starting with "_" and any other character(s)).

  2. $project - Decorate output document.

    2.1. Trim the "_" from data_regex.match.

    2.2. Else default message.

db.collection.aggregate([
  {
    $set: {
      data_regex: {
        $regexFind: {
          input: "$data",
          regex: "_.*"
        }
      }
    }
  },
  {
    $project: {
      data: {
        "$cond": {
          "if": {
            $not: {
              $eq: [
                "$data_regex",
                null
              ]
            }
          },
          "then": {
            "$trim": {
              "input": "$data_regex.match",
              "chars": "_"
            }
          },
          "else": "No auto found"
        }
      }
    }
  }
])

Sample Mongo Playground ($regexFind)

Upvotes: 1

Anveeg Sinha
Anveeg Sinha

Reputation: 505

Use $ifNull for "No Auto Found" and $split for "_"

  db.collection.aggregate([
  {
    $project: {
      data: {
        $ifNull: [
          {
            $arrayElemAt: [
              {
                $split: [
                  "$data",
                  "_"
                ]
              },
              1
            ]
          },
          "No Auto found"
        ]
      }
    }
  }
])

here is a working mongoplayground link

Upvotes: 0

Zahid Cakici
Zahid Cakici

Reputation: 259

Try this one:

db.collection.aggregate([{$project: {data: {$arrayElemAt:[{ $split:["$data","_"]},1]}}}])

Upvotes: 0

Related Questions