how to split the data in mongo db

Question

I have several documents, I want to remove the alphabets from 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"
  },
]

user_8017690 · Answer

$set - Set split_data field:

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

1.2. Else return empty array.
$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:

$set - Create data_regex field with regex match object by searching "_ .*" (Mean string starting with "_" and any other character(s)).
$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)

user_17498940 · Answer

Try this one:

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

user_17498746 · Answer

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

how to split the data in mongo db

3 Answers