How to filter string arrays within documents in mongodb?

Question

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"
            }
          }
        }
      }
    }
  }
])

Rfroes87 · Accepted Answer · 2020-08-27 21:19:06Z

1

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

edited Aug 27, 2020 at 21:19

answered Aug 26, 2020 at 22:50

Rfroes87

6781 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rfroes87 Over a year ago

I see what you're trying to do, but you'd only be able to reproduce it with an aggregate query, as opposed to find.

Rfroes87 Over a year ago

Try this: mongoplayground.net/p/Ve5TQQV77xG

Rfroes87 Over a year ago

$regexMatch expression is available since MongoDB 4.2 - aside from development version 4.1.11.

Rfroes87 Over a year ago

One more option: mongoplayground.net/p/pqEKvqjefNP

Rfroes87 Over a year ago

If your issue was solved with this, please accept the answer.

Collectives™ on Stack Overflow

How to filter string arrays within documents in mongodb?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related