I am building an URL shortener. So I want to track datewise click count when a URL is visited using the short URL. For example: on 30th January if the URL was visited 3 times by using the short URL it will show the click count 5 but my document looks like this -

{
"_id":{"$oid":"61f6322cd3e3484d97a25e7c"},
"dayWiseClicks":
[{"date":"01/30/2022","dailyClicks":1,"_id":{"$oid":"61f66ff95fadc3e9f01b0d34"}},
{"date":"01/30/2022","dailyClicks":1,"_id":{"$oid":"61f66ffd5fadc3e9f01b0d38"}},
{"date":"01/30/2022","dailyClicks":1,"_id":{"$oid":"61f66fff5fadc3e9f01b0d3c"}}]
}

I want my document to look like this:

{
"_id":{"$oid":"61f6322cd3e3484d97a25e7c"},
"dayWiseClicks":
[{"date":"01/30/2022","dailyClicks":3,"_id":{"$oid":"61f66ff95fadc3e9f01b0d34"}}]
}

How can I achieve this using the MongoDB aggregation pipeline?

UPDATE Here's the full schema:

{
    merchant_name: String,
    store_id: String,
    original_url: String,
    url_hash: String,
    subdomain: String,
    // password: String,
    totalClicks: {
      type: Number,
      default: 0,
    },
    igClicks: {
      type: Number,
      default: 0,
    },
    fbClicks: {
      type: Number,
      default: 0,
    },
    directClicks: {
      type: Number,
      default: 0,
    },
    dayWiseClicks: [
      {
        date: {
          type: String,
          default: moment(new Date()).format("L"),
        },
        dailyClicks: {
          type: Number,
          default: 0,
        },
      },
    ],
    desktopClicks: {
      device: { type: String, default: "Desktop" },
      clicks: { type: Number, default: 0 },
    },
    mobileClicks: {
      device: { type: String, default: "Mobile" },
      clicks: { type: Number, default: 0 },
    },
  },
  { timestamps: true }

After the aggregation is done I want my document to look like this:

{
    "_id": {
      "$oid": "61f7a3b83dcebb77b05bd180"
    },
    "desktopClicks": {
      "device": "Desktop",
      "clicks": 5
    },
    "mobileClicks": {
      "device": "Mobile",
      "clicks": 0
    },
    "totalClicks": 5,
    "igClicks": 0,
    "fbClicks": 0,
    "directClicks": 5,
    "dayWiseClicks": [
      {
        "date": "01/31/2022",
        "dailyClicks": 5,
        "_id": {
          "$oid": "61f7a3fe5bd4f779cc53f697"
        }
      }
    ],
    "merchant_name": "Akash DTH",
    "store_id": "3333",
    "url_hash": "OGZhYTI",
    "subdomain": "",
    "original_url": "https://akashdth.com/",
    "createdAt": {
      "$date": "2022-01-31T08:54:16.472Z"
    },
    "updatedAt": {
      "$date": "2022-01-31T09:03:55.925Z"
    },
    "__v": 0
  }

CodePudding user response：

try

https://mongoplayground.net/p/osWlBuYy0NZ

db.collection.aggregate([
  {
    $unwind: {
      "path": "$dayWiseClicks"
    }
  },
  {
    $group: {
      _id: {
        "oid": "$_id",
        "date": "$dayWiseClicks.date"
      },
      "dailyClicks": {
        $sum: "$dayWiseClicks.dailyClicks"
      },
      "originalDayWiseClicks": {
        $push: "$dayWiseClicks"
      }
    }
  },
  {
    "$addFields": {
      "dayWiseClicks": [
        {
          "date": "$_id.date",
          "dailyClicks": "$dailyClicks",
          "_id": {
            "$first": "$originalDayWiseClicks._id"
          }
        }
      ]
    }
  },
  {
    "$project": {
      _id: "$_id.oid",
      "dayWiseClicks": 1
    }
  }
])

If you don't need the dayWiseClicks as nested or the first dayWiseClicks._id

It can be simplified to this:

https://mongoplayground.net/p/Zc9whjiLWdt

db.collection.aggregate([
  {
    $unwind: {
      "path": "$dayWiseClicks"
    }
  },
  {
    $group: {
      _id: {
        "oid": "$_id",
        "date": "$dayWiseClicks.date"
      },
      "dailyClicks": {
        $sum: "$dayWiseClicks.dailyClicks"
      }
    }
  },
  {
    $addFields: {
      "_id": "$_id.oid",
      "date": "$_id.date",
      
    }
  }
])