MogoDB subdocument filteration

Question

  {
        "_id" : ObjectId("5a4e43edb85ed11cd4dcba45"),
        "email" : "av@gmail.com",
        "username" : "alpesh",
        "subscriptions" : [ 
            {
                "sub_id" : "5a4df654b9799b79147f9361",
                "activation_date" : ISODate("2017-12-19T18:30:00.000Z"),
                "expiry_date" : ISODate("2018-01-19T18:30:00.000Z")
            }, 
            {
                "sub_id" : "5a4df654b9799b79147f9361",
                "activation_date" : ISODate("2018-01-19T18:30:00.000Z"),
                "expiry_date" : ISODate("2018-02-19T18:30:00.000Z")
            }, 
            {
                "sub_id" : "5a51a925ddc5003b68cc38b3",
                "activation_date" : ISODate("2018-02-19T18:30:00.000Z"),
                "expiry_date" : ISODate("2018-03-22T18:30:00.000Z")
            }
        ]
    }

i have tried this.. db.find({"subscriptions.sub_id" : "5a4df654b9799b79147f9361" }); it returns ..

{
    "_id" : ObjectId("5a4e43edb85ed11cd4dcba45"),
    "email" : "av@gmail.com",
    "username" : "alpesh",
    "subscriptions" : [ 
        {
            "sub_id" : "5a4df654b9799b79147f9361",
            "activation_date" : ISODate("2017-12-19T18:30:00.000Z"),
            "expiry_date" : ISODate("2018-01-19T18:30:00.000Z")
        }, 
        {
            "sub_id" : "5a4df654b9799b79147f9361",
            "activation_date" : ISODate("2018-01-19T18:30:00.000Z"),
            "expiry_date" : ISODate("2018-02-19T18:30:00.000Z")
        }, 
        {
            "sub_id" : "5a51a925ddc5003b68cc38b3",
            "activation_date" : ISODate("2018-02-19T18:30:00.000Z"),
            "expiry_date" : ISODate("2018-03-22T18:30:00.000Z")
        }
    ]
}

i have also tried $aggregate , $unwind , $filter , $projection and many ways but none of them returns as expected... i want all the matching subdocuments...like this....

   {
        "_id" : ObjectId("5a4e43edb85ed11cd4dcba45"),
        "email" : "av@gmail.com",
        "username" : "alpesh",
        "subscriptions" : [ 
            {
                "sub_id" : "5a4df654b9799b79147f9361",
                "activation_date" : ISODate("2017-12-19T18:30:00.000Z"),
                "expiry_date" : ISODate("2018-01-19T18:30:00.000Z")
            }, 
            {
                "sub_id" : "5a4df654b9799b79147f9361",
                "activation_date" : ISODate("2018-01-19T18:30:00.000Z"),
                "expiry_date" : ISODate("2018-02-19T18:30:00.000Z")
            }
        ]
    }

Subdocument is `subscriptions` and you are using `subscription.sub_id` ? — Arvind, Jan 07 '18 at 06:23
Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — s7vr, Jan 07 '18 at 08:20

score 3 · Answer 1 · edited Jan 08 '18 at 07:17

Two ways to achieve that. You can use $elemMatch as a projection operator but this will only bring you first matching subdocument:

db.collection.find(
    { "subscriptions.sub_id": "5a4df654b9799b79147f9361" },
    { "subscriptions" : { $elemMatch: { "sub_id": "5a4df654b9799b79147f9361" } } }
)

Second way is by using aggregation framework:

db.collection.aggregate([  
    { $unwind: "$subscriptions" },
    { $match: {"subscriptions.sub_id": "5a4df654b9799b79147f9361"} },
    {
        $group: {
            _id: "$_id",
            "email" : { $first: "$email" },
            "username" : { $first: "$username" },
            "subscriptions": { $push: "$subscriptions" }
        }
    }
])

Here the point is that you need to group back your unwinded subscriptions to get back original shape (after filtering). To retrieve accumulate subscriptions into array you can use $push operator and to get back properties from outer document you can move them from any matching document (by using $first or $last).

it is not confirmed that there will be only email and username out of subscription.. — AlpeshVasani, Jan 07 '18 at 08:27

score 3 · Accepted Answer · edited Jan 08 '18 at 07:13

db.collection('gyms').aggregate([
    {
        $match: {
            subscriptions: { 
                $elemMatch: { sub_id: "5a4df654b9799b79147f9361" }
            }
        }
    },
    {
        $redact: {
            $cond: {
                if: {
                    $or: [
                        { $eq: ["$sub_id", "5a4df654b9799b79147f9361" ] },
                        { $not: "$sub_id" }
                    ]
                },
                then: "$$DESCEND",
                else: "$$PRUNE"
            }
        }
    }
])

this is generating as expected within a single doucment without unnecessary sub documents.

{
    "email": "av@gmail.com",
    "username": "alpesh",
    "subscriptions": [
        {
            "sub_id": "5a4df654b9799b79147f9361",
            "activation_date": "2017-12-19T18:30:00.000Z",
            "expiry_date": "2018-01-19T18:30:00.000Z"
        },
        {
            "sub_id": "5a4df654b9799b79147f9361",
            "activation_date": "2018-01-19T18:30:00.000Z",
            "expiry_date": "2018-02-19T18:30:00.000Z"
        }
    ]
}

MogoDB subdocument filteration

2 Answers2