英文:
MongoDB aggregate and summary result using $match and $group
问题
这是你的代码部分，我会忽略它并为你翻译注释中的内容：
{
"status": 200,
"message": "Success",
"data": [
{
"_id": "2022400045",
"data": [
{
"x": "2023-02-22",
"y": 20400
},
{
"x": "2023-02-25",
"y": 500000
}
]
}
]
}
在这个部分，你希望通过材料和日期对数据进行分组。你的查询目前已经按材料分组，但你想要进一步优化查询以按日期对数据进行分组。你希望得到的结果是，每个材料都有一个数据数组，其中包含不同日期的总和。
英文:
So I have listed an array of objects from the MongoDB document below:
data=[
{
"_id": {
"$oid": "63f5e2449741a13be769ca16"
},
"userId": {
"$oid": "63f5bf0c0dc9b0c36ff32087"
},
"Jumlah": 12600,
"Batch": "9999999",
"Material":"2123100085",
"Expired_Date": "2099-99-99",
"Gedung": "C",
"Zona": "STAGING INBOUND",
"Plant": "K118",
"Transaction_Status": "verified",
"Transaction_Date": "2023-02-22",
"createdAt": 1677058628,
"updatedAt": 1677058628,
"__v": 0,
"From": "INBOUND BONGKAR"
},{
"_id": {
"$oid": "63f5e2449741a13be769ca16"
},
"userId": {
"$oid": "63f5bf0c0dc9b0c36ff32087"
},
"Material":"2123100085",
"Jumlah": 600,
"Batch": "9999999",
"Expired_Date": "2099-99-99",
"Gedung": "C",
"Zona": "STAGING INBOUND",
"Plant": "K118",
"Transaction_Status": "verified",
"Transaction_Date": "2023-02-22",
"createdAt": 1677058628,
"updatedAt": 1677058628,
"__v": 0,
"From": "INBOUND BONGKAR"
},{
"_id": {
"$oid": "63f5e2449741a13be769ca16"
},
"userId": {
"$oid": "63f5bf0c0dc9b0c36ff32087"
},
"Jumlah": 12100,
"Batch": "9999999",
"Material":"2123100085",
"Expired_Date": "2099-99-99",
"Gedung": "C",
"Zona": "STAGING INBOUND",
"Plant": "K118",
"Transaction_Status": "verified",
"Transaction_Date": "2023-02-23",
"createdAt": 1677058628,
"updatedAt": 1677058628,
"__v": 0,
"From": "INBOUND BONGKAR"
}
]
I try to group it by date using MongoDB query with the result below:
{
"status": 200,
"message": "Success",
"data": [
{
"_id": "2022400045",
"data": [
{
"x": "2023-02-22",
"y": 20400
},
{
"x": "2023-02-25",
"y": 20000
},
{
"x": "2023-02-25",
"y": 9000
},
{
"x": "2023-02-25",
"y": 4000
},
{
"x": "2023-02-25",
"y": 7000
},
{
"x": "2023-02-25",
"y": 3000
},
{
"x": "2023-02-25",
"y": 3000
}
]
},
]
}
here is my try:
const result = await TransactionIB.aggregate([
{ $match: { Transaction_Date: { $gte: date1, $lte: date2 } } },
{
$group: {
_id: "$Material",
Material_Description: { $first: "$Material_Description" },
data: {
$push: {
x: "$Transaction_Date",
y: { $sum: "$Jumlah" },
},
},
},
},
]);
is that a way to optimize the query so the expected value will be grouped not only by the material but also by the date with the expected result as below, or any help on this?:
"data": [
{
"_id": "2022400045",
"data": [
{
"x": "2023-02-22",
"y": 20400
},
{
"x": "2023-02-25",
"y": 500000
},
]
},
]
答案1
得分: 1
它需要两个层级的分组，
- 通过
Material和Transaction_Date使用$group，并在y中获取Jumlah的总和。 - 仅通过
Material使用$group，并构建data数组。
db.collection.aggregate([
{
$group: {
_id: {
Material: "$Material",
Transaction_Date: "$Transaction_Date"
},
Material_Description: {
$first: "$Material_Description"
},
y: {
$sum: "$Jumlah"
}
}
},
{
$group: {
_id: "$_id.Material",
Material_Description: {
$first: "$Material_Description"
},
data: {
$push: {
x: "$_id.Transaction_Date",
y: "$y"
}
}
}
}
])
英文:
It requires two levels of groups,
$groupbyMaterialandTransaction_Dateand get a total ofJumlahiny$groupby onlyMaterialand construct thedataarray
db.collection.aggregate([
{
$group: {
_id: {
Material: "$Material",
Transaction_Date: "$Transaction_Date"
},
Material_Description: {
$first: "$Material_Description"
},
y: {
$sum: "$Jumlah"
}
}
},
{
$group: {
_id: "$_id.Material",
Material_Description: {
$first: "$Material_Description"
},
data: {
$push: {
x: "$_id.Transaction_Date",
y: "$y"
}
}
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论