MongoDB Aggregation Pipeline filter based on properties of child objects within array

Question

Given documents with the following structure:

{ 'id': 1, name: 'bob', type: 'foo', children: [{'id': 2}, {'id': 3}]}
{ 'id': 2, name: 'bob', type: 'foo' }
{ 'id': 3, name: 'bob', type: 'bar' }
{ 'id': 4, name: 'bob', type: 'foo', children: [{'id': 5}, {'id': 6}]}
{ 'id': 5, name: 'bob', type: 'foo' }
{ 'id': 6, name: 'bob', type: 'foo' }

How could I write an aggregate pipeline query to find all the documents where, if they have children, all the children are of type foo (and the parent is of type 'foo')?

Additional notes:

children is an array of objects with a property referencing other documents in the same collection
not all documents have children
changing the document structure is not an option
I've looked into $unwind and $lookup, but this results in many documents and I only want the parent document at the end of this.

I don't have a working query, hence the question. The closest I've come is with a simple lookup but then getting the match to work against every nested document hasn't been successful. It is not clear to me if that's the correct approach or if something else will work better. Even if that works, I don't know if it's optimal or if there's a simpler approach. I just don't have the mongodb expertise. — MirroredFate
– MirroredFate, Commented Oct 26, 2020 at 22:58
Review idownvotedbecau.se/noattempt then add your query and results. — D. SM
– D. SM, Commented Oct 26, 2020 at 23:10
@D.SM Not to worry, I've added my own answer with the solution I've found. I hope it is useful to others in the future. Feel free to downvote, though. I still don't feel my myriad of failed iterations would have at all helped clarify the question, they would have instead made it substantially more convoluted and confusing. There's an infinite number of wrong ways to do something, and I attempted quite a few. — MirroredFate
– MirroredFate, Commented Oct 26, 2020 at 23:33

MirroredFate · Accepted Answer · 2020-10-26 23:27:10Z

After some additional toying with the aggregation pipeline API, here is one potential solution.

The steps are:

First $match based on the type criterion, to ensure only the parent documents with the appropriate type are used subsequently in the pipeline.
Perform a simple $lookup on the child documents. Although this doesn't appear to be explicitly documented, $lookup can use properties of nested objects in arrays with no difficulty.
Perform a final match on the resulting documents, making use of $elemMatch and some negation to achieve the desired effect.

Here's what that looks like using Robo3T (should be easily translated to other query clients):

Note: In this particular case, id is just a placeholder for whatever the documents are being joined on, it is not the "official" _id mongo field

db.getCollection('items').aggregate([
    { $match : { "type": "foo" } },
    {
        $lookup: {
            from: "items",
            localField: "children.id",
            foreignField: "id",
            as: "items"
        }
    },
    {
        $match : { 
            "items": {
                $not: {
                    $elemMatch: {
                        "type": { $ne: "foo" }
                    }
                }
            }
        }
    }
])

This will exclude documents 1 and 3, since 3 has a type of "bar" and 1 includes it.

This may not be an optimal solution, I have not tested it on large datasets. Also, the final match using $elemMatch is quite messy, so recommendations for improvements on that are welcome.

Talha Noyon · Accepted Answer · 2021-10-04 18:19:12Z

0

I have written a solution. please take a look the query. solution checkup link: https://mongoplayground.net/p/cu7Mf8XZHDI

this query is similar to the question: MongoDB (Mongoose) how to returning all document fields using $elemMatch

db.collection.find({},
{
  students: {
    $filter: {
      input: "$students",
      as: "student",
      cond: {
        $or: [
          {
            $eq: [
              "$$student.age",
              8
            ]
          },
          {
            $eq: [
              "$$student.age",
              15
            ]
          }
        ]
      }
    }
  }
})

answered Oct 4, 2021 at 18:19

Talha Noyon

8648 silver badges14 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregation Pipeline filter based on properties of child objects within array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related