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 — Ikechukwu Eze
– Ikechukwu Eze, Commented Oct 26, 2022 at 8: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
– Oliver, Commented Oct 26, 2022 at 8:48
Most of the answers have aggregate options, that would return more than the first element — Ikechukwu Eze
– Ikechukwu Eze, Commented Oct 26, 2022 at 12:26

Tom Slabbaert · Accepted Answer · 2022-10-26 08:50:32Z

1

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

edited Oct 26, 2022 at 8:50

answered Oct 26, 2022 at 8:38

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

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

1 Comment

Oliver Over a year ago

This is life changing, thank you :)

Collectives™ on Stack Overflow

filtering MongoDB array of Nested objects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related