CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkaggregate
Thorai219
Thorai219

Reputation: 53

mongo aggregate group, match, project, count

I am trying to get an aggregate work in mongo Here is a simple json structure, and how I would like to achieve the aggregate.

// this is the format of the collection
[
  {
    _id: 6154f64df41fa3628ac2062a,
    type: 'email',
    platform: 'google',
    apiDataId: '17c33ac4735c80bf',
    timestamp: '2021-09-01',
    userId: '6132a04892559282c40fd29a',
    groupId: '6132a0cb74af9d82df74e918',
    __v: 0
  },
  {
    _id: 6154f64df41fa3628ac2062b,
    type: 'call',
    platform: 'yahoo',
    apiDataId: '17c2d25e2ccf770d',
    timestamp: '2021-09-01',
    userId: '6132a04892559282c40fd29a',
    groupId: '6132a0cb74af9d82df74e918',
    __v: 0
  },
  {
    _id: 6154f64df41fa3628ac2062c,
    type: 'email',
    platform: 'google',
    apiDataId: '2021-09-03',
    timestamp: '1632958029720',
    userId: '6132a04892559282c40ff2a9',
    groupId: '6132a0cb74af9d82df74e918',
    __v: 0
  },
  {
    _id: 6154f64df41fa3628ac2062d,
    type: 'email',
    platform: 'google',
    apiDataId: '17c273deffc51cc9',
    timestamp: '2021-09-04',
    userId: '6132a04892559282c40fd29a',
    groupId: '6132a0cb74af9d82df74e918',
    __v: 0
  },
  {
    _id: 6154f64df41fa3628ac2062e,
    type: 'call',
    platform: 'yahoo',
    apiDataId: '17c14c85f6a89088',
    timestamp: '2021-09-04',
    userId: '6132a04892559282c40ff2a9',
    groupId: '6132a0cb74af9d82df74e918',
    __v: 0
  }
]

I've started out wrting the aggregate, but I am having hard time understanding the aggregate,

aggregate([
    { $match: { groupId: groupId } },
    { $group: { _id: "$userId" } },
    { $group: { timestamp: "$timestamp" }  },
    { $project: { "groupId": 0, "userId": 0} },
    { $count: "num_data" }
  ]).exec()

what I am trying to accomplish edited

[
   groupId: '6132a04892559282c40fd29a',
   userId: {
      calls: [
        "2021-09-01": {platform: "yahoo"},
        "2021-09-03": {platform: "yahoo"},
        ...
      ],
      emails: [
        "2021-09-01": {platform: "google"},
        "2021-09-04": {platform: "google"},
        ...
      ],
  },
  userId: { ... }
]

of course that aggregation doesn't work, I am having hard time understanding and trying to figure out the orders and what $things to use.

Upvotes: 0

Views: 1000

Answers (1)

YuTing
YuTing

Reputation: 6629

Is this aggregate is what you looking for?

db.collection.aggregate([
  {
    $match: {
      groupId: "1"
    }
  },
  {
    $group: {
      _id: "$userId",
      count: {
        $sum: 1
      },
      apiDataId: {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$project": {
      "count": "$count",
      "apiDataId": {
        "timestamp": "$apiDataId.timestamp",
        "platform": "$apiDataId.platform",
        "type": "$apiDataId.type",
        "data": "$apiDataId.data"
      }
    }
  }
])

data

[
  {
    "groupId": "1",
    "timestamp": "1",
    "userId": "1",
    "platform": "1",
    "apiDataId": "1",
    "type": "1",
    "data": "1"
  },
  {
    "groupId": "1",
    "timestamp": "1",
    "userId": "2",
    "platform": "3",
    "apiDataId": "4",
    "type": "5",
    "data": "6"
  },
  {
    "groupId": "1",
    "timestamp": "3",
    "userId": "2",
    "platform": "7",
    "apiDataId": "8",
    "type": "9",
    "data": "9"
  },
  {
    "groupId": "2",
    "timestamp": "1",
    "userId": "1",
    "platform": "1",
    "apiDataId": "1",
    "type": "1",
    "data": "1"
  }
]

result

[
  {
    "_id": "2",
    "apiDataId": {
      "data": "6",
      "platform": "3",
      "timestamp": "1",
      "type": "5"
    },
    "count": 2
  },
  {
    "_id": "1",
    "apiDataId": {
      "data": "1",
      "platform": "1",
      "timestamp": "1",
      "type": "1"
    },
    "count": 1
  }
]

mongoplayground

Update: 2021-10-03

aggregate by userid then by date

db.collection.aggregate([
  {
    $match: {
      __v: 0
    }
  },
  {
    $group: {
      _id: {
        u: "$userId",
        t: "$timestamp"
      },
      count: {
        $sum: 1
      },
      "timestampList": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    $group: {
      _id: "$_id.u",
      count: {
        $sum: 1
      },
      "userList": {
        "$push": "$$ROOT"
      }
    }
  }
])

Upvotes: 1

Related Questions