假设我有以下数据:
let result = [
{
InvoiceID: ORD001,
orders: [
{
taxType: {
_id: "64e20a255366cca58c047ce9",
taxName: "Percentage Tax",
percentage: 2,
amount: null,
},
Name: "Yogurt",
category: "Dairy Products",
quantity: 1,
price: 100,
appliedTaxAmount: null,
},
{
taxType: {
_id: "64e20a255366cca58c047ce9",
taxName: "Amount Tax",
percentage: null,
amount: 2,
},
Name: "Campa Cola",
category: "Cold Drinks",
quantity: 5,
price: 50,
appliedTaxAmount: null,
},
],
},
{
InvoiceID: ORD002,
orders: [
{
taxType: {
_id: "64e20a255366cca58c047ce9",
taxName: "Amount Tax",
percentage: null,
amount: 3,
},
Name: "",
category: "Snacks",
quantity: 10,
price: 10,
appliedTaxAmount: null,
},
{
taxType: {
_id: "64e20a255366cca58c047ce9",
taxName: "Percentage Tax",
percentage: 10,
amount: null,
},
Name: "Campa Cola",
category: "Cold Drinks",
quantity: 2,
price: 10,
appliedTaxAmount: null,
},
],
},
];
要计算每个订单的appliedtaxamount并将PriceWithTax字段添加到每个对象,您可以使用以下JavaScript代码:
function calcTax(info) {
for (const data of result) {
for (const item of data.orders) {
if (item.taxType.amount === null) {
item.appliedTaxAmount =
(item.price * item.quantity * item.taxType.percentage) / 100;
} else if (item.taxType.percentage === null) {
item.appliedTaxAmount = item.quantity * item.taxType.amount;
}
item.priceWithTax = item.price * item.quantity + item.appliedTaxAmount;
}
}
return info;
}
const response = calcTax(result);
console.log(JSON.stringify(response, null, 2));
您还可以使用MongoDB的聚合框架实现类似的计算。以下是代码:
db.collectionName.aggregate([
{
$unwind: "$orders"
},
{
$addFields: {
"orders.appliedTaxAmount": {
$cond: {
if: { $ne: ["$orders.taxType.percentage", null] },
then: {
$multiply: [
"$orders.price",
"$orders.taxType.percentage",
"$orders.quantity",
0.01
]
},
else: {
$multiply: ["$orders.taxType.amount", "$orders.quantity"]
}
}
},
"orders.priceWithTax ": {
$add: [
{ $multiply: ["$orders.price", "$orders.quantity"] },
"$orders.appliedTaxAmount"
]
}
}
},
{
$group: {
_id: "$InvoiceID",
InvoiceID: { $first: "$InvoiceID" },
orders: { $push: "$orders" }
}
},
{
$group: {
_id: null,
result: { $push: "$$ROOT" }
}
},
{
$project: {
_id: 0,
result: 1
}
}
])
响应将以JSON文档的形式,该文档包含一个名为“结果:
”的阵列
{
"result": [
{
"InvoiceID": ORD001,
"orders": [
{
"taxType": {
"_id": "64e20a255366cca58c047ce9",
"taxName": "TaxOne",
"percentage": 2,
"amount": null
},
"Name": "Yogurt",
"category": "Dairy Products",
"quantity": 1,
"price": 100,
"appliedTaxAmount": 2,
"priceWithTax": 102
},
{
"taxType": {
"_id": "64e20a255366cca58c047ce9",
"taxName": "TaxTwo",
"percentage": null,
"amount": 2
},
"Name": "Campa Cola",
"category": "Cold Drinks",
"quantity": 5,
"price": 50,
"appliedTaxAmount": 10,
"priceWithTax": 60
}
]
},
{
"InvoiceID": ORD002,
"orders": [
{
"taxType": {
"_id": "64e20a255366cca58c047ce9",
"taxName": "Amount Tax",
"percentage": null,
"amount": 3
},
"Name": "",
"category": "Snacks",
"quantity": 10,
"price": 10,
"appliedTaxAmount": 30,
"priceWithTax": 130
},
{
"taxType": {
"_id": "64e20a255366cca58c047ce9",
"taxName": "TaxThree",
"percentage": 10,
"amount": null
},
"Name": "Campa Cola",
"category": "Cold Drinks",
"quantity": 2,
"price": 10,
"appliedTaxAmount": 2,
"priceWithTax": 22
}
]
}
]
}
上面的聚合管道执行以下步骤:
- $ utind :放弃订单数组,为每个订单行创建单独的文档。
- $ addfields :将计算出的字段添加到每个订单行中。
- $ group :通过InvoiceId对数据进行分组,并保留每个订单内的订单。
- 另一个 $ group :将所有订单分组为单个结果数组。
- $ project :仅项目结果字段,删除_id。