MongoDB aggregation one of three field is not an empty array-CodePudding

I have the following aggregation:

    db.processedRowsWithRules.aggregate([
    {
        $lookup: {
            from: 'ruleExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'rule_exceptions'
        }
    },
    
    {
        $lookup: {
            from: 'validationsExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'validation_exceptions'
        }
    },
    {
        $lookup: {
            from: 'sanctionExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'sanction_exceptions'
        }
    }
])

I am trying to filter the response for any of the rows that contain at least one of sanction_exceptions, rule_exceptions or validation_exceptions. If I find any row where those are empty arrays [] then I want to discard them.

Thanks for your help!

CodePudding user response：

Add a $match stage after the $lookups. Use $or with $exists to check for non-empty arrays.

  {
    "$match": {
      "$or": [
        {
          "rule_exceptions.0": {
            "$exists": true
          }
        },
        {
          "validation_exceptions.0": {
            "$exists": true
          }
        },
        {
          "sanction_exceptions.0": {
            "$exists": true
          }
        }
      ]
    }
  }

The entire call would then be:

db.processedRowsWithRules.aggregate([
    {
        $lookup: {
            from: 'ruleExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'rule_exceptions'
        }
    },

    {
        $lookup: {
            from: 'validationsExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'validation_exceptions'
        }
    },
    {
        $lookup: {
            from: 'sanctionExceptions',
            localField: 'row_id',
            foreignField: 'row_id',
            as: 'sanction_exceptions'
        }
    },
    {
        $match: {
            $or: [
                {
                    'rule_exceptions.0': {
                        '$exists': true
                    }
                },
                {
                    'validation_exceptions.0': {
                        '$exists': true
                    }
                },
                {
                    'sanction_exceptions.0': {
                        '$exists': true
                    }
                }
            ]
        }
    }
])