mongodb nested array matching and sorting

Question

My json file looks like this:

{
    "_id": ObjectId("57208fa10f80d14e9472942f"),
    "title": "How to stop hair loss?",
    "postdatetime": "Wed Apr 27 2016 15:38:33 GMT+0530 (IST)",
    "status": "Active",
    "answer": [{
        "answerid": ObjectId("5719b83ffa86ca75d25b600e"),
        "text": "l eat protien",
        "imageurl": "http://leavesugar.jpg",
        "videourl": "http://leavesugar.mp4",
        "author": {
            "userid": ObjectId("5719b83ffa86ca75d25b600c"),
            "name": "chetan kumar",
            "imageurl": "http://chetan.jpg",
            "followers": 150
        },
        "postdatetime": "Wed Apr 27 2016 11:55:45 GMT+0530 (IST)",
        "status": "Active",
        "comment": [{
            "commentid": ObjectId("5719b83ffa86ca75d25b600c"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600e"),
            "posttext": "Most important is workout",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600c"),
                "name": "raju",
                "imageurl": "http://raju.jpg",
            },
            "postdatetime": "Wed Apr 27 2016 12:17:59 GMT+0530 (IST)"
        }]
    }, {
        "answerid": ObjectId("5719b83ffa86ca75d25b600f"),
        "text": "WORKOUT 1 TO 2 HRS DAILY",
        "imageurl": "http://workout.jpg",
        "videourl": "http://workout.mp4",
        "author": {
            "userid": ObjectId("5719b83ffa86ca75d25b600a"),
            "name": "Ram kumar",
            "imageurl": "http://ram.jpg",
        },
        "postdatetime": "Wed Apr 27 2016 11:56:26 GMT+0530 (IST)",
        "status": "Active",
        "comment": [{
            "commentid": ObjectId("5719b83ffa86ca75d25b600e"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600f"),
            "posttext": "EAT MORE PROTIEN",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600e"),
                "name": "raju",
                "imageurl": "http://raju.jpg",
            },
            "postdatetime": "Wed Apr 27 2016 12:49:24 GMT+0530 (IST)"
        }, {
            "commentid": ObjectId("5719b83ffa86ca75d25b600f"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600f"),
            "posttext": "EAT PROTIEN AND WORKOUT DAILY",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600f"),
                "name": "RAMU",
                "imageurl": "http://RAMU.jpg",
            },
            "postdatetime": "Wed Apr 27 2016 12:50:07 GMT+0530 (IST)"
        }]
    }]
} {
    "_id": ObjectId("57204c194694965a19bac484"),
    "title": "How to loose weight?",
    "postdatetime": "Wed Apr 27 2016 10:50:25 GMT+0530 (IST)",
    "status": "Active",
    "answer": [{
        "answerid": ObjectId("5719b83ffa86ca75d25b600b"),
        "text": "leave sugar and eat protien",
        "imageurl": "http://leavesugar.jpg",
        "videourl": "http://leavesugar.mp4",
        "author": {
            "userid": ObjectId("5719b83ffa86ca75d25b600c"),
            "name": "chetan kumar",
            "imageurl": "http://chetan.jpg",
            "followers": 250
        },
        "postdatetime": "Wed Apr 27 2016 11:55:45 GMT+0530 (IST)",
        "status": "InActive",
        "comment": [{
            "commentid": ObjectId("5719b83ffa86ca75d25b600c"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600b"),
            "posttext": "Most important is workout",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600c"),
                "name": "raju",
                "imageurl": "http://raju.jpg",
            },
            "postdatetime": "Wed Apr 27 2016 12:17:59 GMT+0530 (IST)"
        }]
    }, {
        "answerid": ObjectId("5719b83ffa86ca75d25b600a"),
        "text": "WORKOUT 1 TO 2 HRS DAILY",
        "imageurl": "http://workout.jpg",
        "videourl": "http://workout.mp4",
        "author": {
            "userid": ObjectId("5719b83ffa86ca75d25b600a"),
            "name": "Ram kumar",
            "imageurl": "http://ram.jpg",
        },
        "postdatetime": "Wed Apr 27 2016 11:56:26 GMT+0530 (IST)",
        "status": "Active",
        "comment": [{
            "commentid": ObjectId("5719b83ffa86ca75d25b600e"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600a"),
            "posttext": "EAT MORE PROTIEN",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600e"),
                "name": "raju",
                "imageurl": "http://raju.jpg"
            },
            "postdatetime": "Wed Apr 27 2016 12:49:24 GMT+0530 (IST)"
        }, {
            "commentid": ObjectId("5719b83ffa86ca75d25b600f"),
            "parentid": ObjectId("5719b83ffa86ca75d25b600a"),
            "posttext": "EAT PROTIEN AND WORKOUT DAILY",
            "author": {
                "userid": ObjectId("5719b83ffa86ca75d25b600f"),
                "name": "RAMU",
                "imageurl": "http://RAMU.jpg"
            },
            "postdatetime": "Wed Apr 27 2016 12:50:07 GMT+0530 (IST)"
        }]
    }, {
        "answerid": ObjectId("5719b83ffa86ca75d25b600d"),
        "text": "WORKOUT 1 TO 2 HRS DAILY",
        "imageurl": "http://workout.jpg",
        "videourl": "http://workout.mp4",
        "author": {
            "userid": ObjectId("5719b83ffa86ca75d25b600a"),
            "name": "Ram kumar",
            "imageurl": "http://ram.jpg"
        },
        "postdatetime": "Thu Apr 28 2016 10:35:38 GMT+0530 (IST)",
        "status": "Active",
        "comment": []
    }]
}

I want to find out "status":"Active", "answer.status":"Active" and short the answer according to their maximum no. of comment

I try this to find out only "status" and "answer.status" is active but it show the all result whose "answer.status" is Active or InActive

How to solve this issue please help me.

I used this aggregate to find out all "status" and "answer.status" :"Active"

db.QuestionAnswer.aggregate([{
            "$match" : {
                $and : [{
                        "status" : "Active"
                    }, {
                        "answer.status" : "Active"
                    }
                ]
            }
        }
    ]).pretty()

You need to use aggregation. There's an easy aggregation query if you are using mongodb 3.2 — Sarath Nair
– Sarath Nair, Commented May 10, 2016 at 10:44
I use this db.QuestionAnswer.aggregate([{"$match":{$and:[{"status":"Active"},{"answer.status":"Active"}]}}]).pretty() — Chetan Kumar
– Chetan Kumar, Commented May 10, 2016 at 10:53
First i want only those data whose "status" : "Active" And "answer.status" :"Active" in above json file i used db.QuestionAnswer.aggregate([{"$match":{$and:[{"status":"Active"},{"answer.status":"Active"}]}}]).pretty() — Chetan Kumar
– Chetan Kumar, Commented May 10, 2016 at 11:36
Bt it show all data where "status":"Active" but doesn't show only those data whose "answer.status":"Active" it also so those file whose data "answer.status":"InActive".In above json file — Chetan Kumar
– Chetan Kumar, Commented May 10, 2016 at 11:39

profesor79 · Accepted Answer · 2016-05-10 12:45:25Z

to get only active answers please use query listed below:

db.aaa.aggregate([{
            $match : {
                status : "Active"
            }
        }, {
            $project : {
                _id : 1,
                title : 1,
                postdatetime : 1,
                status : 1,
                answer : {
                    $filter : {
                        input : "$answer",
                        as : "answe",
                        cond : {
                            $eq : ["$$answe.status", "Active"]
                        }
                    }
                }
            }
        }
    ])

output

{
    "_id" : ObjectId("57208fa10f80d14e9472942f"),
    "title" : "How to stop hair loss?",
    "postdatetime" : "Wed Apr 27 2016 15:38:33 GMT+0530 (IST)",
    "status" : "Active",
    "answer" : [ 
        {
            "answerid" : ObjectId("5719b83ffa86ca75d25b600e"),
            "text" : "l eat protien",
            "imageurl" : "http://leavesugar.jpg",
            "videourl" : "http://leavesugar.mp4",
            "author" : {
                "userid" : ObjectId("5719b83ffa86ca75d25b600c"),
                "name" : "chetan kumar",
                "imageurl" : "http://chetan.jpg",
                "followers" : 150
            },
            "postdatetime" : "Wed Apr 27 2016 11:55:45 GMT+0530 (IST)",
            "status" : "Active",
            "comment" : [ 
                {
                    "commentid" : ObjectId("5719b83ffa86ca75d25b600c"),
                    "parentid" : ObjectId("5719b83ffa86ca75d25b600e"),
                    "posttext" : "Most important is workout",
                    "author" : {
                        "userid" : ObjectId("5719b83ffa86ca75d25b600c"),
                        "name" : "raju",
                        "imageurl" : "http://raju.jpg"
                    },
                    "postdatetime" : "Wed Apr 27 2016 12:17:59 GMT+0530 (IST)"
                }
            ]
        }, 
        {
            "answerid" : ObjectId("5719b83ffa86ca75d25b600f"),
            "text" : "WORKOUT 1 TO 2 HRS DAILY",
            "imageurl" : "http://workout.jpg",
            "videourl" : "http://workout.mp4",
            "author" : {
                "userid" : ObjectId("5719b83ffa86ca75d25b600a"),
                "name" : "Ram kumar",
                "imageurl" : "http://ram.jpg"
            },
            "postdatetime" : "Wed Apr 27 2016 11:56:26 GMT+0530 (IST)",
            "status" : "Active",
            "comment" : [ 
                {
                    "commentid" : ObjectId("5719b83ffa86ca75d25b600e"),
                    "parentid" : ObjectId("5719b83ffa86ca75d25b600f"),
                    "posttext" : "EAT MORE PROTIEN",
                    "author" : {
                        "userid" : ObjectId("5719b83ffa86ca75d25b600e"),
                        "name" : "raju",
                        "imageurl" : "http://raju.jpg"
                    },
                    "postdatetime" : "Wed Apr 27 2016 12:49:24 GMT+0530 (IST)"
                }, 
                {
                    "commentid" : ObjectId("5719b83ffa86ca75d25b600f"),
                    "parentid" : ObjectId("5719b83ffa86ca75d25b600f"),
                    "posttext" : "EAT PROTIEN AND WORKOUT DAILY",
                    "author" : {
                        "userid" : ObjectId("5719b83ffa86ca75d25b600f"),
                        "name" : "RAMU",
                        "imageurl" : "http://RAMU.jpg"
                    },
                    "postdatetime" : "Wed Apr 27 2016 12:50:07 GMT+0530 (IST)"
                }
            ]
        }
    ]
}

Collectives™ on Stack Overflow

mongodb nested array matching and sorting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related