Mixing
Mongodb compare two fields with matched fields value aggregate
I am having problems in my query, The parameter I am passing are voucherId, and userId, I need to check if that user exceeded the limitedQuantityPerUser. If yes, exclude this voucher. But I am having problems comparing the matching fields, example if voucherId is 'aaa', check if the quantityBrought > limitedQuantityPerUser.
I have tried this
vouchers.find({
'status': {
$in: ['OP', 'WG', 'LO', 'SO']
},
'details.userReachedLimitedQuantity': {
$ne: userId
},
}, fields, {
sort: {
'order': 1
}
}
But this will give me all the result where userId not equal in the list. not exactly what I need. Is it better to solve using aggregate?
Example resource JSON file
[{
"_id": "111",
"details": {
"limitedQuantityPerUser": "3",
"userReachedLimitedQuantity": [{
"userId": "aaa",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "ccc",
"quantityBrought": "3"
}
],
"status": "OP"
}
},
{
"_id": "222",
"details": {
"limitedQuantityPerUser": "2",
"userReachedLimitedQuantity": [{
"userId": "eee",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "vvv",
"quantityBrought": "2"
}
],
"status": "OP"
}
}
]
mongodb aggregation-framework
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
add a comment |
I am having problems in my query, The parameter I am passing are voucherId, and userId, I need to check if that user exceeded the limitedQuantityPerUser. If yes, exclude this voucher. But I am having problems comparing the matching fields, example if voucherId is 'aaa', check if the quantityBrought > limitedQuantityPerUser.
I have tried this
vouchers.find({
'status': {
$in: ['OP', 'WG', 'LO', 'SO']
},
'details.userReachedLimitedQuantity': {
$ne: userId
},
}, fields, {
sort: {
'order': 1
}
}
But this will give me all the result where userId not equal in the list. not exactly what I need. Is it better to solve using aggregate?
Example resource JSON file
[{
"_id": "111",
"details": {
"limitedQuantityPerUser": "3",
"userReachedLimitedQuantity": [{
"userId": "aaa",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "ccc",
"quantityBrought": "3"
}
],
"status": "OP"
}
},
{
"_id": "222",
"details": {
"limitedQuantityPerUser": "2",
"userReachedLimitedQuantity": [{
"userId": "eee",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "vvv",
"quantityBrought": "2"
}
],
"status": "OP"
}
}
]
mongodb aggregation-framework
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
add a comment |
I am having problems in my query, The parameter I am passing are voucherId, and userId, I need to check if that user exceeded the limitedQuantityPerUser. If yes, exclude this voucher. But I am having problems comparing the matching fields, example if voucherId is 'aaa', check if the quantityBrought > limitedQuantityPerUser.
I have tried this
vouchers.find({
'status': {
$in: ['OP', 'WG', 'LO', 'SO']
},
'details.userReachedLimitedQuantity': {
$ne: userId
},
}, fields, {
sort: {
'order': 1
}
}
But this will give me all the result where userId not equal in the list. not exactly what I need. Is it better to solve using aggregate?
Example resource JSON file
[{
"_id": "111",
"details": {
"limitedQuantityPerUser": "3",
"userReachedLimitedQuantity": [{
"userId": "aaa",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "ccc",
"quantityBrought": "3"
}
],
"status": "OP"
}
},
{
"_id": "222",
"details": {
"limitedQuantityPerUser": "2",
"userReachedLimitedQuantity": [{
"userId": "eee",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "vvv",
"quantityBrought": "2"
}
],
"status": "OP"
}
}
]
mongodb aggregation-framework
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
I am having problems in my query, The parameter I am passing are voucherId, and userId, I need to check if that user exceeded the limitedQuantityPerUser. If yes, exclude this voucher. But I am having problems comparing the matching fields, example if voucherId is 'aaa', check if the quantityBrought > limitedQuantityPerUser.
I have tried this
vouchers.find({
'status': {
$in: ['OP', 'WG', 'LO', 'SO']
},
'details.userReachedLimitedQuantity': {
$ne: userId
},
}, fields, {
sort: {
'order': 1
}
}
But this will give me all the result where userId not equal in the list. not exactly what I need. Is it better to solve using aggregate?
Example resource JSON file
[{
"_id": "111",
"details": {
"limitedQuantityPerUser": "3",
"userReachedLimitedQuantity": [{
"userId": "aaa",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "ccc",
"quantityBrought": "3"
}
],
"status": "OP"
}
},
{
"_id": "222",
"details": {
"limitedQuantityPerUser": "2",
"userReachedLimitedQuantity": [{
"userId": "eee",
"quantityBrought": "2"
},
{
"userId": "bbb",
"quantityBrought": "1"
},
{
"userId": "vvv",
"quantityBrought": "2"
}
],
"status": "OP"
}
}
]
mongodb aggregation-framework
mongodb aggregation-framework
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
asked Jan 1 at 13:33
Thomas KimThomas Kim
8717
8717
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You can use below aggregation to check whether specified userId exceeded defined limit.
db.vouchers.aggregate([
{
$match: {
$expr: {
$allElementsTrue: {
$map: {
input: "$details.userReachedLimitedQuantity",
as: "user",
in: {
$or: [
{ $ne: [ "$$user.userId", userId ] },
{ $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser" ] }
]
}
}
}
}
}
}
])
$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.
So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser
answered Jan 1 at 19:03
micklmickl
14.3k51639
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above$match?
– mickl
Jan 2 at 17:41
|
show 2 more comments
No a perfect answer, but I did the filter in the result of mongod query
// Only take matched userId's quantityPurchased
const newResult = result.filter(val => {
if (!val.details.userReachedLimitedQuantity) {
return val;
}
val.details.userReachedLimitedQuantity= val.details.userReachedLimitedQuantity.filter((o) => {
return (String(o.userId) == userId)
});
if (val.details.userReachedLimitedQuantity[0].quantityBrou < val.details.limitedQuantityPerUser) {
return val;
} else {
return false;
}
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
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%2f53995878%2fmongodb-compare-two-fields-with-matched-fields-value-aggregate%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use below aggregation to check whether specified userId exceeded defined limit.
db.vouchers.aggregate([
{
$match: {
$expr: {
$allElementsTrue: {
$map: {
input: "$details.userReachedLimitedQuantity",
as: "user",
in: {
$or: [
{ $ne: [ "$$user.userId", userId ] },
{ $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser" ] }
]
}
}
}
}
}
}
])
$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.
So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser
answered Jan 1 at 19:03
micklmickl
14.3k51639
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above$match?
– mickl
Jan 2 at 17:41
|
show 2 more comments
You can use below aggregation to check whether specified userId exceeded defined limit.
db.vouchers.aggregate([
{
$match: {
$expr: {
$allElementsTrue: {
$map: {
input: "$details.userReachedLimitedQuantity",
as: "user",
in: {
$or: [
{ $ne: [ "$$user.userId", userId ] },
{ $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser" ] }
]
}
}
}
}
}
}
])
$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.
So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser
answered Jan 1 at 19:03
micklmickl
14.3k51639
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above$match?
– mickl
Jan 2 at 17:41
|
show 2 more comments
You can use below aggregation to check whether specified userId exceeded defined limit.
db.vouchers.aggregate([
{
$match: {
$expr: {
$allElementsTrue: {
$map: {
input: "$details.userReachedLimitedQuantity",
as: "user",
in: {
$or: [
{ $ne: [ "$$user.userId", userId ] },
{ $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser" ] }
]
}
}
}
}
}
}
])
$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.
So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser
answered Jan 1 at 19:03
micklmickl
14.3k51639
You can use below aggregation to check whether specified userId exceeded defined limit.
db.vouchers.aggregate([
{
$match: {
$expr: {
$allElementsTrue: {
$map: {
input: "$details.userReachedLimitedQuantity",
as: "user",
in: {
$or: [
{ $ne: [ "$$user.userId", userId ] },
{ $lte: [ "$$user.quantityBrought", "$details.limitedQuantityPerUser" ] }
]
}
}
}
}
}
}
])
$allElementsTrue takes an array of boolean values as a parameter. You can use $map to transform your userReachedLimitedQuantity into such boolean values checking userId and quantityBrought fields.
So if any element is false then it means that that user's quantityBrought is greater than limitedQuantityPerUser
answered Jan 1 at 19:03
micklmickl
14.3k51639
answered Jan 1 at 19:03
micklmickl
14.3k51639
answered Jan 1 at 19:03
micklmickl
14.3k51639
answered Jan 1 at 19:03
micklmickl
14.3k51639
14.3k51639
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above$match?
– mickl
Jan 2 at 17:41
|
show 2 more comments
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above$match?
– mickl
Jan 2 at 17:41
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, thanks for answering. I got this error when trying to use it MongoError: $allElementsTrue's argument must be an array, but is null, I tried to add bracket between { $map } but, still got the same error
– Thomas Kim
Jan 2 at 0:48
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
Hi, do you know how to only project the matched 'user Id' and its 'quantityBrought'. Because of the userReachedLimitedQuantity might get bigger and bigger in time
– Thomas Kim
Jan 2 at 3:05
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
I managed to get your code working by using double [ [ ] ] to wrap the { $map} content.
– Thomas Kim
Jan 2 at 3:26
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
Looks like adding double bracket will not work too, because in documentation [ [ false ] ], "isAllTrue" : true. to me, allElementsTrue doesn corp well with $map
– Thomas Kim
Jan 2 at 4:25
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above
$match ?– mickl
Jan 2 at 17:41
@ThomasKim I've tested my solution on those documents you pasted, I guess that you have some other documents that have a different schema and that's why you're getting this null, any chance that you can filter them out before you apply above
$match ?– mickl
Jan 2 at 17:41
|
show 2 more comments
No a perfect answer, but I did the filter in the result of mongod query
// Only take matched userId's quantityPurchased
const newResult = result.filter(val => {
if (!val.details.userReachedLimitedQuantity) {
return val;
}
val.details.userReachedLimitedQuantity= val.details.userReachedLimitedQuantity.filter((o) => {
return (String(o.userId) == userId)
});
if (val.details.userReachedLimitedQuantity[0].quantityBrou < val.details.limitedQuantityPerUser) {
return val;
} else {
return false;
}
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
add a comment |
No a perfect answer, but I did the filter in the result of mongod query
// Only take matched userId's quantityPurchased
const newResult = result.filter(val => {
if (!val.details.userReachedLimitedQuantity) {
return val;
}
val.details.userReachedLimitedQuantity= val.details.userReachedLimitedQuantity.filter((o) => {
return (String(o.userId) == userId)
});
if (val.details.userReachedLimitedQuantity[0].quantityBrou < val.details.limitedQuantityPerUser) {
return val;
} else {
return false;
}
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
add a comment |
No a perfect answer, but I did the filter in the result of mongod query
// Only take matched userId's quantityPurchased
const newResult = result.filter(val => {
if (!val.details.userReachedLimitedQuantity) {
return val;
}
val.details.userReachedLimitedQuantity= val.details.userReachedLimitedQuantity.filter((o) => {
return (String(o.userId) == userId)
});
if (val.details.userReachedLimitedQuantity[0].quantityBrou < val.details.limitedQuantityPerUser) {
return val;
} else {
return false;
}
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
No a perfect answer, but I did the filter in the result of mongod query
// Only take matched userId's quantityPurchased
const newResult = result.filter(val => {
if (!val.details.userReachedLimitedQuantity) {
return val;
}
val.details.userReachedLimitedQuantity= val.details.userReachedLimitedQuantity.filter((o) => {
return (String(o.userId) == userId)
});
if (val.details.userReachedLimitedQuantity[0].quantityBrou < val.details.limitedQuantityPerUser) {
return val;
} else {
return false;
}
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
answered Jan 2 at 4:46
Thomas KimThomas Kim
8717
8717
add a comment |
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%2f53995878%2fmongodb-compare-two-fields-with-matched-fields-value-aggregate%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
