Is there a way to do this using aggregation?

Working with Data
1

I have two collections: vehicles and setters

Setters
A setter document has two main fields: conditions and result. It’s used to store some rules like “if Category = d10 and Brand = BMW then isTransportable = false”. The way we store this in the setters collections is :

{
     "conditions": {
         "category": "d10",
         "brand": "BMW"
     },
     "result": {
         "isTransportable": false
     }
 }

Another example: “if brand = Ferrari then isPricey = true” is stored

{
     "conditions": {
         "brand": "Ferrari"
     },
     "result": {
         "isPricey": true
     }
 }

Remarks:

  1. A setter can have multiple conditions but always have one result (i.e. the subdocument “result” will always have one single field).
  2. The fields that are used in the result does never appear in conditions. There is no setter document for which isPricey or isTransportable is used as a condition. And vice-versa, the fields used in conditions are never used in the result. There is no setter document for which brand is a result. Fields appearing in conditions and fields appearing in result are two completely disjoint fields.

Vehicles:
A vehicle document has a flat structure, is has the fields Category, brand, isTransportable, isPricey.

What I am trying to do
I want to update the values of vehicles according to the rules.
If I have a vehicle that have “category” =“d10” and “brand” =“BMW”, I want to update its isTransportable value using the result of the setter document:

{
     "conditions": {
         "category": "d10",
         "brand": "BMW"
     },
     "result": {
         "isTransportable": false
     }
 }

So it will be set the vehicle will have isTransportable equal to false…

However, if my vehicle have category = “d10” and brand != “bmw” then I will not use that rule.
Is there a way to do this using the aggregates, without having to fetch vehicles and setters

2

I had a play and came up with something like this:

  • Create a lookup from vehicles to conditions where we only retain the conditions that apply
  • Filter out vehicles without a matching condition
  • Unwind the conditions matching to build a list of updates we need to apply
  • Reformat the document to be just the _id of the vehicle and the new data to merge in
  • Call a $merge to match on the original document in the vehicles collection and merge in the changes that we found in the matching condition

This is what it looks like:

db.getCollection("vehicles").aggregate([
{
    $lookup:{
        from:'conditions',
        let:{
            'brandMatch':'$brand',
            'categoryMatch':'$category',
        },
        pipeline:[
            {
                $match:{
                    $and:[
                        {
                            $expr:{
                                $or:[
                                    {    
                                        $eq:[
                                            '$conditions.brand',
                                            '$$brandMatch'
                                        ]
                                
                                    },
                                    {
                                        $eq:[
                                            '$conditions.brand', null
                                        ]
                                    }
                                ]
                             }
                        },                    
                        {
                            $expr:{
                                $or:[
                                    {    
                                        $eq:[
                                            '$conditions.category',
                                            '$$categoryMatch'
                                        ]
                                
                                    },
                                    {
                                        $eq:[
                                            '$conditions.category', null
                                        ]
                                    }
                                ]
                             }
                        },                    
                    ]
                }
            }
        ],
        as: 'matchedConditions'
    }
},
{
    $match:{
        "matchedConditions.0":{$exists:true}
    }
},
{
    $unwind:'$matchedConditions'
},
{
    $project:{
        _id:1,
        result:'$matchedConditions.result'       
    }
},
{
    $addFields:{
        'result._id':'$_id'
    }
},
{
    $replaceRoot:{
        newRoot:'$result'
    }
},
{
    $merge:{
        into:'vehicles',
        on:'_id',
        whenMatched:'merge'
    }
}
])

I’ve not played with this on a large scale of document and the downer is that you need to update the query for each condition you add, I guess you could create a script that pulls the unique ones out first and THEN build this based on that list…
I’ve also set it so that it updates if a condition matches or the condition does not exist, you may want to tweak that to perform as you want…

I’m not sure this is the best way, but it’s a way!