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 — s7vr
– s7vr, Commented Mar 4, 2017 at 10:11

radhakrishnan · Accepted Answer · 2017-03-04 10:23:52Z

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 4, 2017 at 10:23

radhakrishnan

1,4791 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user6586661 Over a year ago

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.

radhakrishnan Over a year ago

For me it worked with your data, $gte 0 and $lte 666666666,

radhakrishnan Over a year ago

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

user6586661 Over a year ago

Ahh, yes! I made a dumb mistake. Thank you, it works!

Prasanna Ab · Accepted Answer · 2017-03-04 18:25:38Z

0

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

answered Mar 4, 2017 at 18:25

Prasanna Ab

1162 silver badges6 bronze badges

Collectives™ on Stack Overflow

MongoDB get only matching elements from nested array

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related