Grouping in the same collection after got distinct value

Working with Data
,
1

Hi,

i tried but still can’t figure it out.
The requirement is:

  • get new users at 2023-09-15 by “newUser” is true.
  • sum the amount of these users group by appId in all documents, not just 2023-09-15.

I put my example as below for validation before coding:

The result would be:

[
{
    "appId": 3,
    "totalAmount": 11
},
{
    "appId": 7,
    "totalAmount": 100
},
{
    "appId": 5,
    "totalAmount": 2
}
]

Any hints are welcome.
Thank you in advance.

2
3

Thank you, Chris!

But it seems not solve the problem.

4

Hi,

It seems that below script is working:

db.collection.aggregate([
  {
    $match: {
      year: 2023,
      month: 9,
      day: 15,
      newUser: true
    }
  },
  {
    $group: {
      _id: null,
      distinctUserId: {
        $addToSet: "$userId"
      }
    }
  },
  {
    $lookup: {
      from: "collection",
      localField: "distinctUserId",
      foreignField: "userId",
      pipeline: [
        {
          $project: {
            _id: 0,
            appId: 1,
            amount: 1
          }
        }
      ],
      as: "result"
    }
  },
  {
    $unset: [
      "_id",
      "distinctUserId"
    ]
  },
  {
    $unwind: {
      path: "$result",
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $project: {
      _id: 0,
      appId: "$result.appId",
      amount: "$result.amount"
    }
  },
  {
    $group: {
      _id: {
        appId: "$appId"
      },
      paidNewUserIncome: {
        $sum: "$amount"
      }
    }
  }
])

But it uses self-lookup,
I don’t know how the performance while the documents grow.

If you have any good idea,
please kindly share with me.

Thank you.

5

Hi,

Another way:

db.collection.aggregate([
  {
    $match: {
      year: 2023,
      month: 9,
      day: 15,
      newUser: true
    }
  },
  {
    $group: {
      _id: null,
      distinctUserId: {
        $addToSet: "$userId"
      }
    }
  },
  {
    $lookup: {
      from: "collection",
      localField: "distinctUserId",
      foreignField: "userId",
      pipeline: [
        {
          $project: {
            _id: 0,
            appId: 1,
            amount: 1
          }
        }
      ],
      as: "result"
    }
  },
  {
    $unset: [
      "_id",
      "distinctUserId"
    ]
  },
  {
    $unwind: {
      path: "$result",
      preserveNullAndEmptyArrays: false
    }
  },
  {
    $replaceWith: "$result"
  },
  {
    $group: {
      _id: {
        appId: "$appId"
      },
      paidNewUserIncome: {
        $sum: "$amount"
      }
    }
  }
])