MongoDB: Aggregating hourly data into daily aggregates-CodePudding

I am new to MongoDB and have not been able to find a solution to my problem.

I am collecting hourly crypto data. Each document is an array of objects. Within each of these objects there is another nested array of objects. It looks as follows:

timestamp: "2022-05-11T12:38:01.537Z",
positions: [
    {
        detail: 1,
        name: "name",
        importantNumber: 0,
        arrayOfTokens: [
            {
                tokenName: "name",
                tokenSymbol: "symbol",
                tokenPrice: 1,
                tokensEarned: 10,
                baseAssetValueOfTokensEarned: 10,
            },
            {
                tokenName: "name2",
                tokenSymbol: "symbol2",
                tokenPrice: 2,
                tokensEarned: 10,
                baseAssetValueOfTokensEarned: 20,
            },
        ],
    },
],};

My goal is to be able to aggregate the hourly data into daily groups, where the timestamp becomes the day's date, the position array still houses the primary details of each position, sums the importantNumber (these I believe I have been able to achieve), and aggregates each hour's token details into one object for each token, calculating the average token price, the total tokens earned etc.

What I have so far is:

    const res = await Name.aggregate([
        {
            $unwind: {
                path: "$positions",
            },
        },
        {
            $project: {
                _id: 0,
                timestamp: "$timestamp",
                detail: "$positions.detail",
                name: "$positions.name",
                importantNumber: "$positions.importantNumber",
                arrayOfTokens: "$positions.arrayOfTokens ",
            },
        },
        {
            $group: {
                _id: {
                    date: { $dateToString: { format: "%Y-%m-%d", date: "$timestamp" } },
                    name: "$name",
                },
                importantNumber: { $sum: "$importantNumber" },
                arrayOfTokens: { $push: "$arrayOfTokens" }, // it is here that I am stuck
            },
        },
    ]);

    return res;
};

With two hours recorded, the above query returns the following result, with the arrayOfTokens housing multiple arrays:

{
  _id: {
    date: '2022-05-11',
    name: 'name',
  },
  importantNumber: //sum of important number,
  arrayOfTokens: [
    [ [Object], [Object] ], // hour 1: token1, token2.
    [ [Object], [Object] ] // hour 2: token1, token2
  ]
}

I would like the arrayOfTokens to house only one instance of each token object. Something similar to the following:

...
arrayOfTokens: [
    {allToken1}, {allToken2} // with the name and symbol preserved, and average of the token price, sum of tokens earned and sum of base asset value.
]

Any help would be greatly appreciated, thank you.

CodePudding user response：

Should be this one:

db.collection.aggregate([
  { $unwind: { path: "$positions" } },
  {
    $group: {
      _id: {
        date: { $dateTrunc: { date: "$timestamp", unit: "day" } },
        name: "$positions.name"
      },
      importantNumber: { $sum: "$positions.importantNumber" },
      arrayOfTokens: { $push: "$positions.arrayOfTokens" }
    }
  }
])

I prefer $dateTrunc over group by string.

Mongo Playground