mongodb nodej驱动程序中的$ sum聚合 [英] $sum aggregation in mongodb nodej driver
本文介绍了mongodb nodej驱动程序中的$ sum聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
收藏1:管理员
{
"_id" : ObjectId("5e27fd3da42d441fe8a89580"),
"mappedcustomers": [
ObjectId("5e2555783405363bc4bf86c5"),
ObjectId("5e2555783405363bc4bf86c0"),
ObjectId("5e2555783405363bc4bf86c4")
],
"phoneNo" : 9897654358,
"name" : "acdbcs"
}
集合2:productOrders
Tt有很多文件，我们只关心:
Tt has lots of documents, our concern is only for:
-
"productOrderedForDate" : ISODate("2020-02-04T18:30:00Z")[明天订购]. -
"productOrderedForDate" : ISODate("2020-02-28T18:30:00Z")[上周订单]
"productOrderedForDate" : ISODate("2020-02-04T18:30:00Z")[tomorrow order]."productOrderedForDate" : ISODate("2020-02-28T18:30:00Z")[last week order]
[
{
"_id": ObjectId("5e27f998a42d441fe8a8957f"),
"authorized": false,
"orderCreatedBy": ObjectId("5e2555783405363bc4bf86c4"), // one of the mappedCustomer
"productOrderedForDate": ISODate("2020-02-04T18:30:00Z"),// tomorrow Order
"order": [{
"_id": ObjectId("5e26be2cc13b7149d0a95110"),
"productName": "Cups",
"productCode": "CICE1",
"size R": 21,
"size L": 16
},
{
"_id": ObjectId("5e26be2cc13b7149d0a9510f"),
"productName": "Bottles",
"productCode": "BTCE1",
"size R": 12,
"size L": 3
}]
},
{
"_id": ObjectId("5e26be2cc13b7149d0b90752b"),
"authorized": false,
"orderCreatedBy": ObjectId("5e2555783405363bc4bf86c0"),// another mappedCustomer
"productOrderedForDate": ISODate("2020-02-04T18:30:00Z"),// tomorrow Order
"order": [{
"_id": ObjectId("5e26be2cc13b7149d0a87230"),
"productName": "Cups",
"productCode": "CICE1",
"size R": 9,
"size L": 7
},
{
"_id": ObjectId("5e26be2cc13b7149d0a8560e"),
"productName": "Bottles",
"productCode": "BTCE1",
"size R": 3,
"size L": 11
}]
},
{
"_id": ObjectId("5e26be2cc13b7149d0b9876f"),
"authorized": true,
"orderCreatedBy": ObjectId("5e2555783405363bc4bf86c4"), // one of the mappedCustomer
"productOrderedForDate": ISODate("2020-01-28T18:30:00Z"),// lastWeek order
"order": [{
"_id": ObjectId("5e26be2cc13b7149d0a54220"),
"productName": "Cups",
"productCode": "CICE1",
"size R": 2,
"size L": 6
},
{
"_id": ObjectId("5e26be2cc13b7149d0a6520e"),
"productName": "Bottles",
"productCode": "BTCE1",
"size R": 8,
"size L": 16
}]
},
{
"_id": ObjectId("5e78f998a42d441fe898765d"),
"authorized": true,
"orderCreatedBy": ObjectId("5e2555783405363bc4bf86c0"), // another mappedCustomer
"productOrderedForDate": ISODate("2020-01-28T18:30:00Z"),// lastWeek order
"order": [{
"_id": ObjectId("5e26be2cc13b7149d0a87230"),
"productName": "Cups",
"productCode": "CICE1",
"size R": 26,
"size L": 19
},
{
"_id": ObjectId("5e26be2cc13b7149d0a8560f"),
"productName": "Bottles",
"productCode": "BTCE1",
"size R": 4,
"size L": 5
}]
}
]
这是我尝试过的并且能够平移所有mapedCustomers的方法，并且据此，我已经能够在下面的订单集合中找到由他们创建的订单，即聚合管道
This is what I have tried and have been able to unwind all mappedCustomers and according to that I have been able to find the orders created by them in orders collection below is the aggregation pipeline
db.admin.aggregate([
{
$match: {
_id: ObjectId("5e27fd3da42d441fe8a89580")
}
},
{
$lookup:
{
from: 'admin',
localField: 'mappedCustomers',
foreignField: '_id',
as: 'mappedCustomers'
}
},
{
$unwind: '$mappedCustomers'
},
{
$replaceRoot: {newRoot: "$mappedCustomers"}
},
{
$lookup:
{
from: "orders",
let: {mappedCustomersId: "$_id"},
pipeline: [
{
$match: {
$expr: {$eq: ["$orderCreatedBy", "$$mappedCustomersId"]},
'$or': [
{
'orderCreatedOn': ISODate("2020-02-04T18:30:00Z")
}, {
'orderCreatedOn': ISODate("2020-01-28T18:30:00Z")
}]
}
}],
as: "orders"
}
}, {
$unwind: "orders"
}
])
我的问题是我需要针对明天的日期和前一周的日期(即
My problem is that I need to display the sum of all the size R and size L for all the mappedCustomers against the productCode mapped under that admin for tomorrow's date and previous week date i.e
预期输出:
{
orders : [
{
"productOrderedForDate": ISODate("2020-02-04T18:30:00Z"),
"productName": "Cups",
"productCode": "CICE1",
"size R": 30,
"size L": 23,
"lastWeek": [{
"productOrderedForDate": ISODate("2020-01-28T18:30:00Z"),
"size R": 28,
"size L": 25,
}]
}, {
"productOrderedForDate": ISODate("2020-02-04T18:30:00Z"),
"productName": "Bottles",
"productCode": "BTCE1",
"size R": 15,
"size L": 14,
"lastWeek": [{
"productOrderedForDate": ISODate("2020-01-28T18:30:00Z"),
"size R": 12,
"size L": 21,
}]
}
]
}
回顾:
1.我将从req.body获取管理员id.
2.我将找到映射到mappedCustomers的所有客户.
3.我将从orders集合中查找mappedCustomers在所需日期创建的订单.
4.我需要将所有size R和size L分组.
To Recap:
1. I will fetch the admin id from req.body.
2. I will find all customers mapped to mappedCustomers.
3. I will lookup from orders collections the orders that the mappedCustomers have created for the required dates.
4. I need to group all size R and size L.
我设法完成了1,2,3，但是我无法得出4和5的要求结果.请看一看，并告诉我是否可以实现.
I managed to do 1,2,3 but I'm unable to produce the required result for 4 and 5. Please have a look at it and tell me if this can be achieved.
I already looked at this post but I could not get it to work.
推荐答案
尝试一下:
db.admin.aggregate([
{
$match: {
_id: ObjectId("5e27fd3da42d441fe8a89580")
}
},
{
$lookup: {
from: "admin",
localField: "mappedcustomers",
foreignField: "_id",
as: "mappedcustomers"
}
},
{
$unwind: "$mappedcustomers"
},
{
$replaceRoot: {
newRoot: "$mappedcustomers"
}
},
{
$lookup: {
from: "orders",
let: {
mappedCustomersId: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$orderCreatedBy",
"$$mappedCustomersId"
]
},
"$or": [
{
"productOrderedForDate": ISODate("2020-02-04T18:30:00Z")
},
{
"productOrderedForDate": ISODate("2020-01-28T18:30:00Z")
}
]
}
}
],
as: "orders"
}
},
{
$unwind: "$orders"
},
{
$unwind: "$orders.order"
},
{
$group: {
_id: "$orders.order.productCode",
orders: {
$push: {
productOrderedForDate: "$orders.productOrderedForDate",
productName: "$orders.order.productName",
productCode: "$orders.order.productCode",
"size R": "$orders.order.size R",
"size L": "$orders.order.size L"
}
}
}
},
{
$project: {
thisweek: {
$reduce: {
input: {
$filter: {
input: "$orders",
cond: {
$eq: [
"$$this.productOrderedForDate",
ISODate("2020-02-04T18:30:00Z")
]
}
}
},
initialValue: {
"size R": 0,
"size L": 0
},
in: {
productOrderedForDate: "$$this.productOrderedForDate",
"productName": "$$this.productName",
"productCode": "$$this.productCode",
"size R": {
$add: [
"$$value.size R",
"$$this.size R"
]
},
"size L": {
$add: [
"$$value.size L",
"$$this.size L"
]
}
}
}
},
lastWeek: {
$reduce: {
input: {
$filter: {
input: "$orders",
cond: {
$eq: [
"$$this.productOrderedForDate",
ISODate("2020-01-28T18:30:00Z")
]
}
}
},
initialValue: {
"size R": 0,
"size L": 0
},
in: {
productOrderedForDate: "$$this.productOrderedForDate",
"size R": {
$add: [
"$$value.size R",
"$$this.size R"
]
},
"size L": {
$add: [
"$$value.size L",
"$$this.size L"
]
}
}
}
}
}
},
{
$group: {
_id: null,
orders: {
$push: {
$mergeObjects: [
"$thisweek",
{
"lastWeek": [
"$lastWeek"
]
}
]
}
}
}
},
{
$unset: "_id"
}
])
这篇关于mongodb nodej驱动程序中的$ sum聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
