filtering MongoDB array of Nested objects

Question

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?

Does this answer your question? [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — Ikechukwu Eze, Oct 26 '22 at 08:36
sort of, db.people.find( {}, { name: 1, fruit: { $elemMatch: { foodname: "apple" } } }) returns just the first element in the array but I would like it to return all of them that match — Oliver, Oct 26 '22 at 08:48
Most of the answers have aggregate options, that would return more than the first element — Ikechukwu Eze, Oct 26 '22 at 12:26

Tom Slabbaert · Accepted Answer · 2022-10-26T08:50:32.857

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

This is life changing, thank you :) – Oliver Oct 26 '22 at 09:12 — Oliver, Oct 26 '22 at 09:12

filtering MongoDB array of Nested objects

1 Answers1

Linked