CODEWITHSUNDEEP
mongodb
tintin
tintin

Reputation: 1529

MongoDB: elemMatch match the last element in an array

I have the data like below:

{
"order_id" : 1234567,
"order_pay_time" : 1437373297,
"pay_info" : [
    {
        "pay_type" : 0,
        "pay_time" : 1437369046
    },
    {
        "pay_type" : 0,
        "pay_time" : 1437369123
    },
    {
        "pay_type" : 0,
        "pay_time" : 1437369348
    }
]}

what I want to get is the last payment is of type 1, but $elemMatch just match the list where pay_type:1 exists, how can I match the orders which last payment is of "pay_type" : 1

Upvotes: 1

Views: 1670

Answers (2)

Sebastien Briois
Sebastien Briois

Reputation: 86

Just found this thread for a similar problem I've had.

I ended up doing this, maybe that will be of interest to someone:

db.collection.find({
    $where: function(){
        return this.pay_info[this.pay_info.length-1].pay_type === 1
    }
})

Upvotes: 2

Vishwas
Vishwas

Reputation: 7067

You can use aggregation to get expected output. The query will be like following:

db.collection.aggregate({
    $unwind: "$pay_info"
}, {
    $match: {
    "pay_info.pay_type": 1
    }
}, {
    $group: {
    _id: "$_id",
    "pay_info": {
        $push: "$pay_info"
    },
    "order_id": {
        $first: "$order_id"
    },
    "order_pay_time": {
        $first: "$order_pay_time"
    }
    }
})

Moreover if you want latest pay_info.pay_time then you can sort it by descending order with limit 1, some what like following:

db.collection.aggregate({
    $unwind: "$pay_info"
}, {
    $match: {
    "pay_info.pay_type": 1
    }
}, {
    $sort: {
    "pay_info.pay_time": -1
    }
}, {
    $limit: 1
}, {
    $group: {
    _id: "$_id",
    "pay_info": {
        $push: "$pay_info"
    },
    "order_id": {
        $first: "$order_id"
    },
    "order_pay_time": {
        $first: "$order_pay_time"
    }
    }
})

Edit

Also you can use $redact to avoid $unwind like following:

db.collection.aggregate({

    $match: {
    "pay_info": {
        $elemMatch: {
            "pay_type": 1
        }
    }
    }
}, {
    $sort: {
    "pay_info.pay_time": -1
    }
}, {
    $limit: 1
}, {
    $redact: {
    $cond: {
        if: {
            $eq: [{
                "$ifNull": ["$pay_type", 1]
            }, 1]
        },
        then: "$$DESCEND",
        else: "$$PRUNE"
    }
    }
}).pretty()

Upvotes: 1

Related Questions