MongoDB query to subtract one record value with another
I was working on fetching the value fields of all the records and subtracting it with next record value.
This is how my records looks like:
{
"name":"abc",
"value":10
},
{
"name":"xyz",
"value":20
},
{
"name":"pqr",
"value":30
}
And I have gone through these queries to achieve it, but didn't get the desired output.
Query:
db.myc.aggregate([{
$unwind: "$value"
}, {
$group: {
_id: "$name",
value1: {
$first: "$value"
},
value2: {
$last: "$value"
},
}
}, {
$project: {
Output: {
$subtract: ["$value1", 10]
}
}
}]);
Got output like :
{ "_id" : "abc", "Output" : 0 }
{ "_id" : "xyz", "Output" : 10 }
{ "_id" : "pqr", "Output" : 20 }
Desired output I was looking for , first record value should remain same which is 10 and next record value(20) should be subtracting with third record value(30). So that all the field values will remain 10. (Input data will always has 10 difference with next data).
Can anyone please let me know how to achieve it ???
node.js database mongodb
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
add a comment |
I was working on fetching the value fields of all the records and subtracting it with next record value.
This is how my records looks like:
{
"name":"abc",
"value":10
},
{
"name":"xyz",
"value":20
},
{
"name":"pqr",
"value":30
}
And I have gone through these queries to achieve it, but didn't get the desired output.
Query:
db.myc.aggregate([{
$unwind: "$value"
}, {
$group: {
_id: "$name",
value1: {
$first: "$value"
},
value2: {
$last: "$value"
},
}
}, {
$project: {
Output: {
$subtract: ["$value1", 10]
}
}
}]);
Got output like :
{ "_id" : "abc", "Output" : 0 }
{ "_id" : "xyz", "Output" : 10 }
{ "_id" : "pqr", "Output" : 20 }
Desired output I was looking for , first record value should remain same which is 10 and next record value(20) should be subtracting with third record value(30). So that all the field values will remain 10. (Input data will always has 10 difference with next data).
Can anyone please let me know how to achieve it ???
node.js database mongodb
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
1
That's not what$firstand$lastdo. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.
– Neil Lunn
Nov 21 '18 at 5:53
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18
add a comment |
I was working on fetching the value fields of all the records and subtracting it with next record value.
This is how my records looks like:
{
"name":"abc",
"value":10
},
{
"name":"xyz",
"value":20
},
{
"name":"pqr",
"value":30
}
And I have gone through these queries to achieve it, but didn't get the desired output.
Query:
db.myc.aggregate([{
$unwind: "$value"
}, {
$group: {
_id: "$name",
value1: {
$first: "$value"
},
value2: {
$last: "$value"
},
}
}, {
$project: {
Output: {
$subtract: ["$value1", 10]
}
}
}]);
Got output like :
{ "_id" : "abc", "Output" : 0 }
{ "_id" : "xyz", "Output" : 10 }
{ "_id" : "pqr", "Output" : 20 }
Desired output I was looking for , first record value should remain same which is 10 and next record value(20) should be subtracting with third record value(30). So that all the field values will remain 10. (Input data will always has 10 difference with next data).
Can anyone please let me know how to achieve it ???
node.js database mongodb
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
I was working on fetching the value fields of all the records and subtracting it with next record value.
This is how my records looks like:
{
"name":"abc",
"value":10
},
{
"name":"xyz",
"value":20
},
{
"name":"pqr",
"value":30
}
And I have gone through these queries to achieve it, but didn't get the desired output.
Query:
db.myc.aggregate([{
$unwind: "$value"
}, {
$group: {
_id: "$name",
value1: {
$first: "$value"
},
value2: {
$last: "$value"
},
}
}, {
$project: {
Output: {
$subtract: ["$value1", 10]
}
}
}]);
Got output like :
{ "_id" : "abc", "Output" : 0 }
{ "_id" : "xyz", "Output" : 10 }
{ "_id" : "pqr", "Output" : 20 }
Desired output I was looking for , first record value should remain same which is 10 and next record value(20) should be subtracting with third record value(30). So that all the field values will remain 10. (Input data will always has 10 difference with next data).
Can anyone please let me know how to achieve it ???
node.js database mongodb
node.js database mongodb
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
edited Nov 21 '18 at 6:18
Hongarc
2,3282926
2,3282926
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
asked Nov 21 '18 at 5:49
NaveeNNaveeN
816
816
1
That's not what$firstand$lastdo. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.
– Neil Lunn
Nov 21 '18 at 5:53
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18
add a comment |
1
That's not what$firstand$lastdo. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.
– Neil Lunn
Nov 21 '18 at 5:53
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18
1
1
That's not what
$first and $last do. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.– Neil Lunn
Nov 21 '18 at 5:53
That's not what
$first and $last do. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.– Neil Lunn
Nov 21 '18 at 5:53
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18
add a comment |
1 Answer
1
active
oldest
votes
Inputs:
{
"_id" : ObjectId("5bf52fe30d11f12257d430bf"),
"name" : "fff",
"value" : 50
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c0"),
"name" : "gd",
"value" : 60
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c1"),
"name" : "ffagf",
"value" : 70
}
Use the below query to get the desired output:
Note: The highest value will be stored as 0 at the end.
db.myc.find().forEach(
function(doc){
var n = db.myc.findOne({_id:{"$gt":doc._id}});
var diff = n.value-doc.value;
print(tojson(diff));
});
The Output :
10
10
0
Refer to this answer : Link
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
add a comment |
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%2f53405961%2fmongodb-query-to-subtract-one-record-value-with-another%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
Inputs:
{
"_id" : ObjectId("5bf52fe30d11f12257d430bf"),
"name" : "fff",
"value" : 50
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c0"),
"name" : "gd",
"value" : 60
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c1"),
"name" : "ffagf",
"value" : 70
}
Use the below query to get the desired output:
Note: The highest value will be stored as 0 at the end.
db.myc.find().forEach(
function(doc){
var n = db.myc.findOne({_id:{"$gt":doc._id}});
var diff = n.value-doc.value;
print(tojson(diff));
});
The Output :
10
10
0
Refer to this answer : Link
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
add a comment |
Inputs:
{
"_id" : ObjectId("5bf52fe30d11f12257d430bf"),
"name" : "fff",
"value" : 50
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c0"),
"name" : "gd",
"value" : 60
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c1"),
"name" : "ffagf",
"value" : 70
}
Use the below query to get the desired output:
Note: The highest value will be stored as 0 at the end.
db.myc.find().forEach(
function(doc){
var n = db.myc.findOne({_id:{"$gt":doc._id}});
var diff = n.value-doc.value;
print(tojson(diff));
});
The Output :
10
10
0
Refer to this answer : Link
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
add a comment |
Inputs:
{
"_id" : ObjectId("5bf52fe30d11f12257d430bf"),
"name" : "fff",
"value" : 50
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c0"),
"name" : "gd",
"value" : 60
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c1"),
"name" : "ffagf",
"value" : 70
}
Use the below query to get the desired output:
Note: The highest value will be stored as 0 at the end.
db.myc.find().forEach(
function(doc){
var n = db.myc.findOne({_id:{"$gt":doc._id}});
var diff = n.value-doc.value;
print(tojson(diff));
});
The Output :
10
10
0
Refer to this answer : Link
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
Inputs:
{
"_id" : ObjectId("5bf52fe30d11f12257d430bf"),
"name" : "fff",
"value" : 50
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c0"),
"name" : "gd",
"value" : 60
},
{
"_id" : ObjectId("5bf52fe30d11f12257d430c1"),
"name" : "ffagf",
"value" : 70
}
Use the below query to get the desired output:
Note: The highest value will be stored as 0 at the end.
db.myc.find().forEach(
function(doc){
var n = db.myc.findOne({_id:{"$gt":doc._id}});
var diff = n.value-doc.value;
print(tojson(diff));
});
The Output :
10
10
0
Refer to this answer : Link
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
answered Nov 21 '18 at 11:14
indraja boyaindraja boya
216
216
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
add a comment |
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
That's great, it worked.
– NaveeN
Nov 21 '18 at 12:49
add a comment |
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%2f53405961%2fmongodb-query-to-subtract-one-record-value-with-another%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
1
That's not what
$firstand$lastdo. You cannot access the previous or next document if that was what you were expecting. Use a cursor for this type of thing instead.– Neil Lunn
Nov 21 '18 at 5:53
@NeilLunn sure, i will try and the cursor.
– NaveeN
Nov 21 '18 at 6:18