Nested filters: $filter array, then $filter child array

Question

Essentially I'm trying to filter OUT subdocuments and sub-subdocuments that have been "trashed". Here's a stripped-down version of my schema:

permitSchema = {
  _id,
  name,
  ...
  feeClassifications: [
    new Schema({
      _id,
      _trashed,
      name,
      fees: [
        new Schema({
          _id,
          _trashed,
          name,
          amount
        })
      ]
    })
  ],
  ...
}

So I'm able to get the effect I want with feeClassifications. But I'm struggling to find a way to have the same effect for feeClassifications.fees as well.

So, this works as desired:

Permit.aggregate([
  { $match: { _id: mongoose.Types.ObjectId(req.params.id) }},
  { $project: {
    _id: 1,
    _name: 1,
    feeClassifications: {
      $filter: {
        input: '$feeClassifications',
        as: 'item',
        cond: { $not: {$gt: ['$$item._trashed', null] } }
      }
    }
  }}
])

But I also want to filter the nested array fees. I've tried a few things including:

Permit.aggregate([
  { $match: { _id: mongoose.Types.ObjectId(req.params.id) }},
  { $project: {
    _id: 1,
    _name: 1,
    feeClassifications: {
      $filter: {
        input: '$feeClassifications',
        as: 'item',
        cond: { $not: {$gt: ['$$item._trashed', null] } }
      },
      fees: {
        $filter: {
          input: '$fees',
          as: 'fee',
          cond: { $not: {$gt: ['$$fee._trashed', null] } }
        }
      }
    }
  }}
])

Which seems to follow the mongodb docs the closest. But I get the error: this object is already an operator expression, and can't be used as a document expression (at 'fees')

Update: -----------

As requested, here's a sample document:

{
    "_id" : ObjectId("57803fcd982971e403e3e879"),
    "_updated" : ISODate("2016-07-11T19:24:27.204Z"),
    "_created" : ISODate("2016-07-09T00:05:33.274Z"),
    "name" : "Single Event",
    "feeClassifications" : [ 
        {
            "_updated" : ISODate("2016-07-11T19:05:52.418Z"),
            "_created" : ISODate("2016-07-11T17:49:12.247Z"),
            "name" : "Event Type 1",
            "_id" : ObjectId("5783dc18e09be99840fad29f"),
            "fees" : [ 
                {
                    "_updated" : ISODate("2016-07-11T18:51:10.259Z"),
                    "_created" : ISODate("2016-07-11T18:41:16.110Z"),
                    "name" : "Basic Fee",
                    "amount" : 156.5,
                    "_id" : ObjectId("5783e84cc46a883349bb2339")
                }, 
                {
                    "_updated" : ISODate("2016-07-11T19:05:52.419Z"),
                    "_created" : ISODate("2016-07-11T19:05:47.340Z"),
                    "name" : "Secondary Fee",
                    "amount" : 50,
                    "_id" : ObjectId("5783ee0bad7bf8774f6f9b5f"),
                    "_trashed" : ISODate("2016-07-11T19:05:52.410Z")
                }
            ]
        }, 
        {
            "_updated" : ISODate("2016-07-11T18:22:21.567Z"),
            "_created" : ISODate("2016-07-11T18:22:21.567Z"),
            "name" : "Event Type 2",
            "_id" : ObjectId("5783e3dd540078de45bbbfaf"),
            "_trashed" : ISODate("2016-07-11T19:24:27.203Z")
        }
    ]
}

And here's the desired output ("trashed" subdocuments are excluded from BOTH feeClassifications AND fees):

{
    "_id" : ObjectId("57803fcd982971e403e3e879"),
    "_updated" : ISODate("2016-07-11T19:24:27.204Z"),
    "_created" : ISODate("2016-07-09T00:05:33.274Z"),
    "name" : "Single Event",
    "feeClassifications" : [ 
        {
            "_updated" : ISODate("2016-07-11T19:05:52.418Z"),
            "_created" : ISODate("2016-07-11T17:49:12.247Z"),
            "name" : "Event Type 1",
            "_id" : ObjectId("5783dc18e09be99840fad29f"),
            "fees" : [ 
                {
                    "_updated" : ISODate("2016-07-11T18:51:10.259Z"),
                    "_created" : ISODate("2016-07-11T18:41:16.110Z"),
                    "name" : "Basic Fee",
                    "amount" : 156.5,
                    "_id" : ObjectId("5783e84cc46a883349bb2339")
                }
            ]
        }
    ]
}

Sede · Accepted Answer · 2016-07-13 10:57:37Z

Since we want to filter both the outer and inner array fields, we can use the $map variable operator which return an array with the "values" we want.

In the $map expression, we provide a logical $conditional $filter to remove the non matching documents from both the document and subdocument array field.

The conditions are $lt which return true when the field "_trashed" is absent in the sub-document and or in the sub-document array field.

Note that in the $cond expression we also return false for the <false case>. Of course we need to apply filter to the $map result to remove all false.

Permit.aggregate(
    [ 
        { "$match": { "_id": mongoose.Types.ObjectId(req.params.id) } },
        { "$project": { 
            "_updated": 1, 
            "_created": 1, 
            "name": 1, 
            "feeClassifications": { 
                "$filter": {
                    "input": {
                        "$map": { 
                            "input": "$feeClassifications", 
                            "as": "fclass", 
                            "in": { 
                                "$cond": [ 
                                    { "$lt": [ "$$fclass._trashed", 0 ] }, 
                                    { 
                                        "_updated": "$$fclass._updated", 
                                        "_created": "$$fclass._created", 
                                        "name": "$$fclass.name", 
                                        "_id": "$$fclass._id", 
                                        "fees": { 
                                            "$filter": { 
                                                "input": "$$fclass.fees", 
                                                "as": "fees", 
                                                "cond": { "$lt": [ "$$fees._trashed", 0 ] }
                                            }
                                        }
                                    }, 
                                    false 
                                ]
                            }
                        }
                    }, 
                    "as": "cls",  
                    "cond": "$$cls"
                }
            }
        }}
    ]
)

In the upcoming MongoDB release (as of this writing and since MongoDB 3.3.5), You can replace the $cond expression in the the $map expression with a $switch expression:

Permit.aggregate(
    [ 
        { "$match": { "_id": mongoose.Types.ObjectId(req.params.id) } },
        { "$project": { 
            "_updated": 1, 
            "_created": 1, 
            "name": 1, 
            "feeClassifications": { 
                "$filter": {
                    "input": {
                        "$map": { 
                            "input": "$feeClassifications", 
                            "as": "fclass", 
                            "in": { 
                                "$switch": { 
                                    "branches": [ 
                                        { 
                                            "case": { "$lt": [ "$$fclass._trashed", 0 ] }, 
                                            "then": { 
                                                "_updated": "$$fclass._updated", 
                                                "_created": "$$fclass._created", 
                                                "name": "$$fclass.name", 
                                                "_id": "$$fclass._id", 
                                                "fees": { 
                                                    "$filter": { 
                                                        "input": "$$fclass.fees", 
                                                        "as": "fees", 
                                                        "cond": { "$lt": [ "$$fees._trashed", 0 ] }
                                                    }
                                                }
                                            } 
                                        } 
                                    ], 
                                    "default":  false 
                                }
                            }
                        }
                    },
                    "as": "cls",  
                    "cond": "$$cls"
                }
            }
        }}
    ]
)

I couldn't thank you enough! Thanks for the informative answer and the explanations. This makes the hours I spent trying to figure this out on my own worth it!

Dalibor Adamec · Accepted Answer · 2020-03-09 10:21:53Z

0

For more complicated bigdats, it would be unnecessarily difficult. Just edit it in $filter input by adding a dotted annotation field.You can search the document to any depth of JSON by dotted annotation without further complicated $filter mapping.

"$filter":{
       "input": "$feeClassifications._trashed",
       "as": "trashed",
       "cond": { "$lt": [ "$$trashed._trashed", 0 ] }                           
 }

answered Mar 9, 2020 at 10:21

community wiki

Dalibor Adamec

Collectives™ on Stack Overflow

Nested filters: $filter array, then $filter child array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related