MongoDB按ID分组，颜色。

Question

MongoDB按ID分组，颜色。

3

我在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。

非常感谢！

- Bob

1

我找到了解决方案，如果你还在寻找，请联系我。 - hhharsha36

@hhharsha36 你好;) - Bob

@hhharsha36 ++++++ - Mike

1

已添加答案，请告诉我是否有效。 - hhharsha36

@hhharsha36 非常感谢！它起作用了。你是最棒的！;) - Bob

很高兴能帮到你。这个查询肯定可以优化，如果我将来更新答案，我会让你知道的。 - hhharsha36

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- hhharsha36 · Accepted Answer

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

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示例执行