Hi to everyone,
I have a schema like this: {fruit_type: string, sale_date: [data: ISODate, data: ISODate, …]}
For query, in input i have two date. In output i wuold like all days between the input dates and true or false if this days match the sale_date of my fruit, like this: ‘day1’:True, day2:False, …
You do not hesitate to write to me if it is not clear.
I’m using the aggregation pipeline with Compass.
Thank you so much
`
Hi @Buz96,
Could you provide the following so that it may clarify what you are after:
I would also advise going over the following post to make it easier to read any code / logs : Formatting code and log snippets in posts.
Best Regards,
Jason
Hi @Jason_Tran ,
My MongoDB version is 5.0.5.
Sample input document: { fruit_type:"banana", sale_date:[{date:"2022-01-12"},{date:"2022-01-14"},{date:"2022-01-15"},{date:"2022-01-18"}]}
Expected outup: if i have two dates, for example: “2022-01-10” and “2022-01-17”, i would like this output:
{ day:"2022-01-10", check_sale: False #because I have not sold bananas on this date }
{ day:"2022-01-11", check_sale: False #because I have not sold bananas on this date }
{ day:"2022-01-12", check_sale: True #because I have sold bananas on this date }
{ day:"2022-01-13", check_sale: False #because I have not sold bananas on this date }
{ day:"2022-01-14", check_sale: False #because I have not sold bananas on this date }
{ day:"2022-01-15", check_sale: True #because I have sold bananas on this date }
{ day:"2022-01-16", check_sale: False #because I have not sold bananas on this date }
{ day:"2022-01-17", check_sale: False #because I have not sold bananas on this date }
Now is more clear? Thank you so much.
Best Regards,
Buz
First, having date as string rather than the data data type is not a good idea. String takes more space, are slower to compare and you cannot use the rich date API.
What you are trying to do is probably easier to do in your application code rather that try to do it on the server. Also doing on the server will increase the data transferred from the server to the application.
To transform an array from one form to the other you need to use $map.
The issue you have is to generate all the days between your 2 dates because you are using string. Your example is easy because you are within the same month. Difficulties will appears when your start and end date is in different months, worst when it start in February. All these difficulties are already solve if you use date data type.
Initially you said that you were using the date type:
But your sample data shows a string "2022-01-10" - now you can convert that to proper ISODate by using $toDate expression, but I’m hoping maybe it’s just a typo and the dates are already stored as dates and not strings. In either case, there are a lot of different ways this can be done, here is one way you can do what you describe (and I’m assuming you want separate documents at the end rather than an array of dates like the original input has):
Input:
db.fruit_sales.find()
{ "_id" : ObjectId("6247365a49291a2f1d0019b8"), "fruit_type" : "banana", "sale_date" : [ { "date" : ISODate("2022-01-12T00:00:00Z") }, { "date" : ISODate("2022-01-14T00:00:00Z") }, { "date" : ISODate("2022-01-15T00:00:00Z") }, { "date" : ISODate("2022-01-18T00:00:00Z") } ] }
Pipeline and result:
db.fruit_sales.aggregate([
{$set:{
startDate:{$min:"$sale_date.date"},
endDate:{$max:"$sale_date.date"}
}},
{$set:{
dates:{$map:{input:{$range:[0, {$subtract:[{$add:[1,"$endDate"]}, "$startDate"]}, 1000*60*60*24]}, in:{$add:["$startDate","$$this"]}}}
}},
{$unwind:"$dates"},
{$project:{
_id:0,
day:"$dates",
check_sale:{$in:["$dates", "$sale_date.date"]}
}}
])
{ "day" : ISODate("2022-01-12T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-13T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-14T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-15T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-16T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-17T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-18T00:00:00Z"), "check_sale" : true }
There are many different ways to do this type of thing, this is just one of them - generate dates between start and end of the time period and then check for each date whether it’s in the array of sales dates.
Asya
First of all, thak you @Asya_Kamsky and @steevej for reply me.
I have showed data as string for semplicity but in my document it is a ISODate type.
I have tried the code @Asya_Kamsky and it’s works for dates like "2022-01-10" and "2022-01-17", but it doesn’t work if i change month and year (as anticipated by @steevej ). Any one can help me?
When working with string or numbers. When working with date data type adding
1000*60*60*24
should leads the next day what ever you change month or year.
JS example in mongosh:
mongosh> december_31_2022 = new Date( "2022-12-31")
2022-12-31T05:00:00.000Z
mongosh> next = new Date( december_31_2022.valueOf() + 1000*60*60*24 )
2023-01-01T05:00:00.000Z
Same logic in $set:
mongosh> c.insertOne( { _id : 0 , "day" : december_31_2022 })
{ acknowledged: true, insertedId: 0 }
mongosh> c.find()
{ _id: 0, day: 2022-12-31T00:00:00.000Z }
mongosh> c.aggregate( { "$set" : { "next_day" : { "$add" : [ "$day" , 1000*60*60*24]}}})
{ _id: 0,
day: 2022-12-31T00:00:00.000Z,
next_day: 2023-01-01T00:00:00.000Z }
So either your code is different or your dates are not ISO dates.
Post real documents, your code and the wrong results with month or year crossing.
Ok, i try to explain my aim.
My Input is:{"_id":{"$oid":"624d987e1924b4a722dbbd5f"}, "fruit_type":"banana", "sale_date": [{"date":{"$date":"2022-01-12T00:00:00.000Z"}},{"date":{"$date":"2022-01-13T00:00:00.000Z"}}, {"date":{"$date":"2022-01-14T00:00:00.000Z"}},{"date":{"$date":"2022-01-14T00:00:00.000Z"}}, {"date":{"$date":"2022-01-15T00:00:00.000Z"}},{"date":{"$date":"2022-02-12T00:00:00.000Z"}}, {"date":{"$date":"2022-02-13T00:00:00.000Z"}},{"date":{"$date":"2022-02-14T00:00:00.000Z"}}, {"date":{"$date":"2022-02-15T00:00:00.000Z"}},{"date":{"$date":"2021-01-12T00:00:00.000Z"}}, {"date":{"$date":"2021-01-13T00:00:00.000Z"}},{"date":{"$date":"2021-01-14T00:00:00.000Z"}}, {"date":{"$date":"2021-01-15T00:00:00.000Z"}}]}
and it represents every day that I have sold a banana.
Now i want to know if between two dates i have sold a banana, so if my two dates are 2021-01-15 and 2021-01-20, (i use this dates for an easy example, but in reality the dates can differ both by months and by years), and i want to know how many bananas i have sold (If a day i have sold two bananas in the array sale_date i have two object date with the same value).
How solutions i’m using this process:
1)set the dates:
[{$set: {
startDate: ISODate('2022-01-13T00:00:00.000Z'),
endDate: ISODate('2022-01-17T00:00:00.000Z')
}},
$set: {
dates: {
$map: {
input: {
$range: [
0,
{
$subtract: [{
$add: [
1,
'$endDate'
]
},
'$startDate'
]
},
1000*60*60*24
]
},
'in': {
$add: [
'$startDate',
'$$this'
]
}
}
}
}
}
{
$project: {
_id: 1,
fruit_type: 1,
startDate: 1,
endDate: 1,
sale_date: {
$filter: {
input: '$sale_date',
as: 'info_sales',
cond: {
$and: [{
$gte: [
'$$info_sales.date',
'$startDate'
]
},
{
$lt: [
'$$info_sales.date',
'$endDate'
]
}
]
}
}
},
dates: 1
}
}
{
$unwind: {
path: '$dates'
}
}
4.1) So, to sum up now my documents is like this, i have one document for every days between startDate and endDate
fruit_type:"banana"
startDate:2022-01-13T00:00:00.000+00:00
endDate:2022-01-17T00:00:00.000+00:00
dates:2022-01-13T00:00:00.000+00:00
sale_date:Array
date:2022-01-13T00:00:00.000+00:00
...(other date)
dates exist in sale_date and than found how many bananas i have sold that day?Hi, to every one. I found a soluton for my pipeline. The only problem that i have is when i try to create an array with all days between the two dates. If i have two date in the same month it’s ok, but if i use dirrent month or year, the code doesn’t works.
The code already supply if used with ISODate as startDate and endDate should work correctly. Has adding 246060*1000 to a date gives the next day what ever you cross a month or a year.
Publish your exact code with your real sample documents and we will help find what is wrong.
I have created a new topic with the specific problem that i have with my code at this link: Press here.
Thanks you @steevej
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.