MongoDB get only matching elements from nested array

Question

I have a collection like this:

{
_id : 123,
username : "xy",
comments : [
    {
        text : "hi",
        postdate : 123456789
    },
    {
        text : "hi1",
        postdate : 555555555
    },
    {
        text : "hi2",
        postdate : 666666666
    },
    {
        text : "hi3",
        postdate : 987654321
    }

]}

Now I want only the comments that have postdate 555555555 or higher and 987654321 or lower. I have this query, but it doesn't work:

db.post.aggregate([
{$match : {$and : [ 
{"_id": ObjectId("123")},
{"comments.posttime" : {$lte : 987654321}},
{"comments.posttime" : {$gte : 555555555}}
]}}
,{$unwind: "$comments"}]).pretty();

But when I try this it gets me all of the array elements. How should this be done?

Thank you!

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — s7vr, Mar 04 '17 at 10:11

score 2 · Accepted Answer · answered Mar 04 '17 at 10:23

2

Use $redact to Restricts the contents of the document,

                db.an.aggregate([{
        $redact: {
            "$cond": [{
                $and: [{
                    "$gte": [{
                            "$ifNull": ["$postdate", 555555555]
                        },
                        555555555
                    ]
                }, {
                    "$lte": [{
                            "$ifNull": ["$postdate", 987654321]
                        },
                        987654321
                    ]
                }]
            }, "$$DESCEND", "$$PRUNE"]
        }
    }]).pretty()

answered Mar 04 '17 at 10:23

radhakrishnan

1,399
1
14
20

thank you for the answer! But it doesn't work. If the $gte is 555555555 I don't get any element. If I set this to 0 and the $lte from 987654321 to 666666666 I get the full stack. – user6586661 Mar 04 '17 at 10:54
For me it worked with your data, $gte 0 and $lte 666666666, – radhakrishnan Mar 04 '17 at 11:10
db.an.aggregate([{ $redact: { "$cond": [{ $and: [{ "$gte": [{"$ifNull":["$postdate", 0] }, 0 ] }, { "$lte": [{"$ifNull":["$postdate",666666666]}, 666666666 ] }] }, "$$DESCEND", "$$PRUNE"] } }]).pretty() – radhakrishnan Mar 04 '17 at 11:12
Ahh, yes! I made a dumb mistake. Thank you, it works! – user6586661 Mar 04 '17 at 13:11

score 0 · Answer 2 · answered Mar 04 '17 at 18:25

you have to unwind the comments first and then do the match. so that comments array will be flattened and match condition can filter it properly.

[{$unwind: "$comments"},{$match : {$and : [ 
{"_id": ObjectId("123")},
{"comments.posttime" : {$lte : 987654321}},
{"comments.posttime" : {$gte : 555555555}}
]}}]

this will give one row for each comment, if you want the matching comments inside the array, use aggregate on _id and $push the comments

MongoDB get only matching elements from nested array

2 Answers2