mongodb - 使用 MongoDB 时获取组 _id 的数组
问题描述
我需要使用group
mongoDB 计算一些记录,但在我的情况下,数组_id
即将到来。我在下面解释我的查询。
let query = {
$group: {
_id: "$Products.ProductName",
dispatchcount: { $sum: 1 },
totalDispatchValue: {$sum:"$TotalAmount.TotalPayment"},
totalDiscountValue: {$sum: "$TotalDiscount.TotalDiscountAmount"}
}
}
pipeLine.push(query);
const orders = await dispatchOrdersCol.aggregate(pipeLine);
实际输出:
[
{
"_id": [
"Unisex Navy Blue Belt"
],
"dispatchcount": 1,
"totalDispatchValue": 27922,
"totalDiscountValue": 4084
},
{
"_id": [
"Writing Ruled Note Book",
"Physics Record Book",
"Chemistry Record Book",
"Computer Science Record Book"
],
"dispatchcount": 1,
"totalDispatchValue": 2190,
"totalDiscountValue": 0
},
{
"_id": [
"PU2-Physics Part-1 Text Book",
"PU2-Physics Part-2 Text Book",
"PU2-Mathematics Part - 1 Text Book",
"PU2-Chemistry Part - 1 Text Book",
"PU2-English Text Book",
"PU2-Mathematics Part - 2 Text Book",
"PU2-Chemistry Part - 2 Text Book",
"PU2-English Work Book",
"PU2-TEXT BOOK",
"PU2-Sanskrit Text Book",
"Boys White & Blue Striped Half Shirt",
"Boys Navy Blue Regular Fit Trousers",
"Illume Calf-length Cotton Black Socks "
],
"dispatchcount": 1,
"totalDispatchValue": 4131,
"totalDiscountValue": 150
},
{
"_id": [
"PU2-TEXT BOOK"
],
"dispatchcount": 1,
"totalDispatchValue": 1679,
"totalDiscountValue": 0
}
]
这里的_id
键有多个值,其中一些值也在下一条记录中重复。
猫鼬型号:
const Model = new Schema({
DispatchId: { type: Number, required: true },
OrderNumber: { type: String, unique: true, required: true },
OrderStatus: { type: String },
OrderType: { type: String },
DispatchPriority: { type: String },
Comments: { type: Array },
Products: { type: Array },
Customer: { type: Object },
TotalDiscount: { type: Object },
TotalShipping: { type: Object },
TotalCoupon: { type: Object },
TotalAmount: { type: Object },
PaymentDetails: { type: Object },
ClientDetails: { type: Object },
DispatchCreatedAt: { type: String },
DispatchUpdatedAt: { type: String },
IsActive: { type: Boolean }
},
{
timestamps: {
createdAt: 'CreatedAt',
updatedAt: 'UpdatedAt'
},
collection: DISPATCH_ORDERS_COLLECTION
}
);
在这里,我需要根据total Payment and total Discount
产品名称来计算。但在我的情况下,一个产品也在两个记录中出现两次。
解决方案
在您的文档Products
中是一个数组,(可能是一个带有字段的 Objects 数组ProductName
)当您这样做时,"$Products.ProductName"
它会给您一个产品名称数组。因此$group
,必须对其中具有完全相同元素的数组进行分组(即使两个数组之间的一个元素不匹配也会被视为_id
分组阶段的两个值)。因此,您需要在阶段$unwind
之前对 Products 数组进行操作$group
:
不正确的查询:
db.collection.aggregate([
/** You don't need this addFields stage, just given to test, you can remove `$group` stage & check the `products` field value */
{
$addFields: {
products: "$products.productName"
}
},
{
$group: {
_id: "$products" // arrays
}
}
])
测试: mongoplayground
正确查询:
db.collection.aggregate([
{
$unwind: "$products"
},
{
$addFields: {
products: "$products.productName"
}
},
{
$group: {
_id: "$products" // Single value
}
}
])
测试: mongoplayground
参考 : $unwind
推荐阅读
- java - 这三个按钮的区别“按钮按钮=(按钮)”
- ios - 将内部版本号传递给 Fastlane/Gym 中的 xcargs
- php - 实现一个 Wordpress 自定义函数
- sql-server - Microsoft SQL Server:选择每台机器上生产的每个零件号的前 N 个、联合和反透视
- java - Spring Boot @RequestMapping 是否完全匹配请求?
- html - 无法从文档文件夹加载到 iOS 上的 webView
- reactjs - react : Unhandled Rejection (SyntaxError): Unexpected token < in JSON at position 0
- matlab - 有没有办法在没有 for 循环的情况下做到这一点?
- android - 项目重建失败,但 APK 编译并正确运行
- python - Google Cloud 函数 Python Flask 模板文件夹