MongoDB Group by Multiple and DIFFERENT Fields

Question

I know this question has been asked a lot of times, but I can't seem to find what i'm looking for. I am using $project to get a collection of documents in MongoDB with 3 fields (Month, Week, Value), so I can then group them to sum the value by grouping each Month, or week independently, not combined.

Im using this $project:

let query = await spds.aggregate([
        { $match : { accountID: decoded.accountID, status: 'won', archivedDate: {$gt: new Date(oneYearAgoDate)}} },
        {
            $project: {
                value: 1,
                monthAndYear: { $dateToString: { format: "%m-%Y", date: "$archivedDate" } },
                weekAndYear: { $cond: [ { $gt: ["$archivedDate", new Date(sixMonthsAgoDate) ] }, { $dateToString: { format: "%V-%Y", date: "$archivedDate" } }, null ] },
            }
        },
    ]).toArray();

After the projection I get:

[
{
    "_id": "61b77f2ac0508737c8fe87e9",
    "value": 4900,
    "monthAndYear": "01-2022",
    "weekAndYear": "02-2022",
},
{
    "_id": "61c565584d698f6d61a69e17",
    "value": 11200,
    "monthAndYear": "12-2021",
    "weekAndYear": "51-2021",
},
{
    "_id": "61ea09aaa1040695259635a8",
    "value": 7800,
    "monthAndYear": "12-2021",
    "weekAndYear": "51-2021",
},
{
    "_id": "61ea09cca1040695259635a9",
    "value": 7800,
    "monthAndYear": "10-2021",
    "weekAndYear": "41-2021",
},
{
    "_id": "61ea0a21a1040695259635ab",
    "value": 1600,
    "monthAndYear": "12-2021",
    "weekAndYear": "52-2021",
},
{
    "_id": "61ea0b2594c47ca489c5573b",
    "value": 7000,
    "monthAndYear": "02-2022",
    "weekAndYear": "06-2022",
},
{
    "_id": "62031c7186a5daed72c7bd3c",
    "value": 0,
    "monthAndYear": "02-2022",
    "weekAndYear": "06-2022",
},
{
    "_id": "62031c7e86a5daed72c7bd3d",
    "value": 0,
    "monthAndYear": "02-2022",
    "weekAndYear": "06-2022",
},
{
    "_id": "62153dbb94fbec0a703a84e9",
    "value": 6920,
    "monthAndYear": "12-2021",
    "weekAndYear": "50-2021",
}

]

After using this $group:

let query = await spds.aggregate([
        { $match : { accountID: decoded.accountID, status: 'won', archivedDate: {$gt: new Date(oneYearAgoDate)}} },
        {
            $project: {
                value: 1,
                monthAndYear: { $dateToString: { format: "%m-%Y", date: "$archivedDate" } },
                weekAndYear: { $cond: [ { $gt: ["$archivedDate", new Date(sixMonthsAgoDate) ] }, { $dateToString: { format: "%V-%Y", date: "$archivedDate" } }, null ] },
            }
        },
        {
            $group: {
                _id: {
                    "monthAndYear": "$monthAndYear",
                    "weekAndYear": "$weekAndYear",
                  },
               value: { $sum: "$value" },
            }
        },
    ]).toArray();

I'm getting:

[
{
    "_id": {
        "monthAndYear": "02-2022",
        "weekAndYear": "06-2022"
    },
    "value": 7000
},
{
    "_id": {
        "monthAndYear": "12-2021",
        "weekAndYear": "52-2021"
    },
    "value": 1600
},
{
    "_id": {
        "monthAndYear": "12-2021",
        "weekAndYear": "50-2021"
    },
    "value": 6920
},
{
    "_id": {
        "monthAndYear": "01-2022",
        "weekAndYear": "02-2022"
    },
    "value": 4900
},
{
    "_id": {
        "monthAndYear": "10-2021",
        "weekAndYear": "41-2021"
    },
    "value": 7800
},
{
    "_id": {
        "monthAndYear": "12-2021",
        "weekAndYear": "51-2021"
    },
    "value": 19000
}

]

But I NEED to get:

monthAndYear:

[
{
    "_id": {
        "monthAndYear": "02-2022"
    },
    "value": 7000
},
{
    "_id": {
        "monthAndYear": "12-2021"
    },
    "value": 27520
},
{
    "_id": {
        "monthAndYear": "01-2022"
    },
    "value": 4900
},
{
    "_id": {
        "monthAndYear": "10-2021"
    },
    "value": 7800
}

]

weekAndYear:

[
{
    "_id": {
        "weekAndYear": "52-2021"
    },
    "value": 1600
},
{
    "_id": {
        "weekAndYear": "02-2022"
    },
    "value": 4900
},
{
    "_id": {
        "weekAndYear": "51-2021"
    },
    "value": 19000
},
{
    "_id": {
        "weekAndYear": "06-2022"
    },
    "value": 7000
},
{
    "_id": {
        "weekAndYear": "50-2021"
    },
    "value": 6920
},
{
    "_id": {
        "weekAndYear": "41-2021"
    },
    "value": 7800
}

] I just need to group by one field at the time, not both. Any help would be highly appreciated.

Wernfried Domscheit · Accepted Answer · 2022-03-10 20:55:00Z

The magic operator is $facet

Instead of grouping by format I prefer $dateTrunc and date format according to ISO-8601

db.getCollection("collection").aggregate([
   {
      $facet: {
         monthAndYear: [
            {
               $group: {
                  _id: { $dateTrunc: { date: "$archivedDate", unit: "month", timezone: "Europe/Zurich" } },
                  value: { $sum: "$value" }
               }
            },
            { $set: { _id: [{ k: "monthAndYear", v: { $dateToString: { format: "%Y-%m", date: "$_id" } } }] } },
            { $set: { _id: { $arrayToObject: "$_id" } } }
         ],
         weekAndYear: [
            {
               $group: {
                  _id: { $dateTrunc: { date: "$archivedDate", unit: "week", timezone: "Europe/Zurich", startOfWeek: "monday" } },
                  value: { $sum: "$value" }
               }
            },
            { $set: { _id: [{ k: "weekAndYear", v: { $dateToString: { format: "%G-W%V", date: "$_id" } } }] } },
            { $set: { _id: { $arrayToObject: "$_id" } } }
         ],
      }
   },
   { $project: { data: { $concatArrays: ["$monthAndYear", "$weekAndYear"] } } },
   { $unwind: "$data" },
   { $replaceWith: "$data" }
])

Collectives™ on Stack Overflow

MongoDB Group by Multiple and DIFFERENT Fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related