CODEWITHSUNDEEP
mongodbmongodb-query
BeNdErR
BeNdErR

Reputation: 17927

mongodb - Filter object and child array in same query

I have the following data structure saved in a mongodb collectionn named images

[
    {
        folder: 'cats',
        files: [
            {filename: 'redCat.jpg', _id: 1},
            {filename: 'blueCat.jpg', _id: 2},
            {filename: 'yellowCat.jpg', _id: 3},
        ]
    },{
        folder: 'dogs',
        files: [
            {filename: 'redDog.jpg', _id: 4},
            {filename: 'blueDog.jpg', _id: 5},
            {filename: 'yellowDog.jpg', _id: 6},
        ]
    },{
       ...
    }
]

I need to extract the id of a particular image, given the folder and the filename. I'm able to get all the images in a particular folder

db.images.find({folder:"cats"}, {files:1})

but I don't know how to filter out the files array in the same query, in order to get the blueCat.jpg image id. If possible I'd avoid two sequential queries.

I tried the following query

db.images.find({folder:"cats", "files.filename":"blueCat.jpg"})

but what I get is, again, the whole file list in the cats folder. What I'm triyng to get is something like:

{filename: 'blueCat.jpg', _id: 2}

Any hint? Thanks in advance

Upvotes: 1

Views: 13373

Answers (2)

user4768611
user4768611

Reputation:

Try this-

db.images.find({"folder":"cats","files.filename":"blueCat.jpg"}, {'files.$":1})

give you-

{ "_id" : ObjectId("576a92b47f0d626b7a74c325"), "files" : { "filename" : "blueCat.jpg", "_id" : 2 } }

And if you want to fetch complete list-

db.images.find({"folder":"cats","files.filename":"blueCat.jpg"}, {'files":1})

will result you-

{ "_id" : ObjectId("576a92b47f0d626b7a74c325"),
 "files" :
 { "filename" : "blueCat.jpg", "_id" : 2 }
 {"filename" : "xxxCat.jpg", "_id" : N }
 {"filename" : "yyyCat.jpg", "_id" : N }
 }

Upvotes: 2

robjwilkins
robjwilkins

Reputation: 5652

You can do this using the aggregation pipeline, and unwinding the files array, then query (match) the filename you are looking for.

For example:

db.images.aggregate([{$unwind:"$files"},{$match:{"files.filename":"blueCat.jpg"}}])

will give you:

{ "_id" : ObjectId("576a92b47f0d626b7a74c325"), "folder" : "cats", "files" : { "filename" : "blueCat.jpg", "_id" : 2 } }

You can further refine the output document by using project. Eg:

db.images.aggregate([{$unwind:"$files"},{$match:{"files.filename":"blueCat.jpg"}},{$project:{"files.filename":1, "files._id":1}}])

will return:

{ "_id" : ObjectId("576a92b47f0d626b7a74c325"), "files" : { "filename" : "blueCat.jpg", "_id" : 2 } }

Upvotes: 0

Related Questions