mongodb query and return specific elements in array of documents, return some result even if null

Question

suppose i have these documents in a collection:

 {_id: 1,fruit: apple,attributes: [{color: red,size: small,qty: 5},{color: green,size: small, qty 3}]},
 {_id: 2,fruit: mango,attributes: [{color: red,size: big, qty: 6},{color: green,size: small, qty: 7},{color: gold,size: medium, qty: 5},{color: yellow,size: small, qty: 20}]},
 {_id: 3,fruit: pineapple },
 {_id: 4,fruit: orange,attributes: [{color: orange,size: small,qty: 2}]}

all documents will always have the field "fruit"
some documents may not have the "attributes" array
for documents which have elements in attributes array, it can be between 1 and 20 elements

i need to return this based on attributes.qty between 4 and 8

always show id and fruit even if attributes field is missing or the qty criteria is not matched
show only a max of 2 elements in the attributes array which match search criteria, sorted in descending order of qty

{_id: 1,fruit: apple,attributes: [{color: red,size: small,qty: 5}]},

{_id: 2,fruit: mango,attributes: [{color: green,size: small, qty: 7}, {color: red,size: big, qty: 6}]},

{_id: 3,fruit: pineapple },

{_id: 4,fruit: orange }

this is the query i am using:

db.test.aggregate([
{"$unwind":"$attributes"},
{"$match":{"attributes.qty": {$gt:4, $lt:8}}},
{"$group":{"_id":"$_id","fruit":{$first:"$fruit"},"attributes":{$addToSet: "$attributes"}}}
])

I am getting the following result:

 {_id: 1,fruit: apple,attributes: [{color: red,size: small,qty: 5}]},
 {_id: 2,fruit: mango,attributes: [{color: red,size: big, qty: 6},{color: green,size: small, qty: 7},{color: gold,size: medium, qty: 5}]}

This does not have pineapple and orange records; values in attributes for mango are not sorted and not limited to 2

Please help

UPDATE

Tried using $redact. still not close to getting desired output. on using $and, there is no output

db.test.aggregate([
{$match:{"site":{$exists:true}}},
        { "$redact": {
        "$cond": {
            "if": {
                $and:[ 
                {"$gt": ["$qty",4]},
                {"$lt": ["$qty,8]}
                ]
            },
            "then": "$$DESCEND",
            "else": "$$PRUNE"
        }
    }}
])

different problem. elemMatch returns one match. I need more than one + limit + default response — user3780084

thegreenogre thegreenogre · Accepted Answer · 2015-05-29T11:45:40

This query should give you result close to what you want.

db.test.aggregate([
    { 
        "$redact": {
            "$cond": {
                "if": { $or : [ {$and:[ {"$gt": ["$qty",4]},{"$lt": ["$qty",8]}]}, { $not : "$qty" }]},
                "then": "$$DESCEND",
                "else": "$$PRUNE"
            }
        }
    },
    {
        '$project': {
            'fruit': 1,
            'attributes' : {
                "$cond" : [{'$and': [ {$ne : ["$attributes", []]},{"$ifNull": ["$attributes", false] }]},"$attributes", [{ 'qty':'test'}] ]
            }
        }
    },
    {'$unwind': '$attributes'},
    {'$sort': {'attributes.qty': -1}},
    {'$group': {'_id': '$_id' , 'fruit' : {'$first': '$fruit'},'attributes': {'$push' : '$attributes'}}},
    {
        "$redact": {
            "$cond": {
                "if": { '$ne': ['$qty','test']},
                "then": "$$DESCEND",
                "else": "$$PRUNE"
            }
        }
    },
])

Note that {$not:"$qty"} is necessary, because it will scan the top document as well, and if $redact does not find a qty field on the top level this will return false that might strip the whole document which we don't want.

Result:

{
    "result" : [ 
        {
            "_id" : ObjectId("5568335b52cdca6ba1c290f0"),
            "fruit" : "apple",
            "attributes" : [ 
                {
                    "color" : "red",
                    "size" : "small",
                    "qty" : 5
                }
            ]
        }, 
        {
            "_id" : ObjectId("5568335b52cdca6ba1c290f1"),
            "fruit" : "mango",
            "attributes" : [ 
                {
                    "color" : "green",
                    "size" : "small",
                    "qty" : 7
                }, 
                {
                    "color" : "red",
                    "size" : "big",
                    "qty" : 6
                }, 
                {
                    "color" : "gold",
                    "size" : "medium",
                    "qty" : 5
                }
            ]
        }, 
        {
            "_id" : ObjectId("5568335b52cdca6ba1c290f3"),
            "fruit" : "orange",
            "attributes" : []
        }, 
        {
            "_id" : ObjectId("5568335b52cdca6ba1c290f2"),
            "fruit" : "pineapple",
            "attributes" : []
        }
    ],
    "ok" : 1
}

Unfortunately (as at MongoDB 3.0) there is no equivalent of $slice for the aggregation pipeline. The relevant feature request to upvote/watch in the MongoDB issue tracker is SERVER-6074: Allow $slice operator in $project.

So you will need a workaround to get max 2 documents.

Manipulation of the results in your application code.
Implementing the aggregation using Map/Reduce

mongodb query and return specific elements in array of documents, return some result even if null

1 Answers