Mixing
Find based on an element of subdocument array matching an element of another subdocument array
How do I write mongodb queries including conditions such as "any of one subdocument matches any of another subdocument?"
For instance, say I have a structure like this:
{
personNumber: 3,
dataSet1:
[
{ date: new Date(2018,4,1), // ... }
{ date: new Date(2018,5,1), // ... }
],
dataSet2:
[
{ fromDate: new Date(2018,3,1), toDate: new Date(2018,5,1), //... }
{ fromDate: new Date(2018,7,1), toDate: new Date(2018,7,2), //... }
]
}
How do I perform queries like:
- People who have an element of dataSet1, with a date that falls between the fromDate and toDate of any element of their dataSet2
And - People who have an element of dataSet2, that do NOT have an element of dataSet1 with a date that falls between the fromDate and toDate?
Based on some info I found, I tried this:
db.test.find(
{
$expr:
{
$and:
[
{ $gt: [ "$dataSet1.date", "$dataSet2.fromDate" ] },
{ $lt: [ "$dataSet1.date", "$dataSet2.toDate" ] }
]
}
}
);
But that seems to always return 0 results. If I remove the and I get something:
db.test.find(
{
$expr:
{
$gt: [ "$dataSet1.date", "$dataSet2.fromDate" ]
}
}
);
Which makes me suspect that the format of the and is wrong or I need to be using elemMatch instead somehow.
But then I still don't know how to go about the second part -- where there is NOT a match. Is there a good way to do these sorts of queries in mongodb?
mongodb mongodb-query
asked Jan 2 at 15:25
kwkkwk
61
add a comment |
How do I write mongodb queries including conditions such as "any of one subdocument matches any of another subdocument?"
For instance, say I have a structure like this:
{
personNumber: 3,
dataSet1:
[
{ date: new Date(2018,4,1), // ... }
{ date: new Date(2018,5,1), // ... }
],
dataSet2:
[
{ fromDate: new Date(2018,3,1), toDate: new Date(2018,5,1), //... }
{ fromDate: new Date(2018,7,1), toDate: new Date(2018,7,2), //... }
]
}
How do I perform queries like:
- People who have an element of dataSet1, with a date that falls between the fromDate and toDate of any element of their dataSet2
And - People who have an element of dataSet2, that do NOT have an element of dataSet1 with a date that falls between the fromDate and toDate?
Based on some info I found, I tried this:
db.test.find(
{
$expr:
{
$and:
[
{ $gt: [ "$dataSet1.date", "$dataSet2.fromDate" ] },
{ $lt: [ "$dataSet1.date", "$dataSet2.toDate" ] }
]
}
}
);
But that seems to always return 0 results. If I remove the and I get something:
db.test.find(
{
$expr:
{
$gt: [ "$dataSet1.date", "$dataSet2.fromDate" ]
}
}
);
Which makes me suspect that the format of the and is wrong or I need to be using elemMatch instead somehow.
But then I still don't know how to go about the second part -- where there is NOT a match. Is there a good way to do these sorts of queries in mongodb?
mongodb mongodb-query
asked Jan 2 at 15:25
kwkkwk
61
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07
add a comment |
How do I write mongodb queries including conditions such as "any of one subdocument matches any of another subdocument?"
For instance, say I have a structure like this:
{
personNumber: 3,
dataSet1:
[
{ date: new Date(2018,4,1), // ... }
{ date: new Date(2018,5,1), // ... }
],
dataSet2:
[
{ fromDate: new Date(2018,3,1), toDate: new Date(2018,5,1), //... }
{ fromDate: new Date(2018,7,1), toDate: new Date(2018,7,2), //... }
]
}
How do I perform queries like:
- People who have an element of dataSet1, with a date that falls between the fromDate and toDate of any element of their dataSet2
And - People who have an element of dataSet2, that do NOT have an element of dataSet1 with a date that falls between the fromDate and toDate?
Based on some info I found, I tried this:
db.test.find(
{
$expr:
{
$and:
[
{ $gt: [ "$dataSet1.date", "$dataSet2.fromDate" ] },
{ $lt: [ "$dataSet1.date", "$dataSet2.toDate" ] }
]
}
}
);
But that seems to always return 0 results. If I remove the and I get something:
db.test.find(
{
$expr:
{
$gt: [ "$dataSet1.date", "$dataSet2.fromDate" ]
}
}
);
Which makes me suspect that the format of the and is wrong or I need to be using elemMatch instead somehow.
But then I still don't know how to go about the second part -- where there is NOT a match. Is there a good way to do these sorts of queries in mongodb?
mongodb mongodb-query
asked Jan 2 at 15:25
kwkkwk
61
How do I write mongodb queries including conditions such as "any of one subdocument matches any of another subdocument?"
For instance, say I have a structure like this:
{
personNumber: 3,
dataSet1:
[
{ date: new Date(2018,4,1), // ... }
{ date: new Date(2018,5,1), // ... }
],
dataSet2:
[
{ fromDate: new Date(2018,3,1), toDate: new Date(2018,5,1), //... }
{ fromDate: new Date(2018,7,1), toDate: new Date(2018,7,2), //... }
]
}
How do I perform queries like:
- People who have an element of dataSet1, with a date that falls between the fromDate and toDate of any element of their dataSet2
And - People who have an element of dataSet2, that do NOT have an element of dataSet1 with a date that falls between the fromDate and toDate?
Based on some info I found, I tried this:
db.test.find(
{
$expr:
{
$and:
[
{ $gt: [ "$dataSet1.date", "$dataSet2.fromDate" ] },
{ $lt: [ "$dataSet1.date", "$dataSet2.toDate" ] }
]
}
}
);
But that seems to always return 0 results. If I remove the and I get something:
db.test.find(
{
$expr:
{
$gt: [ "$dataSet1.date", "$dataSet2.fromDate" ]
}
}
);
Which makes me suspect that the format of the and is wrong or I need to be using elemMatch instead somehow.
But then I still don't know how to go about the second part -- where there is NOT a match. Is there a good way to do these sorts of queries in mongodb?
mongodb mongodb-query
mongodb mongodb-query
asked Jan 2 at 15:25
kwkkwk
61
asked Jan 2 at 15:25
kwkkwk
61
asked Jan 2 at 15:25
kwkkwk
61
asked Jan 2 at 15:25
kwkkwk
61
asked Jan 2 at 15:25
kwkkwk
61
61
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07
add a comment |
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07
add a comment |
0
active
oldest
votes
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%2f54008941%2ffind-based-on-an-element-of-subdocument-array-matching-an-element-of-another-sub%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54008941%2ffind-based-on-an-element-of-subdocument-array-matching-an-element-of-another-sub%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
I came up with the idea of using aggregate with a pair of unwinds (1 for dataSet1, 1 for dataSet2) to get the cross product of every combination, and then doing a match on that. It works but it's slow.
– kwk
Jan 7 at 16:07