For example, I have a collection which looks like:
[{
"_id": xxxx,
"data": {
"1": 10,
"2": 30,
}
},
{
"_id": yyyy,
"data": {
"2": 10,
"3": 30,
}
}]
And I want to sum data by key :
{
"1": 10,
"2": 40,
"3": 30,
}
How can I do this ?
You can try below query :
db.collection.aggregate([
/** Convert data object into array of objects [{k:...,v:...}] */
{
$project: {
data: {
$objectToArray: "$data"
}
}
},
/** Unwind data array */
{
$unwind: "$data"
},
/** group on 'k' field of data field in each doc & sum values & push to 'v' field */
{
$group: {
_id: "$data.k",
v: {
$sum: "$data.v"
}
}
},
/** Transform _id object as 'k' & v to 'v' of an object of array ([{k:...,v:...}]) &
* make an object of field named data */
{
$project: {
_id: 0,
data: {
$arrayToObject: [
[
{
k: "$_id",
v: "$v"
}
]
]
}
}
},
/** make data as new root to each doc */
{
$replaceRoot: {
newRoot: "$data"
}
}
])
Test : MongoDB-Playground
Try this one:
db.collection.aggregate([
{
$addFields: {
data: {
$objectToArray: "$data"
}
}
},
{
$unwind: "$data"
},
{
$group: {
_id: "$data.k",
data: {
$sum: "$data.v"
}
}
},
{
$group: {
_id: null,
data: {
$push: {
k: "$_id",
v: "$data"
}
}
}
},
{
$addFields: {
data: {
$arrayToObject: "$data"
}
}
},
{
$replaceRoot: {
newRoot: "$data"
}
}
])