CODEWITHSUNDEEP
mongodbmongodb-querynosqlaggregation-framework
MirroredFate
MirroredFate

Reputation: 12816

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

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:

Upvotes: 0

Views: 2486

Answers (2)

Talha Noyon
Talha Noyon

Reputation: 854

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
            ]
          }
        ]
      }
    }
  }
})

Upvotes: 0

MirroredFate
MirroredFate

Reputation: 12816

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

The steps are:

  1. First $match based on the type criterion, to ensure only the parent documents with the appropriate type are used subsequently in the pipeline.
  2. 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.
  3. 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.

Upvotes: 1

Related Questions