Struggling to structure aggregate data with $replaceRoot & $group

Working with Data
1

I have a collection of tasks that I’m trying to group using aggregate.

Sample Data:

{
  ...
  "assigned": "622aafaf9e9e2b0023fb5f92",
  "responsible": "624b90d8cfa6460059711558",
  "name": "test task",
  "status": "pending"
  ...
},
{
  ...
  "assigned": "624b90d8cfa6460059711558",
  "responsible": "622aafaf9e9e2b0023fb5f92",
  "name": "another test task",
  "status": "queued"
  ...
}

I’d like to have the aggregate query to return the following object:

{
"assigned": {
  "pending": [
    {
      ...
      "assigned": "622aafaf9e9e2b0023fb5f92",
      "responsible": "624b90d8cfa6460059711558",
      "name": "test task",
      "status": "pending"
      ...
    }
  ],
}
"responsible": {
  "queued": [
    {
      ...
      "assigned": "624b90d8cfa6460059711558",
      "responsible": "622aafaf9e9e2b0023fb5f92",
      "name": "another test task",
      "status": "queued"
      ...
    }
  ]
}
}

I’ve put this aggregate query together so far, which has almost got me there, but not quite:

const sharedStages = [
  { $sort: { createdAt: -1 } },
  { $limit: 20 },
  { $group: {
    _id: "$status",
    tasks: { $push: "$$ROOT" }
  } }
]
const agg = await Task.aggregate([
  {
    $facet: {
      "assigned": [
        { $match: { assignedTo: user.id } },
        ...sharedStages
      ],
      "responsible": [
        { $match: { responsible: user.id } },
        ...sharedStages
      ]
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        assigned: "$assigned",
        responsible: "$responsible"
      }
    }
  },
])

This query gets me this output, which is close, but I can’t seem to figure out how to get $replaceRoot to put together the status arrays full of tasks without losing some data.

[
  {
    assigned: [
      {
        _id: "pending",
        tasks: [{...}]
    ],
    responsible: [
      {
        _id: "queued",
        tasks: [{...}]
      }
    ]
  }
]

I’m still pretty new to Mongo so I could be going about this all wrong too - I’ve tried a bunch of different ways to use $replaceRoot and $arrayToObject and $objectToArray but haven’t found any success.

Would appreciate if anyone could help point me in the right direction to make this work, thank you!

2

Hi @boomography and welcome in the MongoDB Community :muscle: !

I’m trying but I’m struggling to understand what you are trying to do exactly.

Can you please confirm that:

  • You only expect a single doc at the end of the pipeline with 2 fields assigned and responsible that both contains a list of tasks?
  • You only expect to have pending tasks in the assigned tasks list.
  • You only expect to have queued tasks in the responsible tasks list.
  • All these tasks must be filtered by a given user, either by the field assigned or responsible.

Also, what do you mean by “losing some data”? What’s missing? Is this because of the limit 20 maybe?

Also I noticed that you are using assignedTo and assigned. Which one is it?

Cheers,
Maxime.

3

Thanks for the welcome, @MaBeuLux88! I apologize for the confusion, I was fighting with this until about 4 AM and posted this when I was half asleep.

Yes, one document as I’m only looking to return tasks for a single user. A user can have tasks that they are assigned to or responsible for. What I’m wanting to end up with is a document that represents all of the tasks associated with a given user that is split up by whether they’re responsible for or assigned to the task and then have the respective tasks grouped by their status.

That document should have two fields, assigned and responsible. Those fields should contain an object with keys named after the status type and the values of those keys being an array of tasks matching the respective type. For example:

assigned: {
  pending: [...],
  queued: [...],
  completed: [...],
  rejected: [...]
},
responsible: {
  pending: [...],
  completed: [...],
  rejected: [...]
}

Tasks can have many statuses and assigned/responsible lists should have an array for each status type that exists within its respective input documents.

If I $unwind tasks after the group stage I will only get one array of tasks back even if there are multiple status types. This may be intended behavior and I’m just not connecting the dots with at this point as I’ve been staring at this for too long.

It’s only assigned.

Hopefully that is more clear, thank you for the help!

Cheers,

Boom

1 Like
4

Hey @boomography,

Here is my attempt in Python. Just to try different things, I generated a bunch of docs randomly and then I wrote the pipeline in Compass and copy/pasted it in Python.

Let me l know what you think:

import random
from pprint import pprint

from faker import Faker
from pymongo import MongoClient

fake = Faker()


def rand_person():
    return random.choice(["Alice", "Bob", "Max", "Boom", "Nic", "John", "Joe"])


def rand_state():
    return random.choice(["pending", "completed", "rejected", "queued"])


def rand_tickets():
    return [{
        'assigned': rand_person(),
        'responsible': rand_person(),
        'name': fake.text(max_nb_chars=20),
        'status': rand_state()
    } for _ in range(100)]


if __name__ == '__main__':
    client = MongoClient()
    db = client.get_database('test')
    tickets = db.get_collection('tickets')
    tickets.drop()
    tickets.create_index("assigned")
    tickets.create_index("responsible")
    tickets.insert_many(rand_tickets())

    user = "Boom"
    pipeline = [
        {
            '$match': {
                '$or': [
                    {
                        'assigned': user
                    }, {
                        'responsible': user
                    }
                ]
            }
        }, {
            '$group': {
                '_id': None,
                'docs': {
                    '$push': '$$ROOT'
                }
            }
        }, {
            '$project': {
                'assigned': {
                    'pending': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.assigned', user]}, {'$eq': ['$$item.status', 'pending']}]}}},
                    'completed': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.assigned', user]}, {'$eq': ['$$item.status', 'completed']}]}}},
                    'rejected': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.assigned', user]}, {'$eq': ['$$item.status', 'rejected']}]}}},
                    'queued': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.assigned', user]}, {'$eq': ['$$item.status', 'queued']}]}}}
                },
                'responsible': {
                    'pending': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.responsible', user]}, {'$eq': ['$$item.status', 'pending']}]}}},
                    'completed': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.responsible', user]}, {'$eq': ['$$item.status', 'completed']}]}}},
                    'rejected': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.responsible', user]}, {'$eq': ['$$item.status', 'rejected']}]}}},
                    'queued': {'$filter': {'input': '$docs', 'as': 'item', 'cond': {'$and': [{'$eq': ['$$item.responsible', user]}, {'$eq': ['$$item.status', 'queued']}]}}}
                }
            }
        }
    ]

    for res in tickets.aggregate(pipeline):
        pprint(res)

Final result looks like this:

{'_id': None,
 'assigned': {'completed': [{'_id': ObjectId('62ace5bae1e2057c790a5d7b'),
                             'assigned': 'Boom',
                             'name': 'Lose someone.',
                             'responsible': 'Bob',
                             'status': 'completed'},
                            {'_id': ObjectId('62ace5bae1e2057c790a5d96'),
                             'assigned': 'Boom',
                             'name': 'Tend plant reveal.',
                             'responsible': 'Joe',
                             'status': 'completed'},
                            {'_id': ObjectId('62ace5bae1e2057c790a5dd0'),
                             'assigned': 'Boom',
                             'name': 'State state around.',
                             'responsible': 'Nic',
                             'status': 'completed'}],
              'pending': [{'_id': ObjectId('62ace5bae1e2057c790a5d8a'),
                           'assigned': 'Boom',
                           'name': 'Ask well prove.',
                           'responsible': 'John',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5d9c'),
                           'assigned': 'Boom',
                           'name': 'Contain development.',
                           'responsible': 'John',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5d9d'),
                           'assigned': 'Boom',
                           'name': 'Mouth strategy.',
                           'responsible': 'Alice',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5da6'),
                           'assigned': 'Boom',
                           'name': 'Clear fire feeling.',
                           'responsible': 'Boom',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5daf'),
                           'assigned': 'Boom',
                           'name': 'Fall bring feel.',
                           'responsible': 'Nic',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5db3'),
                           'assigned': 'Boom',
                           'name': 'Manage but himself.',
                           'responsible': 'Max',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5dc3'),
                           'assigned': 'Boom',
                           'name': 'So suffer accept.',
                           'responsible': 'Nic',
                           'status': 'pending'},
                          {'_id': ObjectId('62ace5bae1e2057c790a5dd5'),
                           'assigned': 'Boom',
                           'name': 'Ever foot different.',
                           'responsible': 'Bob',
                           'status': 'pending'}],
              'queued': [{'_id': ObjectId('62ace5bae1e2057c790a5d9f'),
                          'assigned': 'Boom',
                          'name': 'Reason after.',
                          'responsible': 'Alice',
                          'status': 'queued'}],
              'rejected': [{'_id': ObjectId('62ace5bae1e2057c790a5db7'),
                            'assigned': 'Boom',
                            'name': 'Middle understand.',
                            'responsible': 'John',
                            'status': 'rejected'},
                           {'_id': ObjectId('62ace5bae1e2057c790a5dc5'),
                            'assigned': 'Boom',
                            'name': 'South camera get.',
                            'responsible': 'Joe',
                            'status': 'rejected'}]},
 'responsible': {'completed': [{'_id': ObjectId('62ace5bae1e2057c790a5d90'),
                                'assigned': 'Joe',
                                'name': 'Letter tax agent.',
                                'responsible': 'Boom',
                                'status': 'completed'},
                               {'_id': ObjectId('62ace5bae1e2057c790a5db6'),
                                'assigned': 'Bob',
                                'name': 'West hand before.',
                                'responsible': 'Boom',
                                'status': 'completed'},
                               {'_id': ObjectId('62ace5bae1e2057c790a5dc9'),
                                'assigned': 'Bob',
                                'name': 'Feeling minute card.',
                                'responsible': 'Boom',
                                'status': 'completed'}],
                 'pending': [{'_id': ObjectId('62ace5bae1e2057c790a5da6'),
                              'assigned': 'Boom',
                              'name': 'Clear fire feeling.',
                              'responsible': 'Boom',
                              'status': 'pending'},
                             {'_id': ObjectId('62ace5bae1e2057c790a5da3'),
                              'assigned': 'John',
                              'name': 'Democratic it.',
                              'responsible': 'Boom',
                              'status': 'pending'},
                             {'_id': ObjectId('62ace5bae1e2057c790a5daa'),
                              'assigned': 'John',
                              'name': 'Account natural.',
                              'responsible': 'Boom',
                              'status': 'pending'}],
                 'queued': [{'_id': ObjectId('62ace5bae1e2057c790a5d7a'),
                             'assigned': 'John',
                             'name': 'Understand not.',
                             'responsible': 'Boom',
                             'status': 'queued'},
                            {'_id': ObjectId('62ace5bae1e2057c790a5d94'),
                             'assigned': 'Alice',
                             'name': 'When toward college.',
                             'responsible': 'Boom',
                             'status': 'queued'}],
                 'rejected': [{'_id': ObjectId('62ace5bae1e2057c790a5d83'),
                               'assigned': 'Joe',
                               'name': 'How cup simple back.',
                               'responsible': 'Boom',
                               'status': 'rejected'},
                              {'_id': ObjectId('62ace5bae1e2057c790a5d9a'),
                               'assigned': 'Joe',
                               'name': 'They task member.',
                               'responsible': 'Boom',
                               'status': 'rejected'},
                              {'_id': ObjectId('62ace5bae1e2057c790a5db1'),
                               'assigned': 'Alice',
                               'name': 'Operation any.',
                               'responsible': 'Boom',
                               'status': 'rejected'},
                              {'_id': ObjectId('62ace5bae1e2057c790a5dcc'),
                               'assigned': 'Joe',
                               'name': 'Interview itself.',
                               'responsible': 'Boom',
                               'status': 'rejected'},
                              {'_id': ObjectId('62ace5bae1e2057c790a5dcf'),
                               'assigned': 'Bob',
                               'name': 'Very teach beyond.',
                               'responsible': 'Boom',
                               'status': 'rejected'}]}}

Maybe there is a more sexy & optimized approach but I think it works. With $slice and $sortArray (v5.2+) you could refine even more the array if you like. You can also sort the array before the $group by Null.

It’s also trivial to add a filter in the $filter conditions if you want the createdAt field > X for instance.

Cheers,
Maxime.

5

Thank you! I will give this a try shortly.

1 Like