MongoDB: aggregation with redact with $in fails

Question

I've got the following two Objects in a mongo collection named cars:

{vendor: "BMW",
model: "325i",
class: "coupe",
equipment: [
    {name: "airbag",
     cost: 120
    },
    {name: "led",
     cost: 170
    },
    {name: "abs",
     cost: 150
    }
 ]
}

{vendor: "Mercedes",
model: "C 250",
class: "coupe",
equipment: [
    {name: "airbag",
     cost: 180
    },
    {name: "esp",
     cost: 170
    },
    {name: "led",
     cost: 120
    }
 ]
}

I'm playing around with the new aggregation feature redact introduced in Mongo 2.6 I want to perform a query to get a list of cars with filtered equipment parts. In words: Give me all cars of type coupe and only the information about LED and AIRBAG equipment.

I've tried this aggregations:

db.cars.aggregate(
    [
        {$match: {"class": "coupe"}},
        {$redact:
        {
            $cond:
            {
                if: { $in : ["$name", ["airbag", "led"]]},
                then: "$$DESCEND",
                else: "$$PRUNE"
            }
        }
        }
    ]
)

But that leads to following error:

assert: command failed: { "errmsg" : "exception: invalid operator '$in'", "code" : 15999, "ok" : 0 } : aggregate failed

What I'm doing wrong? Is there another way to achieve the goal.

I would expect to get back this result from Mongo:

{vendor: "BMW",
model: "325i",
class: "coupe",
equipment: [
    {name: "airbag",
     cost: 120
    },
    {name: "led",
     cost: 170
    }
 ]
}

{vendor: "Mercedes",
model: "C 250",
class: "coupe",
equipment: [
    {name: "airbag",
     cost: 180
    },
    {name: "led",
     cost: 120
    }
 ]
}

Any suggestions on how to achieve this?

Cheers, Ralf

You want to use $setIsSubset instead of $in when performing aggregations (including redaction) in MongoDB. See docs.mongodb.org/manual/reference/operator/aggregation/… — Paul
You can use $filter with $in from 3.4 version. Something like db.cars.aggregate([ { "$match": { "class": "coupe", "equipment.name": { "$in": ["airbag", "led"] } } }, { "$addFields": { "equipment": { "$filter": { "input": "$equipment", "as": "equipment", "cond": { "$in": [ "$$equipment.name", ["airbag", "led"] ] } } } } } ]) — s7vr

Neil Lunn Neil Lunn · Accepted Answer · 2014-07-01T08:59:16

The $redact pipeline operator is really not the one you want for this case. What it wants to do is recursively "descend" through the document structure and evaluate the conditions at each "level" to see what actions it is going to take.

In your case, there is no "name" field at the top level of the document in order to meet the condition, so as a result the whole document is "pruned".

You are after filtering arrays, and for that case where you do not want to use $unwind and $match, then you can use the new operators such as $map:

db.cars.aggregate([
    { "$match": { "class": "coupe" }},
    { "$project": {
        "equipment": {
            "$setDifference": [
                { "$map": {
                    "input": "$equipment",
                    "as": "el",
                    "in": {
                        "$cond": [
                             { "$or": [
                                { "$eq": [ "$$el.name", "airbag" ] },
                                { "$eq": [ "$$el.name", "led" ] }
                             ]},
                             { "$cond": [ 1, "$$el", 0 ] },
                             false
                        ]
                    }
                }},
                [false]
            ]
        }
    }}
])

The $map operator works with the array and evaluates a logical condition against all of the elements, in this case within $cond. The same thing as $in which is not a logical operator in this sense is using $or, which is a logical operator for the aggregation framework.

As any item that does not meet the condition would return false, what you need to do now is "remove" all of the false values. This is aided by $setDifference which will do this by comparison.

The result is what you want:

{
    "_id" : ObjectId("53b2725120edfc7d0df2f0b1"),
    "equipment" : [
            {
                    "name" : "airbag",
                    "cost" : 120
            },
            {
                    "name" : "led",
                    "cost" : 170
            }
    ]
}
{
    "_id" : ObjectId("53b2725120edfc7d0df2f0b2"),
    "equipment" : [
            {
                    "name" : "airbag",
                    "cost" : 180
            },
            {
                    "name" : "led",
                    "cost" : 120
            }
    ]
}

If you were really intent on using $redact then there is always this contrived example:

db.cars.aggregate([
    { "$match": { "class": "coupe" }},
    { "$redact": {
        "$cond": {
            "if": {
                "$or": [
                    { "$eq": [
                       { "$ifNull": ["$name", "airbag"] },
                       "airbag"  
                    ]},
                    { "$eq": [
                       { "$ifNull": ["$name", "led"] },
                       "led"  
                    ]},
                ]
            },
            "then": "$$DESCEND",
            "else": "$$PRUNE"
        }
    }}
])

So that basically makes sure that if the field does not exist at all when descending then it will "artificially" produce one that is going to match, so that level does not get "pruned". Where the field does exist, the found value is used, and if that does not match the condition then it will be pruned.

MongoDB: aggregation with redact with $in fails

1 Answers