MongoDB: sum values in object without MapReduce
I have a collections of objects:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 0.8891772,
"blue" : 0.7580757,
"green" : 0.4345628,
"white" : 0.7373822,
"black" : 0.93228924,
...
"purple" : 0.83328924,
}
}
Colors has many more keys that was shown above. Also, not every object has exactly the same keys, e.g. an object may not have properties.colors.red at all.
I need to sum the values of the color keys so that the output looks:
/* 1 */
{
"key" : "Red",
"value" : 2723.1982
}
/* 2 */
{
"key" : "Blue",
"value" : 972172.271
}
...
Where the values are the sum of the values for that color.
EDIT
In fact, better than just the sum for each properties.colors would be the average of the sums over the total number of documents in the original collections.
So for example:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 2.0,
"blue" : 4.0,
}
}
{
"_id" : "02",
"properties" : {
"colors" : {
"red" : 2.0,
"black" : 8.0,
}
}
Should result in:
/* 1 */
{
"key" : "red",
"value" : 2.0
}
/* 2 */
{
"key" : "blue",
"value" : 2.0
}
/* 3 */
{
"key" : "black",
"value" : 4.0
}
database mongodb aggregation-framework
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
add a comment |
I have a collections of objects:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 0.8891772,
"blue" : 0.7580757,
"green" : 0.4345628,
"white" : 0.7373822,
"black" : 0.93228924,
...
"purple" : 0.83328924,
}
}
Colors has many more keys that was shown above. Also, not every object has exactly the same keys, e.g. an object may not have properties.colors.red at all.
I need to sum the values of the color keys so that the output looks:
/* 1 */
{
"key" : "Red",
"value" : 2723.1982
}
/* 2 */
{
"key" : "Blue",
"value" : 972172.271
}
...
Where the values are the sum of the values for that color.
EDIT
In fact, better than just the sum for each properties.colors would be the average of the sums over the total number of documents in the original collections.
So for example:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 2.0,
"blue" : 4.0,
}
}
{
"_id" : "02",
"properties" : {
"colors" : {
"red" : 2.0,
"black" : 8.0,
}
}
Should result in:
/* 1 */
{
"key" : "red",
"value" : 2.0
}
/* 2 */
{
"key" : "blue",
"value" : 2.0
}
/* 3 */
{
"key" : "black",
"value" : 4.0
}
database mongodb aggregation-framework
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
add a comment |
I have a collections of objects:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 0.8891772,
"blue" : 0.7580757,
"green" : 0.4345628,
"white" : 0.7373822,
"black" : 0.93228924,
...
"purple" : 0.83328924,
}
}
Colors has many more keys that was shown above. Also, not every object has exactly the same keys, e.g. an object may not have properties.colors.red at all.
I need to sum the values of the color keys so that the output looks:
/* 1 */
{
"key" : "Red",
"value" : 2723.1982
}
/* 2 */
{
"key" : "Blue",
"value" : 972172.271
}
...
Where the values are the sum of the values for that color.
EDIT
In fact, better than just the sum for each properties.colors would be the average of the sums over the total number of documents in the original collections.
So for example:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 2.0,
"blue" : 4.0,
}
}
{
"_id" : "02",
"properties" : {
"colors" : {
"red" : 2.0,
"black" : 8.0,
}
}
Should result in:
/* 1 */
{
"key" : "red",
"value" : 2.0
}
/* 2 */
{
"key" : "blue",
"value" : 2.0
}
/* 3 */
{
"key" : "black",
"value" : 4.0
}
database mongodb aggregation-framework
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
I have a collections of objects:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 0.8891772,
"blue" : 0.7580757,
"green" : 0.4345628,
"white" : 0.7373822,
"black" : 0.93228924,
...
"purple" : 0.83328924,
}
}
Colors has many more keys that was shown above. Also, not every object has exactly the same keys, e.g. an object may not have properties.colors.red at all.
I need to sum the values of the color keys so that the output looks:
/* 1 */
{
"key" : "Red",
"value" : 2723.1982
}
/* 2 */
{
"key" : "Blue",
"value" : 972172.271
}
...
Where the values are the sum of the values for that color.
EDIT
In fact, better than just the sum for each properties.colors would be the average of the sums over the total number of documents in the original collections.
So for example:
{
"_id" : "01",
"properties" : {
"colors" : {
"red" : 2.0,
"blue" : 4.0,
}
}
{
"_id" : "02",
"properties" : {
"colors" : {
"red" : 2.0,
"black" : 8.0,
}
}
Should result in:
/* 1 */
{
"key" : "red",
"value" : 2.0
}
/* 2 */
{
"key" : "blue",
"value" : 2.0
}
/* 3 */
{
"key" : "black",
"value" : 4.0
}
database mongodb aggregation-framework
database mongodb aggregation-framework
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
edited Nov 24 '18 at 19:32
Edgar Derby
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
asked Nov 24 '18 at 19:05
Edgar DerbyEdgar Derby
67021230
67021230
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You have to run two simultaneous pipeline: one that simply counts all the documents and the second one that aggregates by color. You can do that using $facet. First pipeline is fairly simple: you just need $count to get the number of elements. You can start your second aggregation with $objectToArray which will transform your nested object to an array of keys and values (k and v fields). Then you can run $unwind on that array to get single document per entry to be able to use $group and $sum. Then you just need $project to reshape final result. Finally you need $divide to divide each result by the number of elements in collection. Try:
db.col.aggregate([
{
$facet: {
total: [ { $count: "value" } ],
agg: [
{
$project: {
colors: {
$objectToArray: "$properties.colors"
}
}
},
{
$unwind: "$colors"
},
{
$group: {
_id: "$colors.k",
v: { $sum: "$colors.v" }
}
}
]
}
},
{
$unwind: "$total"
},
{
$unwind: "$agg"
},
{
$project: {
_id: 0,
key: "$agg._id",
value: { $divide: [ "$agg.v", "$total.value" ] }
}
}
])
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
Yes I've noticed that, you need$facetto do that, check my modified answer.
– mickl
Nov 24 '18 at 19:44
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461476%2fmongodb-sum-values-in-object-without-mapreduce%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You have to run two simultaneous pipeline: one that simply counts all the documents and the second one that aggregates by color. You can do that using $facet. First pipeline is fairly simple: you just need $count to get the number of elements. You can start your second aggregation with $objectToArray which will transform your nested object to an array of keys and values (k and v fields). Then you can run $unwind on that array to get single document per entry to be able to use $group and $sum. Then you just need $project to reshape final result. Finally you need $divide to divide each result by the number of elements in collection. Try:
db.col.aggregate([
{
$facet: {
total: [ { $count: "value" } ],
agg: [
{
$project: {
colors: {
$objectToArray: "$properties.colors"
}
}
},
{
$unwind: "$colors"
},
{
$group: {
_id: "$colors.k",
v: { $sum: "$colors.v" }
}
}
]
}
},
{
$unwind: "$total"
},
{
$unwind: "$agg"
},
{
$project: {
_id: 0,
key: "$agg._id",
value: { $divide: [ "$agg.v", "$total.value" ] }
}
}
])
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
Yes I've noticed that, you need$facetto do that, check my modified answer.
– mickl
Nov 24 '18 at 19:44
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
|
show 3 more comments
You have to run two simultaneous pipeline: one that simply counts all the documents and the second one that aggregates by color. You can do that using $facet. First pipeline is fairly simple: you just need $count to get the number of elements. You can start your second aggregation with $objectToArray which will transform your nested object to an array of keys and values (k and v fields). Then you can run $unwind on that array to get single document per entry to be able to use $group and $sum. Then you just need $project to reshape final result. Finally you need $divide to divide each result by the number of elements in collection. Try:
db.col.aggregate([
{
$facet: {
total: [ { $count: "value" } ],
agg: [
{
$project: {
colors: {
$objectToArray: "$properties.colors"
}
}
},
{
$unwind: "$colors"
},
{
$group: {
_id: "$colors.k",
v: { $sum: "$colors.v" }
}
}
]
}
},
{
$unwind: "$total"
},
{
$unwind: "$agg"
},
{
$project: {
_id: 0,
key: "$agg._id",
value: { $divide: [ "$agg.v", "$total.value" ] }
}
}
])
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
Yes I've noticed that, you need$facetto do that, check my modified answer.
– mickl
Nov 24 '18 at 19:44
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
|
show 3 more comments
You have to run two simultaneous pipeline: one that simply counts all the documents and the second one that aggregates by color. You can do that using $facet. First pipeline is fairly simple: you just need $count to get the number of elements. You can start your second aggregation with $objectToArray which will transform your nested object to an array of keys and values (k and v fields). Then you can run $unwind on that array to get single document per entry to be able to use $group and $sum. Then you just need $project to reshape final result. Finally you need $divide to divide each result by the number of elements in collection. Try:
db.col.aggregate([
{
$facet: {
total: [ { $count: "value" } ],
agg: [
{
$project: {
colors: {
$objectToArray: "$properties.colors"
}
}
},
{
$unwind: "$colors"
},
{
$group: {
_id: "$colors.k",
v: { $sum: "$colors.v" }
}
}
]
}
},
{
$unwind: "$total"
},
{
$unwind: "$agg"
},
{
$project: {
_id: 0,
key: "$agg._id",
value: { $divide: [ "$agg.v", "$total.value" ] }
}
}
])
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
You have to run two simultaneous pipeline: one that simply counts all the documents and the second one that aggregates by color. You can do that using $facet. First pipeline is fairly simple: you just need $count to get the number of elements. You can start your second aggregation with $objectToArray which will transform your nested object to an array of keys and values (k and v fields). Then you can run $unwind on that array to get single document per entry to be able to use $group and $sum. Then you just need $project to reshape final result. Finally you need $divide to divide each result by the number of elements in collection. Try:
db.col.aggregate([
{
$facet: {
total: [ { $count: "value" } ],
agg: [
{
$project: {
colors: {
$objectToArray: "$properties.colors"
}
}
},
{
$unwind: "$colors"
},
{
$group: {
_id: "$colors.k",
v: { $sum: "$colors.v" }
}
}
]
}
},
{
$unwind: "$total"
},
{
$unwind: "$agg"
},
{
$project: {
_id: 0,
key: "$agg._id",
value: { $divide: [ "$agg.v", "$total.value" ] }
}
}
])
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
edited Nov 24 '18 at 19:43
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
answered Nov 24 '18 at 19:10
micklmickl
14.2k51639
14.2k51639
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
Yes I've noticed that, you need$facetto do that, check my modified answer.
– mickl
Nov 24 '18 at 19:44
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
|
show 3 more comments
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
Yes I've noticed that, you need$facetto do that, check my modified answer.
– mickl
Nov 24 '18 at 19:44
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
1
1
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Wow, this worked just perfectly. Thanks a lot!
– Edgar Derby
Nov 24 '18 at 19:14
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Follow-up question, @mickl: would it be possible, in the same query, to divide all the sum-values for the total of number of documents in the original collections?
– Edgar Derby
Nov 24 '18 at 19:21
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
Yes, that's possible, the question is what do you mean by the total number of documents: just count on collection or only those documents where particular key appears ? Or every key occurs in every document ? It'd be better to answer that in a separate question if you don't mind as it's not a best practice to modify answered question
– mickl
Nov 24 '18 at 19:25
1
1
Yes I've noticed that, you need
$facet to do that, check my modified answer.– mickl
Nov 24 '18 at 19:44
Yes I've noticed that, you need
$facet to do that, check my modified answer.– mickl
Nov 24 '18 at 19:44
1
1
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
This is not a query, @mickl, this is a work of art. :)
– Edgar Derby
Nov 24 '18 at 19:49
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461476%2fmongodb-sum-values-in-object-without-mapreduce%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
