mongodb - mongoDB groupBy Id, color
问题描述
我在 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。
非常感谢!
解决方案
您可以使用以下聚合查询。
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
})
如果您想解释每个阶段,请告诉我。
推荐阅读
- ssh - Paramiko 运行命令因 Kerberos 身份验证问题而失败
- bison - 在 Bison 中为布尔操作数创建产品的方法
- r - R:当向量用 Hmisc 标记时,`median()` 返回的类不一致
- javascript - 在浏览器上下文中使用 ioHook Node.js 全局本机键盘和鼠标侦听器
- node.js - 在 Sequelize 中使用 include 提取外键值
- python - 用于二进制分类的 cnn 模型,86% val_accuracy 总是返回 1
- django - Django雇主雇员关系
- matlab - 在 Matlab 中写入文件的命名约定错误
- matlab - 通过前置/后置 NaN 对齐单元阵列中的多个阵列
- html - 用html删除多余的空格