Mongo filter/find all elements in embedded array that match a criteria and return surrounding documents?

Question

Basically, I have a Mongo DB with the following structure:

[
  {
    "id": 1,
    "other_thing": "whatever",
    "annoying_array": [
      {
        "sub_id": "a",
        "thing_type": "apple"
      },
      {
        "sub_id": "b",
        "thing_type": "pear"
      }
    ]
  },
  {
    "id": 2,
    "other_thing": "whatever_else",
    "annoying_array": [
      {
        "sub_id": "c",
        "thing_type": "carrot"
      },
      {
        "sub_id": "d",
        "thing_type": "pear"
      },
      {
        "sub_id": "e",
        "thing_type": "pear"
      }
    ]
  }
]

I'd basically like to do something like db.docs.find( {annoying.array.thing_type: "pear"}) and have it return:

[
  {
    "id": 1,
    "other_thing": "whatever",
    "annoying_array": [
      {
        "sub_id": "b",
        "thing_type": "pear"
      }
    ]
  },
  {
    "id": 2,
    "other_thing": "whatever_else",
    "annoying_array": [
      {
        "sub_id": "d",
        "thing_type": "pear"
      },
      {
        "sub_id": "e",
        "thing_type": "pear"
      }
    ]
  }
]

This question seems relevant, but is almost 10 years old, and I know a ton of the filter/aggregation pipeline has changed since then. There's probably a much cleaner way of doing it.

I've tried everything I could think of, from find to match and elemMatch but everything returns all document and sub-arrays for which there exists thing_type: 'pear', not every element in the sub-array for which thing_type = pear. Even if it didn't return the surrounding metadata (i.e. didn't include other_thing, etc.) I'd settle for just returning every element (sub-document) for which a key matches a specific value.

It'd also be nice to have the option to return each matching element of the annoying_array that matches as its own document, with the document-wide data (i.e. id, or other_thing) projected to each match.

Answer 1

You can use Aggregation Pipeline:

• $match to find all documents which annoying_array contains item which thing_type propery is equal to pear.
• $project to specify which properties you want to return from the result
• $filter to filter only those items in annoying_array which thing_type propery is equal to pear.

db.collection.aggregate([
  {
    "$match": {
      "annoying_array.thing_type": "pear"
    }
  },
  {
    "$project": {
      "id": 1,
      "other_thing": 1,
      "annoying_array": {
        "$filter": {
          input: "$annoying_array",
          as: "item",
          cond: {
            "$eq": [
              "$$item.thing_type",
              "pear"
            ]
          }
        }
      }
    }
  }
])

Here is the working exmaple: https://mongoplayground.net/p/7bLcScBQv8c