Does the mongodb $filter operation work with nested arrays?

Question

I'm trying to use the $filter operation on a nested array and cannot seem to get it to work right.

Does the $fliter operation work with nested arrays?

This is my MongoDB collection

{
    metrics: [
        {hits: 4 ,data: [
        {"status_Code" : 200, "response_time" : 245 },
        {"status_Code" : 200, "response_time" : 343},
        {"status_Code" : 501, "response_time" : 345 },
        {"status_Code" : 200, "response_time" : 234},
        {"status_Code" : 0, "response_time" : -1, },
    ]
        },
        {hits: 2 ,data: [
        {"status_Code" : 200, "response_time" : 245},
        {"status_Code" : 200, "response_time" : 343},
        {"status_Code" : 0, "response_time" : -1},
        {"status_Code" : 0, "response_time" : -1},
        {"status_Code" : 0, "response_time" : -1},
        ]}
    ],
 }

Aggregate Query:

db.collection.aggregate([ 
{
    $project: {
        metrics: {
            $filter: {
                input: "$metrics.data",
                as: "mdata",
                cond: {$gt: ["$$mdata.response_time", -1]}
            }
        }
    }
}
]);

Desired Output:

{
    metrics: [
        {
            hits: 4 ,
            data: [
                {"status_Code" : 200, "response_time" : 245 },
                {"status_Code" : 200, "response_time" : 343},
                {"status_Code" : 501, "response_time" : 345 },
                {"status_Code" : 200, "response_time" : 234},
            ]
        },
        { 
            hits: 2 ,
            data: [
                { "status_Code" : 200, "response_time" : 245},
                {"status_Code" : 200, "response_time" : 343},
            ] 
        }

    ]
}

score 4 · Accepted Answer · edited May 23 '17 at 12:30

You can't use the $filter operator here because there is no field named "response_time" in the "metrics" array sub-documents.

To use $filter you will need to $unwind the "metrics" array.

db.collection.aggregate([ 
    { '$match': { 'metrics.data.response_time': { '$gt': -1 } } },
    { '$unwind': '$metrics' },
    { '$project': {
        'metrics': { 
            '$filter': {
                'input': "$metrics.data", 
                'as': "mdata",                 
                'cond': { '$gt': [ '$$mdata.response_time', -1 ] }               
            }        
         }  
    }} 
])

Another option is to use $redact as shown here.

db.collection.aggregate([ 
    { '$match': { 'metrics.data.response_time': { '$gt': -1 } } },
    { '$redact': { 
        '$cond': [
            { '$or': [
                { '$gt': [ '$response_time', -1  ] },
                { '$not': '$response_time' } ] 
            }, 
            '$$DESCEND', 
            '$$PRUNE'
        ]
    }}
] )

Does the mongodb $filter operation work with nested arrays?

1 Answers1