MongoDB do not return document if condition in lookup fails-CodePudding

I am having this mongo DB query which queries a collection called songs and for each song, returns the respective album associated:

db.songs.aggregate([{
    $lookup: {
            from: "albums",
            let: { album: '$album' },
            as: "album",
            pipeline: [{
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ['$albumId', '$$album._id'] },
                            { $eq: ['$status', 'Draft'] },
                        ]
                    }
                }
            }]
        }
}])

In the above query, my intention was to return a song only if the album was in Draft status, but in contrast, it returns all songs, and for the ones for which the album is not in Draft, it just returns an empty array inside the lookup. How can I not return the song document at all if the album is not in Draft?

Additionally, is it possible to flatten the results in the document? ie, merge all the fields of albums into the song document?

CodePudding user response：

Once you perform the $lookup you can filter out the documents with an empty array:

{ $match: { album: { $ne: [] } }}

Then there is an example in the MongoDB documentation for the $mergeObjects operator that is very similar to your case. Assuming that each song belongs to one album, put together your aggregation pipeline may look like this:

db.songs.aggregate([
{
    $lookup: {
            from: "albums",
            let: { album: '$album' },
            as: "album",
            pipeline: [{
                $match: {
                    $expr: {
                        $and: [
                            { $eq: ['$albumId', '$$album._id'] },
                            { $eq: ['$status', 'Draft'] },
                        ]
                    }
                }
            }]
        }
},
{   $match: { album: { $ne: [] } }},
{
    $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$album", 0 ] }, "$$ROOT" ] } }
},
{ $project: { album: 0 } }
])

CodePudding user response：

You may want to experiment going in the other direction: find albums in status = Draft then get the songs:

db.album.aggregate([
    {$match: {"status":"Draft"}}
    ,{$lookup: {from: "song",
        localField: "album", foreignField: "album",
                as: "songs"}}
    // songs is now an array of docs.  Run $map to turn that into an
    // array of just the song title, and overwrite it (think  x = x   1):
    ,{$addFields: {songs: {$map: {
                            input: "$songs",
                            in: "$$this.song"
    }} }}
]);

If you have a LOT of material in the song document, you can use the fancier $lookup to cut down the size of lookup array -- but you still need the $map to turn it into an array of strings.

db.album.aggregate([
    {$match: {"status":"Draft"}}
    ,{$lookup: {from: "song",
                let: { aid: "$album" },
                pipeline: [
                    {$match: {$expr: {$eq:["$album","$$aid"]}}},
                    {$project: {song:true}}
                ],
                as: "songs"}}

    ,{$addFields: {songs: {$map: {
                            input: "$songs",
                            in: "$$this.song"
    }} }}

]);