CODEWITHSUNDEEP
node.jsmongodbaggregation-framework
E.ss
E.ss

Reputation: 33

Transform a document with two nested documents using the aggregation framework

I need to transform this document using the aggregation framework

{
  title: 'Sample title',
  options: [
    { text: "yes", id: 0 },
    { text: "no", id: 1 }
  ],
  votes: [
    { option_id: 1, user_id: 1 },
    { option_id: 1, user_id: 2 },
    { option_id: 1, user_id: 3 }
  ]
}

Into this object

{ 
 title: 'Sample title',
 result: [{ _id: 1, text: 'no', votes: 3}, { _id: 0, text: 'yes', votes: 0 }]
}

What I have tried: 

[
            { $match: { _id: poll_id } },
            { $unwind: '$votes' },
            {
                $replaceRoot: {
                    newRoot: { $mergeObjects: ['$votes', '$$ROOT'] }
                }
            },
            {
                $group: {
                    _id: '$option_id',
                    title: { $first: '$title' },
                    votes: { $sum: 1 }
                }
            }
        ]

Which produces this result: 

[{ _id: 1, title: 'Sample Title', votes: 3}]

If the option does not have votes, it is excluded from the final result. I don't know how to include the text of the option either. I have already read the mongodb reference, but I could not find anything.

Upvotes: 2

Views: 27

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

You can use the below pipeline that utilizes $map and $filter:

db.collection.aggregate([
    {
        "$addFields": {
            "result": {
                "$map": {
                    "input": "$options",
                    "as": "option",
                    "in": {
                        "_id": "$$option.id",
                        "text": "$$option.text",
                        "votes": {
                            "$size": {
                                "$filter": {
                                    "input": "$votes",
                                    "as": "vote",
                                    "cond": {
                                        "$eq": [
                                            "$$vote.option_id",
                                            "$$option.id"
                                        ]
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        "$project": {
             title: "$title",
             "result": {
                $filter: {
                   input: "$result",
                   as: "option",
                   cond: {
                      $gt: ["$$option.votes", 0]
                   }
                }
           }
       }
    }
])

Upvotes: 2

Related Questions