mongodb - 如何使用 MongoDb 聚合 3 个集合的计数/总和？

Question

我正在尝试聚合多个集合并根据 createdAt 获取每日总数。 field :

db={
  "agents": [
    {
      "_id": ObjectId("5a934e000ac2030405000370"),
      "name": "Book Now"
    }
  ],
  "tolls": [
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "amountCollected": 20,
      "plaza": "Shimabala",
      "directionLane": "South/3",
      "cashier": "Chishala",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-02T13:23:06.232Z")
    },
    {
      "_id": ObjectId("a9934ec001a203040500dc00"),
      "amountCollected": 150,
      "plaza": "Shimabala",
      "directionLane": "South/3",
      "cashier": "Chishala",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-04T13:23:06.232Z")
    }
  ],
  "fuel": [
    {
      "_id": ObjectId("60b79e4e4afd77a1c27c730c"),
      "litres": 5.2,
      "price": 90,
      "station": "Chilanga",
      "fuelAttendant": "Manda",
      "agent": {
        "_id": ObjectId("5a934e000ac2030405000370"),
        "name": "Book Now"
      },
      "createdAt": ISODate("2021-06-04T16:06:16.232Z")
    }
  ]
}

预期成绩:

[
  {
    "_id": "2021-06-02",
    "fuel": 0,
    "tolls": 1
  },
  {
    "_id": "2021-06-04",
    "fuel": 1,
    "tolls": 1
  }
]

因为这两个集合连接到代理的集合，所以我有这个查询，但不知道如何从那里开始:

db.agents.aggregate([
  {
    "$lookup": {
      "localField": "_id",
      "from": "tolls",
      "foreignField": "agent._id",
      "as": "tolls"
    }
  },
  {
    "$unwind": {
      "path": "$tolls",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$lookup": {
      "localField": "_id",
      "from": "fuel",
      "foreignField": "agent._id",
      "as": "fuel"
    }
  },
  {
    "$unwind": {
      "path": "$fuel",
      "preserveNullAndEmptyArrays": true
    }
  } 
])

Link to the Playground

Answer 1

查询来自 tolls收藏

$project显示必填字段并添加新字段 type用于“通行费”

$unionWith与 fuel收藏和$project显示必填字段 添加新字段 type用于“燃料”

现在我们已经在根目录中合并了两个集合文档并添加了 type每个文档

$group来自 date和 type , 计算总元素

$group仅来自 date并构造 type 的数组与其 count键值格式

$addFields转换该 analytic使用 $arrayToObject 对象的字段

db.tolls.aggregate([
  { $project: { createdAt: 1, type: "tolls" } },
  {
    $unionWith: {
      coll: "fuel",
      pipeline: [
        { $project: { createdAt: 1, type: "fuel" } }
      ]
    }
  },
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            date: "$createdAt",
            format: "%Y-%m-%d"
          }
        },
        type: "$type"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      analytic: {
        $push: { k: "$_id.type", v: "$count" }
      }
    }
  },
  { $addFields: { analytic: { $arrayToObject: "$analytic" } } }
])

Playground
结果伤口为:

[
  {
    "_id": "2021-06-04",
    "analytic": {
      "fuel": 1,
      "tolls": 1
    }
  },
  {
    "_id": "2021-06-02",
    "analytic": {
      "tolls": 1
    }
  }
]

This will not add 0 count field, you have to manage it on frontend/client-side