Transform a document with two nested documents using the aggregation framework

Question

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.

Tom Slabbaert · Accepted Answer · 2020-02-24 19:08:29Z

2

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]
                   }
                }
           }
       }
    }
])

edited Feb 24, 2020 at 19:08

answered Feb 24, 2020 at 19:03

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

E.ss Over a year ago

Your solution worked. I just had to remove the $filter in the $project.

Tom Slabbaert Over a year ago

you said you wanted to remove objects with 0 votes thats why i added it, otherwise you can do it with one $project phase.

E.ss Over a year ago

Yes, that was my fault. I didn't notice it.

Collectives™ on Stack Overflow

Transform a document with two nested documents using the aggregation framework

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related