When MongoDB aggregate can't find any result,

2020-08-02 20:07发布

问题:

I want to get the list of my single project's bids within the project's aggregate.

but when I run my Mongodb aggregate, if it can't find any result for bids of the project, it returns this result:

[ { _id: 5b69f6afa1ad1827cc9e1dc6,
    projectID: 100029,
    bidsArray: [ { freelanceArray: {} } ]
} ]

But I want to return empty bidsArray if it can't find related bids, (something like this):

[ { _id: 5b69f6afa1ad1827cc9e1dc6,
    projectID: 100029,
    bidsArray: []
} ]

Here is my aggregate:

[
        {
            $match: {
                projectID: projectID
            }
        },
        {
            $lookup: {
                from: "bids",
                localField: "projectID",
                foreignField: "projectID",
                as: "bidsArray"
            }
        },
        {
            $unwind: {
                path: "$bidsArray",
                preserveNullAndEmptyArrays: true
            }
        },
        {
            $lookup: {
                from: "users",
                localField: "bidsArray.freelanceID",
                foreignField: "userID",
                as: "freelanceArray"
            }
        },
        {
            $unwind: {
                path: "$freelanceArray",
                preserveNullAndEmptyArrays: true
            }
        },
        {
            $group: {
                _id: "$_id",
                projectID: { $first: "$projectID" },
                bidsArray: {
                    $addToSet: {
                        bidID: "$bidsArray.bidID",
                        daysToDone: "$bidsArray.daysToDone",
                        freelanceArray: {
                            userID: "$freelanceArray.userID",
                            username: "$freelanceArray.username",
                            publicName: "$freelanceArray.publicName",
                        }
                    }
                }
            }
        },
        {
            $project: {
                projectID: 1,
                bidsArray: {
                    bidID: 1,
                    daysToDone: 1,
                    freelanceArray: {
                        userID: 1,
                        username: 1,
                        publicName: 1,
                    }
                }
            }
        }
    ]

回答1:

In MongoDB 3.6, you can use the variable $$REMOVE in aggregation expressions to conditionally suppress a field.

Remove the last $project stage and update the freelanceArray expression inside $group as below. The $cond expression basically checks freelanceArray value when present ( not null value) output the fields else remove the freelanceArray.

"freelanceArray":{
  "$cond":[
    {"$gt":["$freelanceArray",0]},
    {"userID":"$freelanceArray.userID","username":"$freelanceArray.username","publicName":"$freelanceArray.publicName"},
    "$$REMOVE"]
}