Filter the sub array of an array by some criteria

Question

Hi have a document in the format :

{
   "_id":"someId",
   "someArray":[
      {
         "subId":1,
         "subArray":[
            {
               "field1":"A",
               "filterMe":"NO"
            },
            {
               "field1":"B",
               "filterMe":"YES"
            }
         ]
      },
      {
         "subId":2,
         "subArray":[
            {
               "field1":"C",
               "filterMe":"YES"
            },
            {
               "field1":"D",
               "filterMe":"NO"
            }
         ]
      }
   ]
}

how can i filter the subArray based on some criteria. Example filter out the subArray if field filterMe is "YES". so finally the output json should be like

{
   "_id":"someId",
   "someArray":[
      {
         "subId":1,
         "subArray":[
            {
               "field1":"A",
               "filterMe":"NO"
            }
         ]
      },
      {
         "subId":2,
         "subArray":[
            {
               "field1":"D",
               "filterMe":"NO"
            }
         ]
      }
   ]
}

I tried in the below way but, its filtering the whole subArray.

db.testJson.aggregate([
    { $project: {
        'someArray.subArray': {
          $filter: {
            input: '$someArray.subArray',
            as: 'input',
            cond: {$eq: ["$$input.filterMe", "YES"]}
        }}
    }}
]);

See if this helps. https://stackoverflow.com/questions/29071748/find-in-double-nested-array-mongodb — s7vr, Jun 02 '17 at 12:26
@Neil Lunn The referred post has a answer does exactly what your current answer does without the extra outer `$filter`. What did I miss ? — s7vr, Jun 02 '17 at 12:34

score 3 · Accepted Answer · answered Jun 02 '17 at 12:28

You just need a $filter inside a $map:

db.junk.aggregate([
  { "$project": {
    "someArray": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$someArray",
             "as": "some",
             "in": {
               "subId": "$$some.subId",
               "subArray": {
                 "$filter": {
                   "input": "$$some.subArray",
                   "as": "sub",
                   "cond": { "$ne": [ "$$sub.filterMe", "YES" ] }
                 }
               }
             }
          }
        },
        "as": "some",
        "cond": { "$gt": [ { "$size": "$$some.subArray" }, 0 ] }
      }
    }
  }}
])

Produces:

{
        "_id" : "someId",
        "someArray" : [
                {
                        "subId" : 1,
                        "subArray" : [
                                {
                                        "field1" : "A",
                                        "filterMe" : "NO"
                                }
                        ]
                },
                {
                        "subId" : 2,
                        "subArray" : [
                                {
                                        "field1" : "D",
                                        "filterMe" : "NO"
                                }
                        ]
                }
        ]
}

I actually wrap that in an additional $filter to remove any someArray entries where the filtered subArray ended up being empty as a result. Mileage may vary on that being what you want to do.

@SparkOn Not sure what you mean. In the context of your question you either want equality or inequality. The "logical" form of `$exists` is [`$ifNul`](https://docs.mongodb.com/manual/reference/operator/aggregation/ifNull/) but that does not apply here. Be careful since if your real data does not look like the question you asked, then trying to apply to something different may not get you the same result. If you have another question then you should [Ask a new Question](https://stackoverflow.com/questions/ask) instead. Much clearer that way. — Neil Lunn, Jun 02 '17 at 12:44

Filter the sub array of an array by some criteria

1 Answers1

Linked