CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Amrut Gaikwad
Amrut Gaikwad

Reputation: 552

How to use $slice a $filter result in MongoDB?

I have a item collection with the following format:

{
    "_id": 123,
    "items": [{
        "name": "item1",
        "status" : "inactive",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    },
    {
        "name": "item2",
        "status" : "active",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    },
    {
        "name": "item3",
        "status" : "active",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    },
    {
        "name": "item4",
        "status" : "inactive",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    },
    {
        "name": "item5",
        "status" : "active",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    }
    ]
}

I want to query on status field of items such that the object with status as 'active' is only returned in the array and also skip last 1 and limit 2 are returned in the query.

At present I am using $filter(aggregation) for this operation, by using following query it return only last record:

db.item.aggregate([
    { "$match": { "items.status": "active" } }, 
    { "$project": { 
        "items": { 
            "$slice": [ 
                { "$filter": { 
                    "input": "$items",
                    "as": "item", 
                    "cond": { "$eq": [ "$$item.status", "active" ] } 
                }}, 
                -1,2 
            ] 
        } 
    }}
])

Output should be:

{
    "_id": 123,
    "items": [
{
        "name": "item2",
        "status" : "active",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    },
    {
        "name": "item3",
        "status" : "active",
        "created" : ISODate("2018-02-14T10:39:28.321Z")
    }]
}

Please help me to achieve this result.

Upvotes: 1

Views: 1263

Answers (1)

dnickless
dnickless

Reputation: 10918

One way of doing it would like this (there's no need for the $match stage depending on the structure of your data and your indexing setup you might still want to keep it, though, for performance reasons):

 db.item.aggregate([
    { "$project": { 
        "items": { 
            "$slice": [ 
                { "$filter": { 
                    "input": "$items",
                    "as": "item", 
                    "cond": { "$eq": [ "$$item.status", "active" ] } 
                }}, 
                -3,2 
            ] 
        } 
    }}
])

I would think it's probably better, though, to use the following query:

db.items.db.aggregate([
{
    $project: {
        "items": {
            "$filter": { 
                "input": "$items",
                "as": "item", 
                "cond": { "$eq": [ "$$item.status", "active" ] } 
            }
        } 
    }
}, {
    $project: {
        "items": {
            $slice: [
                {
                    $slice: [
                        "$items",
                        {
                            $subtract: [ { $size: "$items" }, 1 ] // length of items array minus one
                        } 
                    ]
                }, 2 // max two elements
            ]
        }
    }
}])

as this one will first get rid of the last element and then limit the output to two items which is probably more what you want in a situation with less than 3 "active" elements.

Upvotes: 1

Related Questions