我在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、....10等。
这是我需要的输出:
+--------+----------+-------+-------+-------+
| 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。
非常感谢!