So i have an mongodb data structure like below:
[
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.32.3.1",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750297,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.32.3.2",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750319,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.31.3.1",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750361,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.31.3.2",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750400,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.30.3.1",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750444,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.30.3.2",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750472,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.29.3.1",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750500,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 182,
"Lokasi": "W.29.3.2",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680750533,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Pink",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 54,
"Lokasi": "W.31.4.1",
"Zona": "W",
"shelfLifeDB": 6,
"expiredDate": "August-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-06",
"expiredStatus": "Good",
"ExpiredIn_months": 4,
"ReAging": "N",
"createdAt": 1680753064,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 108,
"Lokasi": "A.10.3.1",
"Zona": "A",
"shelfLifeDB": 6,
"expiredDate": "August-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-07",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680831580,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 108,
"Lokasi": "A.10.3.2",
"Zona": "A",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-07",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680831598,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 44,
"Lokasi": "A.9.1.1",
"Zona": "A",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-07",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680831742,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 108,
"Lokasi": "A.11.3.1",
"Zona": "A",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-07",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680831771,
"updatedAt": 1681428600,
"__v": 0
},
{
"Material": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"Type": "FG",
"Status": "NPL",
"Channel": "GT",
"Country": "INDONESIA",
"Activity": "Aging",
"Lakban": "Kuning",
"Gedung": "A",
"Plant": "K120",
"Jumlah": 108,
"Lokasi": "A.11.3.2",
"Zona": "A",
"shelfLifeDB": 6,
"expiredDate": "July-2023",
"Transaction_Status": "verified",
"Transaction_Date": "2023-04-07",
"expiredStatus": "Near Expired",
"ExpiredIn_months": 3,
"ReAging": "N",
"createdAt": 1680831790,
"updatedAt": 1681428600,
"__v": 0
}
]
as you can see above i want to aggregate it by 3 fields, Material, Transaction Date and The expired Date, at my first try i succeed to grouped it by Material and Transaction Date but i notice that eventhough it has more than one different expiredDate it not disctint it on the Response instead it groups it as one, so i try to aggregate by expiredDate also, here is my try:
if (req.body.search) {
query.push({
$and: [
{
$or: [
{
Material: { $regex: req.body.search, $options: "i" },
},
{
Material_Description: {
$regex: req.body.search,
$options: "i",
},
},
{
Type: { $regex: req.body.search, $options: "i" },
},
{
Lokasi: { $regex: req.body.search, $options: "i" },
},
{
Expired_Date: { $regex: req.body.search, $options: "i" },
},
{
Channel: { $regex: req.body.search, $options: "i" },
},
],
},
{
$and: [
{
Plant: plant,
},
{
$and: [
{
Type: type,
},
{
$and: [
{ Gedung: gedung },
{
$and: [
{
Transaction_Status: "verified",
},
{
$and: [
{
Transaction_Date: {
$lte: dateLists[0],
},
},
{
Transaction_Date: {
$gte: dateLists.slice(-1).toString(),
},
},
],
},
],
},
],
},
],
},
],
},
],
});
} else {
query.push({
$and: [
{
Plant: plant,
},
{
$and: [
{
Type: type,
},
{
$and: [
{ Gedung: gedung },
{
$and: [
{
Transaction_Status: "verified",
},
{
$and: [
{
Transaction_Date: {
$lte: dateLists[0],
},
},
{
Transaction_Date: {
$gte: dateLists.slice(-1).toString(),
},
},
],
},
],
},
],
},
],
},
],
});
}
console.log(dateLists);
const aggregate = TransactionFG.aggregate([
{
$match: query[0] ? query[0] : {},
},
{
$group: {
_id: {
Material: "$Material",
Transaction_Date: "$Transaction_Date",
},
Material_Description: {
$first: "$Material_Description",
},
expiredDate: {
$first: "$expiredDate",
},
y: {
$sum: "$Jumlah",
},
},
},
{
$group: {
_id: "$_id.Material",
Material_Description: {
$first: "$Material_Description",
},
expiredDate: {
$first: "$expiredDate",
},
data: {
$push: {
k: "$_id.Transaction_Date",
v: "$y",
},
},
},
},
{
$group: {
_id: "$_id.expiredDate",
Material_Description: {
$first: "$Material_Description",
},
Material: {
$first: "$Material",
},
datas: {
$push: {
k: "$_id.Transaction_Date",
v: "$y",
},
},
},
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
"$$ROOT",
{
$arrayToObject: "$data",
},
],
},
},
},
{
$unset: "data",
},
]);
const data = await TransactionFG.aggregatePaginate(aggregate, options);
But instead of returning the result i hope i get this error:
{
"status": 200,
"message": "Success Retrieving Data",
"data": {
"docs": [
{
"_id": null,
"Material_Description": "NABATI RCO 125g MT (24pcs)",
"Material": null,
"datas": [
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{}
]
}
],
"totalDocs": 1,
"limit": 1000,
"page": 1,
"totalPages": 1,
"pagingCounter": 1,
"hasPrevPage": false,
"hasNextPage": false,
"prevPage": null,
"nextPage": null
}
}
Can someone pointing out where did i do wrong or where do i have to improve here? my expected Value:
{
"_id": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"expiredDate": "August-2023",
"2023-04-07": 476,
"2023-04-06": 1510
},
{
"_id": "309979",
"Material_Description": "RICHEESE MIE GORENG KJ 74g (40 pcs)",
"expiredDate": "July-2023",
"2023-04-07": 183,
},
