mongoDB groupBy Id，颜色

Question

我在mongodb中有树记录但可能还有更多，我通过来自前端的 ID 获取商店

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop1", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 1, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 2, // mongodb id
            itemCount: 3,
            colorId: colorId2
        }
    ]
}

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop2", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 2, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 3, // mongodb id
            itemCount: 3,
            colorId: colorId2
        }
    ]
}

{
    "_id" : ObjectId("6072c2d7ea13fb0338f6cf05"),
    "shopId" : "shop3", <- this is mongodb id
    "shopItems" : [
        {
            _id: ...,
            itemId: 3, // mongodb id
            itemCount: 5,
            colorId: colorId1
        }
        {
            _id: ...,
            itemId: 1, // mongodb id
            itemCount: 3,
            colorId: colorId1
        }
    ]
}

我需要获取 20 条记录并按 itemId 和 colorId 对它们进行分组，并获取每家商店的计数。商店的数量可以是1,2,3,....10etc..

这是我需要的输出:

+--------+----------+-------+-------+-------+
| itemId | colorId  | shop1 | shop2 | shop3 |
+========+==========+=======+=======+=======+
| 1      | colorId1 | 5     | 0     | 3     |
+--------+----------+-------+-------+-------+
| 2      | colorId2 | 3     | 0     | 0     |
+--------+----------+-------+-------+-------+
| 3      | colorId2 | 0     | 3     | 0     |
+--------+----------+-------+-------+-------+
| 2      | colorId1 | 0     | 5     | 0     |
+--------+----------+-------+-------+-------+
| 3      | colorId1 | 0     | 0     | 5     |
+--------+----------+-------+-------+-------+

我的代码是:

const stores  = await Store.aggregate([
{ $match: query },

{ $project: { shopId: 1, tt: { $slice: [ "$shopItems", 3 ] } } },
])

如果商店中不存在具有 itemId 和 colorId 的商品，我需要值 0。

非常感谢!

Answer 1

您可以使用下面的聚合查询。

db.collection.aggregate([
  {
    "$match": {}  // <-- Highly recommend you to use match due to the complexity of this query
  },
  {
    "$unwind": {
      "path": "$shopItems",
    }
  }, 
  {
    "$facet": {
      "shopIds": [
        {
          "$group": {
            "_id": null,
            "shopIds": {
              "$addToSet": "$shopId"
            }
          }
        },
        {
          "$unwind": {
            "path": "$shopIds",
          }
        },
        {
          "$sort": {
            "shopIds": 1
          }
        },
        {
          "$group": {
            "_id": null,
            "shopIds": {"$push": "$shopIds"}
          }
        },
      ],
      "docRoot": [
        {
          "$group": {
            "_id": {
              "itemId": "$shopItems.itemId",
              "colorId": "$shopItems.colorId",
              "shopIds": "$shopId",
            },
            "count": {"$sum": 1}
          }
        },
        {
          "$group": {
            "_id": {
              "itemId": "$_id.itemId",
              "colorId": "$_id.colorId",
            },
            "shopCount": {
              "$push": {
                "shopId": "$_id.shopIds",
                "count": "$count",
              }
            }
          },
        },
      ],
    }
  }, 
  {
    "$unwind": {
      "path": "$docRoot",
    }
  }, 
  {
    "$project": {
      "itemId": "$docRoot._id.itemId",
      "colorId": "$docRoot._id.colorId",
      "shopId": "$complete.shopId",
      "count": "$complete.count",
      "keySwap": {
        "$reduce": {
          "input": {
            "$map": {
              "input": {
                "$map": {
                  "input": {
                    "$concatArrays": [
                      {
                          "$map": {
                          "input": {
                            "$setDifference": [
                              {"$arrayElemAt": ["$shopIds.shopIds", 0]},
                              {
                                "$map": {
                                  "input": "$docRoot.shopCount",
                                  "as": "elem",
                                  "in": "$$elem.shopId"
                                }
                              },
                            ],
                          },
                          "as": "elem",
                          "in": {
                            "shopId": "$$elem",
                            "count": 0
                          },
                        },
                      },
                      "$docRoot.shopCount",
                    ]
                  },
                  "as": "elem",
                  "in": {
                    "$objectToArray": "$$elem"
                  }
                },
              },
              "as": "elem1",
              "in": {
                "$arrayToObject": [
                  [{
                    "k": {"$arrayElemAt": ["$$elem1.v", 0]},
                    "v": {"$arrayElemAt": ["$$elem1.v", 1]},
                  }]
                ],
              },
            },
          },
          "initialValue": {},
          "in": {
            "$mergeObjects": ["$$value", "$$this"]
          }
        },
      },
    }
  }, 
  {
    "$replaceRoot": {
      "newRoot": {
          "$mergeObjects": [
            {"itemId": "$itemId", "colorId": "$colorId"},
            "$keySwap",
          ]
      }
    }
  }
], {
  "allowDiskUse": true
})

如果您想要每个阶段的解释，请告诉我。

Mongo Playground Sample Execution