MongoDB - Help needed to make some aggregation

Question

I am having a bad time trying to do an aggregation in MongoDB.

I need to cross some infos from each user and as a final result I want a list of users (where there is only one object for each user) and for each object there is some lists with distinct information.

1 - The createdAtList array must be ordered from the oldest to the newest date. The sumOfTotal means the current position total summed up with the previous sumOfTotal (Exemplified in the code below), not just the sum of the total's

2 - The categotyList must be ordered like: category1, category2, category3 ...

3 - The desired final result must be ordered like: user1, user2, user3 ...

Basically I need some help to do the following:

//List of docs from my collection:
[
    {
        _id: "doc1",
        user: "user1",
        category: "category1",
        createdAt: "2018-01-01T00:00:00.000Z"
    },
    {
        _id: "doc2",
        user: "user1",
        category: "category2",
        createdAt: "2017-12-12T00:00:00.000Z",
    },
    {
        _id: "doc3",
        user: "user1",
        category: "category1",
        createdAt: "2017-12-12T00:00:00.000Z",
    },
    {
        _id: "doc4",
        user: "user1",
        category: "category2",
        createdAt: "2018-01-01T00:00:00.000Z"
    },
    {
        _id: "doc5",
        user: "user1",
        category: "category3",
        createdAt: "2017-11-11T00:00:00.000Z"
    }
]

//Desired result:
{
    user: "user1",
    createdAtList: [ //list ordered by createdAt
        {
            createdAt: "2017-11-11T00:00:00.000Z",
            total: 1,
            sumOfTotal: 0
        }
        {
            createdAt: "2017-12-12T00:00:00.000Z",
            total: 2,
            sumOfTotal: 3 //summed up with the previous
        }
        {
            createdAt: "2018-01-01T00:00:00.000Z",
            total: 2,
            sumOfTotal: 5 //summed up with the previous
        }
    ],
    categotyList: [ //list ordered by category
        {
            category: "category1",
            total: 2
        },
        {
            category: "category2",
            total: 2
        },
        {
            category: "category3",
            total: 1
        }
    ]
},
...

Is possible to do this in the same aggregate?

Answer 1

I do not think it really makes sense to have the createdAtList.sumOfTotal field. I do not think the fields in an array should be dependent upon a particular order of the elements. If you want some field to contain the sum of the createdAtList.total field, I think there should only be one field (outside of the array). That being said, here is the query I came up with to give you the desired results (using "users" as the name of the collection):

db.users.aggregate([
    {
        $group: {
            _id: {
                user: "$user",
                createdAt: "$createdAt"
            },
            total: { $sum: 1 },
            category: { $push: "$category" }
        }
    },
    {
        $project: {
            _id: 0,
            user: "$_id.user",
            createdAt: "$_id.createdAt",
            total: "$total",
            category: 1
        }
    },
    { $unwind: "$category" },
    {
        $group: {
            _id: {
                user: "$user",
                category: "$category"
            },
            catTotal: { $sum: 1 },
            createdAtList: {
                $push: {
                    createdAt: "$createdAt",
                    total: "$total"
                }
            }
        }
    },
    {
        $project: {
            _id: 0,
            user: "$_id.user",
            createdAtList: 1,
            category: "$_id.category",
            catTotal: 1
        }
    },
    { $unwind: "$createdAtList" },
    {
        $group: {
            _id: "$user",
            createdAtList: {
                $addToSet: "$createdAtList"
            },
            categoryList: {
                $addToSet: {
                    category: "$category",
                    total: "$catTotal"
                }
            }
        }
    },
    { $unwind: "$createdAtList" },
    { $sort: { "createdAtList.createdAt": 1 } },
    {
        $group: {
            _id: "$_id",
            createdAtList: {
                $push: "$createdAtList"
            },
            categoryList: {
                $first: "$categoryList"
            }
        }
    },
    { $unwind: "$categoryList" },
    { $sort: { "categoryList.category": 1 } },
    {
        $group: {
            _id: "$_id",
            createdAtList: {
                $first: "$createdAtList"
            },
            categoryList: {
                $push: "$categoryList"
            }
        }
    },
    {
        $project: {
        _id: 0,
        user: "$_id",
        createdAtList: 1,
        sumOfTotal: { $sum: "$createdAtList.total" },
        categoryList: 1
        }
    },
    { $sort: { user: 1 } },
]).pretty()