Mongo aggregate to exclude objects from array

Question

My sample document

    {   "pId":12345,    "charges": [
          {
             "type": "asr",
             "dId": 123,
             "value": 100
          },
           {
             "type": "asr",
             "dId": 124,
             "value": 120
          },
          {
             "type": "asp",
             "dId": 125,
             "value": 130
          },
          {
             "type": "asn",
             "dId": 126,
             "value": 130
          },
          {
             "type": "aso",
             "dId": 127,
             "value": 150
          }....
    
        ] }

Excluded charges input:

    charges [
        {
            "type": "asr",
             "dId": 123
        },
         {
            "type": "asr",
             "dId": 124
        } ...
    ]

I need to fetch all charges from the sample document except Excluded charges. Can someone help me to solve this?

I tried this

    {}
    {"$project" :{
        "_id" : 0, "pId" : 1,
        "charges": { "$filter" : { "input" : "$charges", "as" : "charge", 
        "cond" :{
            { "$not" : { "$and" : [{ "$eq" : ["$$charge.type", "asr"]}, { "$eq" : ["$$charge.dId", 123]}]}}
    
        }
    }}

When I have multiple excluded charges how can we do this

mohammad Naimi · Accepted Answer · 2021-08-26 13:36:46Z

1

use this :

[
  {
    '$project': {
      'charges': {
        '$map': {
          'input': {
            '$filter': {
              'input': '$charges', 
              'as': 'featuresT', 
              'cond': {
                '$eq': [
                  {
                    '$or': [
                      {
                        '$and': [
                          {
                            '$eq': [
                              '$$featuresT.type', 'asr'
                            ]
                          }, {
                            '$eq': [
                              '$$featuresT.dId', 123
                            ]
                          }
                        ]
                      }, {
                        '$and': [
                          {
                            '$eq': [
                              '$$featuresT.type', 'asr'
                            ]
                          }, {
                            '$eq': [
                              '$$featuresT.dId', 124
                            ]
                          }
                        ]
                      }
                    ]
                  }, false
                ]
              }
            }
          }, 
          'as': 'featuresF', 
          'in': {
            'type': '$$featuresF.type', 
            'dId': '$$featuresF.dId', 
            'value': '$$featuresF.value'
          }
        }
      }
    }
  }
]

edited Aug 26, 2021 at 13:36

answered Aug 26, 2021 at 12:06

mohammad Naimi

2,3591 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

phani Over a year ago

Thanks for your reply. If I have multiple types in my document how can we use this $ne': [ '$$featuresT.type', 'asp. Pls revisit the edited question.

mohammad Naimi Over a year ago

let me know to found correct , you have multiple types to exclude from result ?

phani Over a year ago

If my exclude charge is charges are [ { "type": "asr" "dId": 123 }. how it will be?

phani Over a year ago

Sorry for the confusion excluded charge also will be multiple like { "type": "asr" "dId": 123 }, { "type": "asr" "dId": 124 } ,{ "type": "aso" "dId": 127 } how we can group the excluded charges while filtering

phani Over a year ago

Let us continue this discussion in chat.

phani · Accepted Answer · 2021-08-27 04:06:05Z

found a simple way.

db.collection.aggregate([
  {
    $match: {
      "pId": {
        $eq: 12345
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "pId": 1,
      "charges": {
        "$filter": {
          "input": "$charges",
          "as": "charge",
          "cond": {
            "$not": {
              "$or": [
                {
                  "$and": [
                    {
                      "$eq": [
                        "$$charge.type",
                        "asr"
                      ]
                    },
                    {
                      "$eq": [
                        "$$charge.dId",
                        123
                      ]
                    }
                  ]
                },
                {
                  "$and": [
                    {
                      "$eq": [
                        "$$charge.type",
                        "asr"
                      ]
                    },
                    {
                      "$eq": [
                        "$$charge.dId",
                        124
                      ]
                    }
                  ]
                }
              ]
            }
          }
        }
      }
    }
  }
])

mongoplayground

NeNaD · Accepted Answer · 2021-08-27 04:17:41Z

0

$filter to filter charges array
$in with $not to exclude only the values that you want

db.collection.aggregate([
  {
    "$project": {
      "_id": 0,
      "pId": 1,
      "charges": {
        "$filter": {
          "input": "$charges",
          "cond": {
            "$not": {
              "$in": [
                "$$this.dId",
                [123, 124]
              ]
            }
          }
        }
      }
    }
  }
])

Here is the working example: https://mongoplayground.net/p/0uIdoml384h

answered Aug 27, 2021 at 4:17

NeNaD

20.8k11 gold badges64 silver badges118 bronze badges

Collectives™ on Stack Overflow

Mongo aggregate to exclude objects from array

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related