I'm new to MongoDB, and having some problems. My document contains fixed size int array that i should sum.
Can mongo sum two int array in a query grouped by another field? In my case, string date.
example data:
{d:[1,2], date:"17-01-2020"} {d:[3,4], date:"17-01-2020"} {d:[5,6], date:"18-01-2020"}
query result that i want:
{d:[4, 6], date:"17-01-2020"} {d:[5,6], date:"18-01-2020"}
If you have fixes size of two values then you can use this one:
db.collection.aggregate([
{ $group: { _id: null, data: { $push: "$d" } } },
{
$set: {
d: [
{$sum:{ $map: { input: "$data", in: { $first: "$$this" } } }},
{$sum:{ $map: { input: "$data", in: { $last: "$$this" } } }}
]
}
},
{$unset: "data"}
])
For any length use this one:
db.collection.aggregate([
{ $group: { _id: null, data: { $push: "$d" } } },
{
$set: {
d: {
$map: {
input: { $range: [0, { $size: { $first: "$data" } }] },
as: "idx",
in: { $sum: { $map: { input: "$data", in: { $arrayElemAt: ["$$this", "$$idx"] } } } }
}
}
}
}
])
The first array determines the length of all the other arrays.
Another approach is this one:
db.collection.aggregate([
{ $unwind: { path: "$d", includeArrayIndex: "idx" } },
{ $group: { _id: "$idx", d: { $sum: "$d" } } },
{ $sort: { _id: 1 } },
{ $group: { _id: null, d: { $push: "$d" } } }
])
Or if you like to add other fields:
db.collection.aggregate([
{ $unwind: { path: "$d", includeArrayIndex: "idx" } },
{ $group: { _id: "$idx", d: { $sum: "$d" }, date: { $first: "$date" } } },
{ $sort: { _id: 1 } },
{ $group: { _id: "$date", d: { $push: "$d" } } },
{ $project: { d: 1, date: "$_id" } }
])
This works even if the arrays do not all have the same length.
$first/$last was introduced in Mongo v4.4. Use { $arrayElemAt: ["$$this", 0] } and { $arrayElemAt: ["$$this", 1] } instead