MongoDB: how to assign index names to the elements of an array in the aggregation pipeline?

Question

Here is an array which I got after a long aggregation.

  "stages": [
     [
        {
           "id": "58a6678bc27c331884b60930",
           "description": "Lorem ipsum dolor sit amet...",
           "somevalue": 0,
           "user": "589cf6511b94281f34617a13"
        },
        {
           "_id": "589cf6511b94281f34617a13",
           "title": "Dr.",
           "firstname": "Doe",
           "lastname": "John"
        }
     ]
  ]

As you see, now I have a heap of arrays nested in each other. Is there a way to either unify the two sets within the array or give them proper index names?

Possible solution #1 would be this. The inner array is gone and the sets are united.

  "stages": [
        {
           "id": "58a6678bc27c331884b60930",
           "description": "Lorem ipsum dolor sit amet...",
           "somevalue": 0,
           "user": "589cf6511b94281f34617a13"
           "_id": "589cf6511b94281f34617a13",
           "title": "Dr.",
           "firstname": "Doe",
           "lastname": "John"
        }
  ]

Possible solution #2: the array not gone, but at least the elements now have index names and I don't have to refer to them as [0] and [1].

  "stages": [
     [
        "stage": {
           "id": "58a6678bc27c331884b60930",
           "description": "Lorem ipsum dolor sit amet...",
           "somevalue": 0,
           "user": "589cf6511b94281f34617a13"
        },

        "user": {
           "_id": "589cf6511b94281f34617a13",
           "title": "Dr.",
           "firstname": "Doe",
           "lastname": "John"
        }
     ]
  ]

A virtual cookie to anyone with a solution...

Aggregation Code

db.collection('bugs').aggregate([{
                $match: new ObjectId()
            }, {
                $lookup: {
                    from: 'users',
                    localField: 'user',
                    foreignField: '_id',
                    as: 'userdata'
                }
            },

            {
                $unwind: '$userdata'
            },

            {
                $sort: {
                    'stages.date': -1
                }
            },

            {
                $unwind: '$stages'
            }, {
                $lookup: {
                    from: 'users',
                    localField: 'stages.user',
                    foreignField: '_id',
                    as: 'stages_users'
                }
            },

            {
                $unwind: '$stages_users'
            }, {
                $unwind: '$stages'
            },

            {
                $group: {
                    '_id': '$_id',
                    'title': {
                        $first: '$title'
                    },
                    'user': {
                        $first: '$user'
                    },
                    'browser': {
                        $first: '$browser'
                    },
                    'severity': {
                        $first: '$severity'
                    },
                    'data': {
                        $first: '$data'
                    },
                    'userdata': {
                        $first: '$userdata'
                    },
                    'stages': {
                        $addToSet: {
                            $setUnion: [
                                ['$stages_users'],
                                ['$stages']
                            ]
                        }
                    }
                }
            },

            {
                $project: {

                    '_id': 1,
                    'user': 1,
                    'title': 1,
                    'browser': 1,
                    'date': 1,
                    'severity': 1,

                    'userdata._id': 1,
                    'userdata.title': 1,
                    'userdata.firstname': 1,
                    'userdata.lastname': 1,

                    'stages._id': 1,
                    'stages.id': 1,
                    'stages.user': 1,
                    'stages.date': 1,
                    'stages.description': 1,
                    'stages.severity': 1,
                    'stages.previous_severity': 1,
                    'stages.files': 1,

                    'stages.title': 1,
                    'stages.firstname': 1,
                    'stages.lastname': 1
                }
            }
        ],

Is it possible for you to post the aggregation code ? May be it is easier to add names before your final output. — s7vr
– s7vr, Commented Feb 17, 2017 at 22:24
Yes, but you may spend the weekend figuring it out... pastebin.com/SNPuZWG0 It is a simple ticket system for a bug reporting module. The bug report has a header in the collection named "bugs" which contains date, title and _id of user who posted it. Follow-ups are called stages and are stored in an array named "stages" for every record of "bugs". The stages also have "user" fields, for the person who posted them, and the aggregation is supposed to look up these users and add them to the "stages" array. — Tamás Polgár
– Tamás Polgár, Commented Feb 17, 2017 at 22:28

s7vr · Accepted Answer · 2017-02-18 02:45:48Z

1

You can try below aggregation.

So the idea here is to merge the fields from stages and stage_users into stages_merge_users document after $lookup in the $project stage.

I have added field each from stages and stage_users for demonstration.

You have to add all the fields one at a time from both the documents and the final stage is to $group stages back together.

db.bugs.aggregate([{
    $lookup: {
        from: 'users',
        localField: 'user',
        foreignField: '_id',
        as: 'userdata'
    }
}, {
    $unwind: '$stages'
}, {
    $lookup: {
        from: 'users',
        localField: 'stages.user',
        foreignField: '_id',
        as: 'stages_users'
    }
}, {
    $unwind: '$stages_users'
}, {
    $project: {
        '_id': 1,
        'user': 1,
        'title': 1,
        'browser': 1,
        'date': 1,
        'severity': 1,
        'userdata': 1,
        "stages_merge_users.firstname": "$stages_users.firstname",
        "stages_merge_users.user": "$stages.user"
    }
}, {
    $group: {
        '_id': '$_id',
        'title': {
            $first: '$title'
        },
        'user': {
            $first: '$user'
        },
        'browser': {
            $first: '$browser'
        },
        'severity': {
            $first: '$severity'
        },
        'data': {
            $first: '$data'
        },
        'userdata': {
            $first: '$userdata'
        },
        'stages': {
            $push: '$stages_merge_users'

        }
    }
}])

Version 3.4 Update

You can replace the $project stage with $addFields. This stages add fields to the existing fields.

{$addFields: {  "stages_merge_users.firstname":"$stages_users.firstname", "stages_merge_users.user_id":"$stages.user_id"}},

edited Feb 18, 2017 at 2:45

answered Feb 18, 2017 at 1:39

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

2 Comments

Tamás Polgár Over a year ago

Oh, so I can use $project before other operations! That's cool, I thought that's the end of it. However this whole thing seems a lot more complicated than in SQL. Thanks for the solution. I'd appreciate a 3.4 solution too.

s7vr Over a year ago

You are welcome. Added the 3.4 update. I mean the $lookup & $group stage is still the same when compared to sql. The rest is pretty standard. You can use aggregation stages in any order you want.

Collectives™ on Stack Overflow

MongoDB: how to assign index names to the elements of an array in the aggregation pipeline?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related