Mongodb: match based on dynamic dates with $lookup
0
I have a collection similar to this
[
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 12:20:00"
},
{
"departure": "MIA",
"arrival": "LAX",
"date": "2018-11-22 19:20:00"
},
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 23:20:00"
}
]
I need to get all results that makes me a combination to reach from NYC, LAX.
So, it should get
a.departure = NYC
a.arrival = b.departure
b.departure = LAX
I was able to do this with a lookup (creating a self connection) + 2 matches:
{
"$lookup": {
"from": "routes",
"localField": "arrival",
"foreignField": "departure",
"as": "step"
}
},
{
"$match": {
"$and": [
{
"step.destination": "LAX"
}
]
}
},
{
"$match": {
"$and": [
{
"departure": "NYC"
}
]
}
}
The problem now is that I want to add a rule based on the date.
Date should not be longer than 3 hours.
I've tried with $match (in a lot of different combinations) without success.
{
"$match": {
"$and": [
{
"step.departure_date": {
"$gte": "$date",
"$lte": new Date($date.getTime() + 3 * 60 * 60 * 1000)
}
}
]
}
}
Any idea? Thanks!
mongodb mongodb-query
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
|
show 1 more comment
0
I have a collection similar to this
[
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 12:20:00"
},
{
"departure": "MIA",
"arrival": "LAX",
"date": "2018-11-22 19:20:00"
},
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 23:20:00"
}
]
I need to get all results that makes me a combination to reach from NYC, LAX.
So, it should get
a.departure = NYC
a.arrival = b.departure
b.departure = LAX
I was able to do this with a lookup (creating a self connection) + 2 matches:
{
"$lookup": {
"from": "routes",
"localField": "arrival",
"foreignField": "departure",
"as": "step"
}
},
{
"$match": {
"$and": [
{
"step.destination": "LAX"
}
]
}
},
{
"$match": {
"$and": [
{
"departure": "NYC"
}
]
}
}
The problem now is that I want to add a rule based on the date.
Date should not be longer than 3 hours.
I've tried with $match (in a lot of different combinations) without success.
{
"$match": {
"$and": [
{
"step.departure_date": {
"$gte": "$date",
"$lte": new Date($date.getTime() + 3 * 60 * 60 * 1000)
}
}
]
}
}
Any idea? Thanks!
mongodb mongodb-query
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they sayISODateor if they are just plain strings. Kind of "looks like" they might be "strings".
– Neil Lunn
Nov 21 '18 at 23:22
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47
|
show 1 more comment
0
0
0
I have a collection similar to this
[
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 12:20:00"
},
{
"departure": "MIA",
"arrival": "LAX",
"date": "2018-11-22 19:20:00"
},
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 23:20:00"
}
]
I need to get all results that makes me a combination to reach from NYC, LAX.
So, it should get
a.departure = NYC
a.arrival = b.departure
b.departure = LAX
I was able to do this with a lookup (creating a self connection) + 2 matches:
{
"$lookup": {
"from": "routes",
"localField": "arrival",
"foreignField": "departure",
"as": "step"
}
},
{
"$match": {
"$and": [
{
"step.destination": "LAX"
}
]
}
},
{
"$match": {
"$and": [
{
"departure": "NYC"
}
]
}
}
The problem now is that I want to add a rule based on the date.
Date should not be longer than 3 hours.
I've tried with $match (in a lot of different combinations) without success.
{
"$match": {
"$and": [
{
"step.departure_date": {
"$gte": "$date",
"$lte": new Date($date.getTime() + 3 * 60 * 60 * 1000)
}
}
]
}
}
Any idea? Thanks!
mongodb mongodb-query
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
I have a collection similar to this
[
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 12:20:00"
},
{
"departure": "MIA",
"arrival": "LAX",
"date": "2018-11-22 19:20:00"
},
{
"departure": "NYC",
"arrival": "MIA",
"date": "2018-11-22 23:20:00"
}
]
I need to get all results that makes me a combination to reach from NYC, LAX.
So, it should get
a.departure = NYC
a.arrival = b.departure
b.departure = LAX
I was able to do this with a lookup (creating a self connection) + 2 matches:
{
"$lookup": {
"from": "routes",
"localField": "arrival",
"foreignField": "departure",
"as": "step"
}
},
{
"$match": {
"$and": [
{
"step.destination": "LAX"
}
]
}
},
{
"$match": {
"$and": [
{
"departure": "NYC"
}
]
}
}
The problem now is that I want to add a rule based on the date.
Date should not be longer than 3 hours.
I've tried with $match (in a lot of different combinations) without success.
{
"$match": {
"$and": [
{
"step.departure_date": {
"$gte": "$date",
"$lte": new Date($date.getTime() + 3 * 60 * 60 * 1000)
}
}
]
}
}
Any idea? Thanks!
mongodb mongodb-query
mongodb mongodb-query
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
edited Nov 21 '18 at 23:20
Claudio Ɯǝıs Mulas
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
asked Nov 21 '18 at 22:59
Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas
5102921
5102921
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they sayISODateor if they are just plain strings. Kind of "looks like" they might be "strings".
– Neil Lunn
Nov 21 '18 at 23:22
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47
|
show 1 more comment
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they sayISODateor if they are just plain strings. Kind of "looks like" they might be "strings".
– Neil Lunn
Nov 21 '18 at 23:22
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they say
ISODate or if they are just plain strings. Kind of "looks like" they might be "strings".– Neil Lunn
Nov 21 '18 at 23:22
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they say
ISODate or if they are just plain strings. Kind of "looks like" they might be "strings".– Neil Lunn
Nov 21 '18 at 23:22
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47
|
show 1 more 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
});
}
});
draft saved
draft discarded
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%2f53421638%2fmongodb-match-based-on-dynamic-dates-with-lookup%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53421638%2fmongodb-match-based-on-dynamic-dates-with-lookup%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
Is this a "self join" on the same collection? What actually are you expecting as a result based on the data you provide? Note nothing you actually provide here seems to meet your criteria. Unless of course what you actually mean is the flights "connect" going to the destination. i.e from NYC to MIA then connecting to LAX as a destination from the MIA stopover. You really need to clear up that intent in the question
– Neil Lunn
Nov 21 '18 at 23:12
Yes, sorry if I didn't specify. Is exactly this, I need to get the connection to reach a location. So far, I was able to get this information but not to create a rule based on the time.
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:19
So ideal output would be the two documents showing starting at NYC, and then showing the "flight(s)" ( plural where a connection would be required ) which would be taken in order to arrive at the destination LAX within a certain time period? Also what do those dates actually look like in your database? Ideally you should use the mongo shell and be able to clearly see if they say
ISODateor if they are just plain strings. Kind of "looks like" they might be "strings".– Neil Lunn
Nov 21 '18 at 23:22
It's ISODate. Just checked
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:38
Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }
– Claudio Ɯǝıs Mulas
Nov 21 '18 at 23:47