MongoDb aggregation query issue

Question

I have a MongoDb table documents with country, month and delivery partner name columns,
They look something like this -

[{
        "id": "1",
        "month": "Jan",
        "deliveryPartner": {
            "name": "Lalamove"
        },
        "country": "ID"
    },
    {
        "id": "2",
        "month": "Jan",
        "deliveryPartner": {
            "name": "Lalamove"
        },
        "country": "ID"
    },
    {
        "id": "3",
        "month": "Jan",
        "deliveryPartner": {
            "name": "Borzo"
        },
        "country": "ID"
    },
    {
        "id": "4",
        "month": "Jan",
        "deliveryPartner": {
            "name": "Lalamove"
        },
        "country": "PH"
    },
    {
        "id": "5",
        "month": "Jan",
        "deliveryPartner": {
            "name": "Borzo"
        },
        "country": "PH"
    },
    {
        "id": "6",
        "month": "Feb",
        "deliveryPartner": {
            "name": "Borzo"
        },
        "country": "ID"
    },
    {
        "id": "7",
        "month": "Feb",
        "deliveryPartner": {
            "name": "Borzo"
        },
        "country": "ID"
    },
    {
        "id": "8",
        "month": "Feb",
        "deliveryPartner": {
            "name": "Lalamove"
        },
        "country": "PH"
    }
]

I want to write an aggregate query that will give me an output like this -

[
    {
        "deliveryPartner": "Lalamove",
        "country": "ID",
        "month": [
            {
                "Jan": 2
            },
            {
                "Feb": 0
            }
        ]
    },
    {
        "deliveryPartner": "Borzo",
        "country": "ID",
        "month": [
            {
                "Jan": 1
            },
            {
                "Feb": 2
            }
        ]
    },
    {
        "deliveryPartner": "Lalamove",
        "country": "PH",
        "month": [
            {
                "Jan": 1
            },
            {
                "Feb": 1
            }
        ]
    },
    {
        "deliveryPartner": "Borzo",
        "country": "PH",
        "month": [
            {
                "Jan": 1
            },
            {
                "Feb": 0
            }
        ]
    }
]

I tried the following query but, the output is not coming as expected -

let result = await con.aggregate([
    { $match : { year : "2023", "deliveryPartner.name": { $ne: null }, "country": { $ne: null }, "month": { $ne: null } } },
    {
      $group: {
        _id: {
          deliveryPartner: "$deliveryPartner.name",
          country: "$country",
          month: "$month"
        },
        count: { $sum: 1 }
      }
    },
    {
      $group: {
        _id: {
          deliveryPartner: "$_id.deliveryPartner",
          country: "$_id.country"
        },
        monthData: {
          $push: {
            month: "$_id.month",
            count: "$count"
          }
        }
      }
    }
  ]).toArray();

  console.log("result => ", JSON.stringify(result));

The output does not set the missing month count to 0

Do you want to provide a set requested of months as an input, or do you want to get an output will all the months that are present in the collection? — nimrod serok
– nimrod serok, Commented Jul 18, 2023 at 21:01
output with months present in DB, I can handle it on code to get a unique month list from the DB resultSet and then add the missing month object with 0 count value, but was wondering if it can be handled on query level — Dev1ce
– Dev1ce, Commented Jul 19, 2023 at 7:09

nimrod serok · Accepted Answer · 2023-07-19 20:28:42Z

One option is to use $setWindowFields and $reduce:

$match and $group as you did.
Add the month index in order to enable sorting by months
Use $setWindowFields in order to add a set of all the months in the data to each document (And also sort by monthIndex)
$group again, but now push according to the sorted order
Use $set and $reduce in order to merge the monthData with the actualMonths

db.collection.aggregate([
  {$match: {
      "deliveryPartner.name": {$ne: null},
      "country": {$ne: null},
      "month": {$ne: null}
  }},
  {$group: {
      _id: {
        deliveryPartner: "$deliveryPartner.name",
        country: "$country",
        month: "$month"
      },
      count: {$sum: 1}
  }},
  {$addFields: {
      monthIndex: {$indexOfArray: [
          ["Jan", "Feb", "Mar", "Apr"], // add all months sorted
          "$_id.month"
      ]}
  }},
  {$setWindowFields: {
      sortBy: {monthIndex: 1},
      output: {actualMonths: {
          $addToSet: "$_id.month",
          window: {documents: ["unbounded", "unbounded"]}
      }}
  }},
  {$group: {
      _id: {
        deliveryPartner: "$_id.deliveryPartner",
        country: "$_id.country"
      },
      monthData: {$push: {month: "$_id.month", count: "$count"}},
      actualMonths: {$first: "$actualMonths"}
  }},
  {$set: {
      monthData: {
        $reduce: {
          input: "$actualMonths",
          initialValue: "$monthData",
          in: {$concatArrays: [
              "$$value",
              {$cond: [
                  {$in: ["$$this", "$monthData.month"]},
                  [],
                  [{count: 0, month: "$$this"}]
              ]}
          ]}
      }},
      actualMonths: "$$REMOVE"
  }}
])

See how it works on the playground example

is it possible to update this answer with what each step is doing for reference? I understood the match and group queries, the steps after that, look complex
How do I sort the monthData, I tried adding this - { $sort: { "_id.monthData.month": -1 } }

Collectives™ on Stack Overflow

MongoDb aggregation query issue

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related