Mongodb: match based on dynamic dates with $lookup





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







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!










share|improve this question

























  • 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 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











  • Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }

    – Claudio Ɯǝıs Mulas
    Nov 21 '18 at 23:47


















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!










share|improve this question

























  • 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 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











  • Found it! { "$cond": [ { "$lt": [ "$step.date", { "$add" : ["$date", 21600000]} ] }, "$$KEEP", "$$PRUNE" ] }

    – Claudio Ɯǝıs Mulas
    Nov 21 '18 at 23:47














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!










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 21 '18 at 23:20







Claudio Ɯǝıs Mulas

















asked Nov 21 '18 at 22:59









Claudio Ɯǝıs MulasClaudio Ɯǝıs Mulas

5272921




5272921













  • 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 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











  • 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











  • 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













  • 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












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


















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














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





















































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







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)