Mongodb聚合查找连接两个对象字段的集合数组匹配对象索引字段的总和

我有两个集合"数据集"和"用户".我try 在两个集合中查找对象array.

我想要连接"datets.stateHistory.date"字段和"users.prices.date"字段.获取DataSets集合的结果我需要"users.prices.Price"总和值的总和

Datasets json Data:个

"datasets": [
    {
      "colorDescription": "braun, rose gold",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493+00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493+00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493+00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493+00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493+00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493+00:00"
        }
      ]
    }
  ]

Users json Data:个

"users": [
    {
      "name": "Aravinth",
      "prices": [
        {
          "date": "2022-02-16T13:06:13.493+00:00",
          "price": 45
        },
        {
          "date": "2022-03-22T13:06:13.493+00:00",
          "price": 55
        }
      ]
    },
    {
      "name": "Raja",
      "prices": [
        {
          "date": "2022-02-24T13:06:13.493+00:00",
          "price": 75
        },
        {
          "date": "2022-03-23T13:06:13.493+00:00",
          "price": 85
        }
      ]
    }
  ]

Expected result json Data:个

[
    {
      "colorDescription": "braun, rose gold",
      "cgPrices: 45,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493+00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493+00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493+00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "cgPrices: 0,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493+00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493+00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493+00:00"
        }
      ]
    }
  ]

"cgPrice"字段我需要与添加两个集合的日期匹配的价格总和.

我的代码是:

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      "localField": "stateHistory.date",
      "foreignField": "prices.date"
    }
  },
  {
    "$project": {
      color: "$details.colorDescription",
      prices: "$details"
    }
  }
])

如何加入查找并获得匹配字段的价格添加额外的字段"cgPrice"count sum.

Mongoplayground 链接:https://mongoplayground.net/p/vv8R3DlEDYo