mongodb - Filter object and child array in same query

Question

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

Possible duplicate of Retrieve only the queried element in an object array in MongoDB collection — Sede
– Sede, Commented Jun 22, 2016 at 12:28
db.images.find({"folder":"cats","files.filename":"blueCat.jpg"}, {'files.$":1}) might work — undefined_variable
– undefined_variable, Commented Jun 22, 2016 at 12:31

user4768611 · Accepted Answer · 2016-06-23 08:12:00Z

2

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 }
 }

answered Jun 23, 2016 at 8:12

user4768611

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

Comments

robjwilkins · Accepted Answer · 2016-06-22 13:59:27Z

0

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 } }

edited Jun 22, 2016 at 13:59

answered Jun 22, 2016 at 13:34

robjwilkins

5,7127 gold badges48 silver badges64 bronze badges

2 Comments

BeNdErR Over a year ago

What if both cats and dogs folder contain an image with the same name?

robjwilkins Over a year ago

You would get two documents returned.

Collectives™ on Stack Overflow

mongodb - Filter object and child array in same query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related