How to filter string arrays within documents in mongodb?

user_7478637 0
Viewed 308

I have multiple mongodb documents which looks like this

    {
    "_id": "001",
    "car_description": "Audi",
    "sales": [
        "India/Mumbai",
        "India/Delhi",
        "India/Chennai",
        "India/Kolkata",
        "US/NYC",
        "US/SF"]
   },
   {
    "_id": "002",
    "car_description": "BMW",
    "sales": [
        "India/Mumbai",
        "India/Delhi",
        "India/Chennai",
        "India/Kolkata",
        "US/NYC",
        "US/SF"]
}

I am trying to get the car_description and sales which happened in India. Final output should be something like this.

{
  {
   "car_description": "Audi",
   "sales": [
        "India/Mumbai",
        "India/Delhi",
        "India/Chennai",
        "India/Kolkata"]
  },
  {
    "car_description": "BMW",
    "sales": [
        "India/Mumbai",
        "India/Delhi",
        "India/Chennai",
        "India/Kolkata"]
  }
}

EDIT : I tried using this but this does not filter out the sales. Instead it gives an error saying "Unrecognized expression '$regexMatch"

db.collection.aggregate([
  {
    "$project": {
      "car_description": 1,
      "sales": 1,
      "sales": {
        "$filter": {
          "input": "$sales",
          "as": "sale",
          "cond": {
            $regexMatch: {
              input: "$$sale",
              regex: "India",
              options: "i"
            }
          }
        }
      }
    }
  }
])
mongodb-query mongodb
1 Answers

Check out this snippet to see if it fulfills your need:

db.sales.find({
  "sales": {
    $elemMatch: {
      $regex: "^India\/",
      $options: "i"
    }
  }
},
{
  "car_description": 1,
  "sales": 1
})

https://mongoplayground.net/p/61sqeMXU_yg

EDIT: If you'd like to filter out all regions which are not matched by the regex expression you could try the following aggregate query:

MongoDB >= 4.1.11

db.sales.aggregate([
  {
    $match: {
      "sales": {
        $elemMatch: {
          $regex: "^India\/",
          $options: "i"
        }
      }
    }
  },
  {
    $set: {
      sales: {
        $filter: {
          input: "$sales",
          as: "sale",
          cond: {
            $regexMatch: {
              input: "$$sale",
              regex: "^India\/",
              options: "i"
            }
          }
        }
      }
    }
  }
])

https://mongoplayground.net/p/NGDSVQeXtRI

Older MongoDB releases

db.collection.aggregate([
  {
    $match: {
      "sales": {
        $elemMatch: {
          $regex: "^India\/",
          $options: "i"
        }
      }
    }
  },
  {
    $set: {
      sales: {
        $filter: {
          input: "$sales",
          as: "sale",
          cond: {
            $eq: [
              {
                $toLower: {
                  $arrayElemAt: [
                    {
                      $split: [
                        "$$sale",
                        "/"
                      ]
                    },
                    0
                  ]
                }
              },
              "india"
            ]
          }
        }
      }
    }
  }
])

https://mongoplayground.net/p/19TARSVTaMN

user_121957380
Related