Projecting flat values from nested objects

Question

I am writing a small aggregation in mongoose to filter values from db and return them in order of frequency they occur. For example: Say multiple documents have partners array as a field, which is an array of objects, each having two values "partner_id" and "passed_tests". I want to return all the unique partners from all the documents sorted according to their frequency in the collection in decreasing order.

Here is a sample document:

{
    "location": "eindhoven",
    "partners": [
        {
            "partner_id": 3,
            "passed_tests": true
        },
        {
            "partner_id": 2,
            "passed_tests": false
        }
    ],
    "_id": "3136323031333066306d4438",
    "uid": "d95f2e446c052514c097e6c925408774",
    "__v": 0,
    "is_approved": true
}

My code is as follows:

function returnAll(callback) {
    TestService.aggregate([
        {
            $match: { "is_approved": true }
        },
        {
            $unwind: "$partners"
        }, {
            $group: {
                "_id": {
                    partner: { $objectToArray: "$partners" },
                    partner_id: { $arrayElemAt: ["$partner", 0] }
                },
                "count": { "$sum": 1 }
            }
        },
        {
            $sort: { "count": -1 }
        },
        {
            $project: {
                "partner_values": {
                    $map: {
                        input: "$_id.partner",
                        as: "el",
                        in: {
                            $cond: {
                                if: {
                                    $or: [{ $eq: ["$$el.v", true] },
                                    { $eq: ["$$el.v", false] }]
                                }, then: {
                                    "passed_tests": "$$el.v"
                                }, else: {
                                    "id": "$$el.v"
                                }
                            }
                        }
                    }
                },
                "count": "$count,
                "_id": 0
            }
        },
    ], function (error, data) {
        if (error) {
            logger.error(error);
            callback(null);
        } else {
            callback(data);
        }
    });
}

Which returns this JSON to my node app:

"data": [
        {
            "partner_values": [
                {
                    "id": 2
                },
                {
                    "passed_tests": false
                }
            ],
            "count": 3
        },
        {
            "partner_values": [
                {
                    "id": 6
                },
                {
                    "passed_tests": true
                }
            ],
            "count": 1
        },
        {
            "partner_values": [
                {
                    "id": 3
                },
                {
                    "passed_tests": true
                }
            ],
            "count": 1
        },
        {
            "partner_values": [
                {
                    "id": 1
                },
                {
                    "passed_tests": true
                }
            ],
            "count": 1
        }
    ]

If I don't use the projection pipeline in my aggregation, I get this:

"data": [
        {
            "_id": {
                "partner": [
                    {
                        "k": "partner_id",
                        "v": 2
                    },
                    {
                        "k": "passed_tests",
                        "v": false
                    }
                ],
                "partner_id": null
            },
            "count": 3
        },
        {
            "_id": {
                "partner": [
                    {
                        "k": "partner_id",
                        "v": 6
                    },
                    {
                        "k": "passed_tests",
                        "v": true
                    }
                ],
                "partner_id": null
            },
            "count": 1
        },
        {
            "_id": {
                "partner": [
                    {
                        "k": "partner_id",
                        "v": 3
                    },
                    {
                        "k": "passed_tests",
                        "v": true
                    }
                ],
                "partner_id": null
            },
            "count": 1
        },
        {
            "_id": {
                "partner": [
                    {
                        "k": "partner_id",
                        "v": 1
                    },
                    {
                        "k": "passed_tests",
                        "v": true
                    }
                ],
                "partner_id": null
            },
            "count": 1
        }
    ]

which is quite understandable as I am introducing "_id" and other extra fields to look into the array and find the value. However, the output I want is:

"data": {
            "partners": [{
                "id": 2,
                "passed_tests": false,
                "count": 3
            }, {
                "id": 6,
                "passed_tests": false,
                "count": 1
            }, {
                "id": 3,
                "passed_tests": false,
                "count": 1
            }, {
                "id": 1,
                "passed_tests": false,
                "count": 1
            }]
        }

Can I get some help on this, please? Thanks.

Answer 1

You can use below aggregation query.

TestService.aggregate([
  {"$match":{"is_approved":true}},
  {"$unwind":"$partners"},
  {"$group":{
    "_id":{"partner_id":"$partners.partner_id","passed_tests":"$partners.passed_tests"},
    "count":{"$sum":1}
  }},
  {"$sort":{"count":-1}},
  {"$group":{
    "_id":null,
    "partners":{"$push":{"id":"$_id.partner_id","passed_tests":"$_id.passed_tests","count":"$count"}}
  }},
  {"$project":{"partners":1}}
])