Nested Query Mongodb

Question

I have this beautiful Json , and I'm trying with the powerful mongodb query to get all comments with file_id 12....so this is what I would like have back [4,5,7,10,11,15].

I tried with this query but the file_id it's completely ignored by the engine:

db.collection.distinct("changes.comments",
        {"my_uuid":"bf48e757-1a65-4546-bf24-2bb001effddd",
         "changes":{$elemMatch:{file_id:12}} } 
)

Output:

{
    "_id" : ObjectId("5342bf796b03d7ffc834afcc"),
    "my_uuid" : "bf48e757-1a65-4546-bf24-2bb001effddd",
    "changes" : [ 
        {
            "file_id" : 12,
            "version" : 1,
            "comments" : [ 
                4, 
                5, 
                7
            ],
            "lastseen" : 1394640549
        }, 
        {
            "file_id" : 12,
            "version" : 2,
            "comments" : [ 
                10, 
                11, 
                15
            ],
            "lastseen" : 1394640511
        }, 
        {
            "file_id" : 234,
            "version" : 1,
            "comments" : [ 
                100, 
                110, 
                150
            ],
            "lastseen" : 1394640555
        }
    ]
}

Thanks in advance

Anand Jayabalan · Accepted Answer · 2014-04-08T14:13:12.137

You can use the aggregation framework to achieve what you what. Although the query looks complex for what you are trying to do, it's simple once you get a hang of it.

db.collection.aggregate([
    // Get only documents where "my_uuid" equals "bf48e757-1a65-4546-bf24-2bb001effddd"
    {"$match":{"my_uuid":"bf48e757-1a65-4546-bf24-2bb001effddd"}},
    // Unwind the "changes" array
    {"$unwind":"$changes"},
    // Get only elements of the "changes" array where "file_id" equals 12
    {"$match":{"changes.file_id":12}},
    // Unwind the "comments" array
    {"$unwind":"$changes.comments"},   
    // Group by _id and add the comments to array only if not already present
    {"$group":{_id:"$_id", comments:{$addToSet:"$changes.comments"}}},
    // Cleanup the output
    {"$project":{_id:0, comments:1}}
])

Output:

{
        "result" : [
                {
                        "comments" : [
                                4,
                                5,
                                7,
                                10,
                                11,
                                15
                        ]
                }
        ],
        "ok" : 1
}

EDIT: Including my_uuid in the results is fairly straight-forward. We just need to group by my_uuid instead of _id:

db.collection.aggregate([
    {"$match":{"my_uuid":"bf48e757-1a65-4546-bf24-2bb001effddd"}},
    {"$unwind":"$changes"},
    {"$match":{"changes.file_id":12}},
    {"$unwind":"$changes.comments"},   
    {"$group":{_id:"$my_uuid", comments:{$addToSet:"$changes.comments"}}},
    {"$project":{_id:0, my_uuid:"$_id", comments:1}}
])

can you provide me an example with my_uuid as unique and one not? — magemello, Apr 08 '14 at 13:59
since we are already matching by `my_uuid`, it probably makes sense to group by `my_uuid` instead of `_id`. i've updated my post with edited query. this would work irrespective of whether `my_uuid` is unique or non-unique — Anand Jayabalan, Apr 08 '14 at 14:17

score 0 · Answer 2 · edited May 23 '17 at 12:28

Currently there is no straight forward way of pulling out only the matching document from an array. The $elemMatch operator will only ensure that at least one of the documents within the array satisfies the condition provided by you. The query will however, always return the entire document. One way to achieve what you are looking for is -

db.sample4.aggregate({$unwind:"$changes"},{$match:{"changes.file_id":12}},{$project:{"changes.comments":1,"_id":0}});

These topics are covered here in stackoverflow, where map-reduce approach as well is listed to achieve this. If the requirement was to return the first matching document, the you could have projected using changes.comments.$:1 Eg. - db.sample4.find({"changes":{$elemMatch:{"file_id":12}} },{"changes.comments.$":1} )

Nested Query Mongodb

2 Answers2

Linked