CODEWITHSUNDEEP
mongodbmongodb-query
torbenrudgaard
torbenrudgaard

Reputation: 2561

MongoDB two or more $match comparisons on the same field does not work

This works:

    $match: {
        $and: 
        [{ 
            status: {$lt: 4},       
            checkin: {$gte: 1490720400, $lte: 1490806799} 
        }]
    }

This does not:

    $match: {
        $and: 
        [{ 
            status: {$lt: 4},       
            checkin: {$gte: 1490720400},
            checkin: {$lte: 1490806799}
        }]
    }

Why is that?

In the second example it completely ignores the second comparison.

If that is so, then what would happen to this one?

    $match:
    { 
        $and: 
        [
            { checkin: {$gte: 1490979600}, checkout: {$lte: 1493569439} },
            { checkin: {$lt:  1490979600}, checkout: {$gt:  1493569439} },
            { checkin: {$lt:  1490979600}, checkout: {$gt:  1490979600} },
            { checkin: {$lt:  1493569439}, checkout: {$gt:  1493569439} }
        ]
    }

Would that be the same? That it looks at the first one and ignores the next three?

Is there a way to avoid that?

Upvotes: 0

Views: 89

Answers (2)

AshokGK
AshokGK

Reputation: 875

Given Ex:1

$match: {
        $and: 
        [{ 
            status: {$lt: 4},       
            checkin: {$gte: 1490720400, $lte: 1490806799} 
        }]
    }

In this example there is no ambiguity on field names, so works fine

Given Ex:2

$match: {
        $and: 
        [{ 
            status: {$lt: 4},       
            checkin: {$gte: 1490720400},
            checkin: {$lte: 1490806799}
        }]
    }

Here there is a ambiguity on filed "checkin" so, "checkin" will be overwritten by next "checkin" while constructing the object (it depends which one will be overwritten, usually first will replaced by second)

To make it work you need to use like below

$match:
        { 
            $and: 
            [
             {status: {$lt: 4}},       
             {checkin: {$gte: 1493569438} },
             {checkin: {$lte: 1493569440}}
           ]
        }
    }

or you can use like in Given Ex:1

Given Ex:3

$match:
    { 
        $and: 
        [
            { checkin: {$gte: 1490979600}, checkout: {$lte: 1493569439} },
            { checkin: {$lt:  1490979600}, checkout: {$gt:  1493569439} },
            { checkin: {$lt:  1490979600}, checkout: {$gt:  1490979600} },
            { checkin: {$lt:  1493569439}, checkout: {$gt:  1493569439} }
        ]
    }

will work fine, i.e It will check all the 4 conditions in $and

Upvotes: 2

Alex Blex
Alex Blex

Reputation: 37048

{ 
    status: {$lt: 4},       
    checkin: {$gte: 1490720400},
    checkin: {$lte: 1490806799}
}

is just wrong syntax for JSON documents. The field name should be unique. You declare checkin twice, which makes it ambiguous, and it is up to the parser how to resolve it. E.g. in mongodb shell the later value overwrites the former:

printjson({ 
    status: {$lt: 4},       
    checkin: {$gte: 1490720400},
    checkin: {$lte: 1490806799}
});

results with 

{
    "status" : {
        "$lt" : 4
    },
    "checkin" : {
        "$lte" : 1490806799
    }
}

Upvotes: 1

Related Questions