Building dynamic $match: for MongoDB aggregates

Question

I am trying to build a dynamic $match: for my MongoDB aggregate

This is what I wish I could do:

var matchStr = "";
if (req.body.propertyID) {
    matchStr += "property: { $in: properties },"
}
if (req.body.status=="ACTIVE" || req.body.status=="NOPAY") {
    matchStr += "status : {$lte: 4},"
} else if (req.body.status) {
    matchStr += "status : {$lte: req.body.status},"
}
matchStr += "checkin : {$gte: (checkin)}, checkout: {$lte: (checkout)}"

And this is what I end up doing:

Its so messy, and now I have to add even more statements to the $match which will give me even bigger if structures.

There has to be a better way to do this!

I hope someone out there has an idea how to build these dynamically without the "if hell :)"

if (req.body.propertyID & req.body.status=="ALL") {
    var matchStr = { 
        $match: {
            $and: 
            [{ 
                property: { $in: properties },
                checkin : {$gte: (checkin)}, 
                checkout: {$lte: (checkout)}    
            }]
        }
    }
} else if (!req.body.propertyID & !req.body.status) {
    var matchStr = { 
        $match: {
            $and: 
            [{ 
                checkin : {$gte: (checkin)}, 
                checkout: {$lte: (checkout)}    
            }]
        }
    }
} else if ((req.body.status=="ACTIVE" || req.body.status=="NOPAY")) {
    if (req.body.propertyID) { 
        var matchStr = { 
            $match: {
                $and: 
                [{ 
                    property: { $in: properties },
                    status : {$lte: 4}, 
                    checkin : {$gte: (checkin)}, 
                    checkout: {$lte: (checkout)}    
                }]
            }
        }
    } else {
        var matchStr = { 
            $match: {
                $and: 
                [{ 
                    status : {$lte: 4}, 
                    checkin : {$gte: (checkin)}, 
                    checkout: {$lte: (checkout)}    
                }]
            }
        }
    }
} else {
    if (req.body.propertyID) { 
        var matchStr = { 
            $match: {
                $and: 
                [{ 
                    property: { $in: properties },
                    status : {$lte: req.body.status}, 
                    checkin : {$gte: (checkin)}, 
                    checkout: {$lte: (checkout)}    
                }]
            }
        }
    } else {
        var matchStr = { 
            $match: {
                $and: 
                [{ 
                    status : {$lte: req.body.status}, 
                    checkin : {$gte: (checkin)}, 
                    checkout: {$lte: (checkout)}    
                }]
            }
        }
    }
}

UPDATE! - Almost got it now, except it puts the "myMatch" inside the mongoDB statement.

Here is the new code:

var myMatch = {
    checkin: {$gte: checkin},
    checkout: {$lte: checkout}
};

if (req.body.hasOwnProperty('propertyID')) {
    var properties = req.body.propertyID.map(function (obj) {
        return obj._id;
    });
    console.log("properties: " + properties); 
    myMatch["property"] = { "$in": properties };
}
if (req.body.status=="ACTIVE" || req.body.status=="NOPAY") {
    myMatch["status"] = { "$lte": 4 };
} else if (req.body.hasOwnProperty('status')) {
    myMatch["status"] = { "$lte": +req.body.status };
}

And here is a console log of that structure:

myMatch: {
    "checkin": {
        "$gte": 1473571600
    },
    "checkout": {
        "$lte": 1596163600
    },
    "property": {
        "$in": [
            "PAP-720",
            "ATL-D406",
            "WAT-606"
        ]
    },
    "status": {
        "$lte": 3
    }
}

Here is where I inject the myMatch object:

    bookingTable.aggregate([
        {
            $match: {
                $and: 
                [{ 
                    myMatch
                }]
            }
        },

And here is the final mongodb statement:

Mongoose: 
booking.aggregate([ 
{ '$match': { '$and': [ { myMatch: { checkin: { '$gte': 1473571600 }, checkout: { '$lte': 1596163600 }, property: { '$in': [ 'PAP-720', 'ATL-D406', 'WAT-606' ] }, status: { '$lte': 3 } } } ] } },

Well for starters it looks like `checkin` and `checkout` are consistently present in every case, so you could just create a base object, as it's trivial to add properties to JavaScript objects. Secondly have you ever heard of [`switch`](https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Statements/switch)? Whatever you do stop thinking in terms of strings and learn a little about object manipulation. — Neil Lunn, May 22 '17 at 09:40
Here's a small example of something a lot more complex in structure, done in a couple of different languages where but the concepts are basically the same for anything [Generating a Structure for Aggregation](http://stackoverflow.com/questions/22198172/generating-a-structure-for-aggregation) — Neil Lunn, May 22 '17 at 09:43
Yes I can move checkin and checkout down, I also looked at the example for the `$project` pipeline but Im not quite sure I understand it. Its a bit over my head. But it do look like you can put multiple objects into the match instead of just having one big string, am I correct? — torbenrudgaard, May 22 '17 at 09:51
!Sigh! Okay then at least please answer me this then. Your `checkin` and `checkout` are both surrounded by brackets above. Why? Are these not already arrays? Please say that the `properties` used with `$in` is at least an array. — Neil Lunn, May 22 '17 at 09:56
@NeilLunn - yes the `properties` is an array `["AAA","BBB]` etc... I'm not sure what you mean by the checkin. Should I do this instead? `checkin : {$gte: checkin}` ? (sorry, im still trying to learn how these structures work :-)) — torbenrudgaard, May 22 '17 at 10:07
@NeilLunn you were right - no need for the (checkin) brackets. Dont even know why I put them there, they made no difference at all :) — torbenrudgaard, May 22 '17 at 10:13

Neil Lunn · Accepted Answer · 2017-05-22T10:50:57.570

3

First thing you really need to note is that you almost never need to write an explicit $and statements. It has a usage, but typically all expressions in a MongoDB query are already an "AND" operation.

The next thing is that the solution will look remarkably familiar:

var myMatch = {
    checkin: checkin,
    checkout: checkout
};

if (req.body.propertyID) {
    myMatch["property"] = { "$in": properties };
}
if (req.body.status=="ACTIVE" || req.body.status=="NOPAY") {
    myMatch["status"] = { "$lte": 4 };
} else if (req.body.hasOwnProperty('status')) {
    myMatch["status"] = { "$lte": +req.body.status };
}

The property names are pretty normal so you could alternately do:

    myMatch.property = { "$in": properties };

But this all comes down to basic manipulation of JavaScript objects. And it's pretty much the same in any dynamically typed language.

Then you can just do later:

{ "$match": myMatch }

And its done.

edited May 22 '17 at 10:50

answered May 22 '17 at 10:04

Neil Lunn

148,042
36
346
317

Ahhhhhh!!!!! I get it!! - So you can simply treat each clause as an object id and then add the parameters to that. So simple - I had no idea this was possible. Niel if this works I can cut a thousand lines from my controllers :-) Let me try it out and get back to you :) – torbenrudgaard May 22 '17 at 10:16
`req.body.hasOwnProperty['status']` that one I didnt know - thats a much more elegant way to ask for the object property instead of just `!req.body.status` which always seem to give me problems. – torbenrudgaard May 22 '17 at 10:20
@torbenrudgaard Note that I'm basically just following your thinking on constructing strings but correcting to manipulating objects. I have made a change on that last `else if` condition as 1.) It seems more logical to test for the logical presence of the "status" property here rather than the "truthyness" of the value returned. 2. ) Given the earlier condition against status is numeric ( `"$lte": 4` ) then it's also logical that "status" should be a numeric value. Unless you've run a body parser over it, then `req.body` items are typically strings. So `+req.body.status` converts. – Neil Lunn May 22 '17 at 10:21
Instead of `if (!req.body.propertyID)` I tried `if (req.body.hasOwnProperty['propertyID'])` but it does not work. The data in req.body is: `{"propertyID": [{"_id": "PAP-720", "_id": "ATL-D406"}]}` so why do `hasOwnProperty['propertyID']` return undefined? – torbenrudgaard May 22 '17 at 10:44
Baaah brackets were wrong :-) `if (req.body.hasOwnProperty('propertyID'))`did the trick :) – torbenrudgaard May 22 '17 at 10:49
@torbenrudgaard My bad. Wasn't paying attention when typing. Hopefully you found a manual page. But I do have to warn here that you are changing logic dramatically from how you present it in your question. Your field was `'status'` and I could at least discern from your post that it must be a numeric value. I cannot guess nor be responsible for vast changes like you appear to be doing. We can only answer questions as they are presented. – Neil Lunn May 22 '17 at 10:54
@Niel Lunn - yes I just figured out about the status problem, I didnt know you could do `+req.body.status` - so I learned something new. But now I got a new problem - everything is fine but it puts the `myMatch` text into the mongodb statement... `{ myMatch: { checkin: { '$gte': 1473571600 }, checkout: { '$lte': 1596163600 }, property: { '$in': [ 'WAT-606' ] }, status: { '$lte': 3 } } }` . – torbenrudgaard May 22 '17 at 10:58
@torbenrudgaard Not "my" code. "You" are doing something different. In my code `myMatch` is just a variable name for which is assigned an `Object`. Then later I'm simply defining another object with the `$match` key and assigning the earlier object variable. So there is nothing in my code that specifies a `"myMatch"` key. – Neil Lunn May 22 '17 at 11:03
@Niel Lunn - I think you are right. I did this: `var myMatch = new Object;` before I start adding values to it. Perhaps that was a mistake? – torbenrudgaard May 22 '17 at 11:07
I updated the source code above to include the new code. Perhaps you can see where I went wrong? – torbenrudgaard May 22 '17 at 11:16
@torbenrudgaard I see the problem and I already say it in the answer. 1.) Don't use `$and` at all. 2. Just write `bookingTable.aggregate([{ "$match": myMatch }])`. That's it. When you write `{ myMatch }`, then that's ES6 shorthand for writing `{ myMatch: myMatch }` where you actually want a key with the same value as the variable you are assigning. So like I said, do as "I do" and not how you make it up. – Neil Lunn May 22 '17 at 11:25
YES!! That did the trick! - I guess the whole $and is not necessary. Thank you SO MUCH for teaching me, this will save me many hours and many lines of code!!! - One quick followup question. What if I have to use a `$or` - would I then have to put that into my object structure? And is there any way to only show the result of an object without the name? like an `myObj.showContent()` or something? – torbenrudgaard May 22 '17 at 11:39

Building dynamic $match: for MongoDB aggregates

1 Answers1