How to match multiple subdocuments in MongoDB?

Question

Assuming that I have the following data in my books collection:

[
    {
        name: "Animal Farm",
        readers: [
            {
                name: "Johny"
            },
            {
                name: "Lisa"
            }
        ],
        likes: [
            {
                name: "Johny"
            }
        ]
    },

    {
        name: "1984",
        readers: [
            {
                name: "Fred"
            },
            {
                name: "Johny"
            },
            {
                name: "Johny",
                type: "bot"
            }
        ],
        likes: [
            {
                name: "Fred"
            }
        ]
    }
]

How do I retrieve all readers and likes that match name "Johny", with end result something like this:

[
    {
        name: "Animal Farm",
        readers: [
            {
                name: "Johny"
            }
        ],
        likes: [
            {
                name: "Johny"
            }
        ]
    },

    {
        name: "1984",
        readers: [
            {
                name: "Johny"
            },
            {
                name: "Johny",
                type: "bot"
            }
        ],
        likes: []
    }
]

A following query is not possible:

db.books.find(
    { $or: [{ "readers.name": "Johny" }, { "likes.name": "Johny" }] },
    { name: 1, "readers.$": 1, "likes.$": 1 })

MongoDB complains with the following error: Cannot specify more than one positional array element per query (currently unsupported).

I have tried to use aggregation framework but did not succeed. So is this possible with MongoDB or do I have to run two queries to retrieve needed results?

Answer 1

As Sammaye has pointed already, specifying more than one positional array element is currently not supported.

However, you can use $elemMatch projection operator to get the results you want. $elemMatch projection operator limits the contents of the array to contain elements that matche the $elemMatch condition:

db.books.find(
    { $or: [{ "readers.name": "Johny" }, { "likes.name": "Johny" }] }, 
    { 
        readers : { $elemMatch : { name : "Johny" }}, 
        likes : { $elemMatch : { name : "Johny" }}
    }
);

Edit

Altough MongoDB doesn't have a built in operator to do what you want, using existing operators, you can achieve what you want. But, embrace yourself, this is going to be a long one:

db.books.aggregate([
    // find only documents that have correct "name"
    { $match: { $or: [{ "readers.name": "Johny" }, { "likes.name": "Johny" }]}},
    // unwind the documents so we can push them to a array
    { $unwind: '$likes' },
    // do a group to conditionally push the values into the array
    { $group : { 
        _id : '$_id', 
        likes : { 
            $push : { 
                $cond : [
                    { $eq : ["$likes.name", "Johny"]}, 
                    "$likes", 
                    null
                ]
            }
        },
        readers : { $first : "$readers" }, 
        name : { $first : "$name" }
    }},
    // the process is repeated for the readers array
    { $unwind: '$readers' },
    { $group : { 
        _id : '$_id', 
        readers : { 
            $push : { 
                $cond : [
                    { $eq : ["$readers.name", "Johny"]}, 
                    "$readers", 
                    null
                ]
            }
        },
        likes : { $first : "$likes" }, 
        name : { $first : "$name" }
    }},
    // final step: remove the null values from the arrays
    { $project : {
        name : 1,
        readers : { $setDifference : [ "$readers", [null] ] },
        likes : { $setDifference : [ "$likes", [null] ] },
    }}
]);

As you can see, you can do a "conditional" $push by using $cond operator inside the $push. But after the group stage, your array will contain null values. You have to filter them out by using setDifference.

Also note that you need to do unwind/group stages for each array you're building, otherwise a double unwind will duplicate the documents and you will end up with duplicate values in your arrays.

Answer 2

Following on from @ChristianP's Answer:

db.books.aggregate(

    // So we don't have to random do this to docs we don't need to
    {$match: { $or: [{ "readers.name": "Johny" }, { "likes.name": "Johny" }] }},

    {$unwind: '$readers'},
    {$match: { "readers.name": "Johny" }},

    {$unwind: '$likes'},
    {$match: { "likes.name": "Johny" }},

    {$group: {_id: '$_id', likes: {$push: '$likes'}, readers: {$push: '$readers'}}}
)

Something like that should be able to do what you want, the functionality to do this in query was shunned in favour of doing it this way.