filtering MongoDB array of Nested objects-CodePudding

I'm using MongoDB Compass for my queries while searching through a lot of data that I've inherited and quite often being asked to produce reports on the data for various teams but the documents often have too much data for them to easily parse so I'd like to cut down the data being reported on as much as possible

I've got the following example document

{
  "_id": "123456",
  "name": "Bob",
  "date": "2022-07-01",
  "fruit": [
    {
      "_id": "000001",
      "foodName": "apple",
      "colour": "red"
    },
    {
      "_id": "000002",
      "foodName": "apple",
      "colour": "green"
    },
    {
      "_id": "000003",
      "foodName": "banana",
      "colour": "yellow"
    },
    {
      "_id": "000004",
      "foodName": "orange",
      "colour": "orange"
    }
  ]
}

using

db.people.find( { "fruit.foodName" : "apple" } )

returns the whole document

I'd like to search for just the apples so that I get the result:

{
  "_id": "123456",
  "name": "Bob",
  "date": "2022-07-01",
  "fruit": [
    {
      "_id": "000001",
      "foodName": "apple",
      "colour": "red"
    },
    {
      "_id": "000002",
      "foodName": "apple",
      "colour": "green"
    }
  ]
}

Is that possible?

CodePudding user response：

You will need to use an aggregation for this and use the $filter operator, The reason you can't use the query language for this is because their projection options are limited and only allow the projection of a single array element, because in your case the array can contain more than 1 matching subdocument it won't do.

You can read more about query language projections here

db.collection.aggregate([
  {
    $match: {
      "fruit.foodName": "apple"
    }
  },
  {
    $addFields: {
      fruit: {
        $filter: {
          input: "$fruit",
          cond: {
            $eq: [
              "$$this.foodName",
              "apple"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground