Hello,
I have time series data as follows and I want to get the sums of amounts between specific dates as rolling sum of each product. The problem occurs in the densify stage when result set doesn’t contain a document within the bounds parameter of densify function, of the given field. It just returns (densifies) for the first document in the result set, you can find the example below. Is it an expected behaviour?
Also, how can I make this work?
Link to playground to reproduce: Mongo playground
[
{
"_id": {
"$oid": "64e84a9ac95e4c4ff915acd6"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
},
"amount": {
"$numberDecimal": "841.12"
},
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
}
},
{
"_id": {
"$oid": "64e84a9bc95e4c4ff915ace4"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
},
"amount": {
"$numberDecimal": "0"
},
"productId": {
"$oid": "64e84a9bc95e4c4ff915ace3"
}
},
{
"_id": {
"$oid": "64e84a9bc95e4c4ff915acee"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
},
"amount": {
"$numberDecimal": "514.379"
},
"productId": {
"$oid": "64e84a9bc95e4c4ff915aced"
}
}
]
All dates are set 2020-01-01T00:00:00.000+00:00 in the given data.
Example scenario, if a user queries the data between 2023-08-06 and 2023-08-09 densify returns result for only one product, omitting the other 2 products as follows:
[{
"amount": {
"$numberDecimal": "841.12"
},
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
}
},
{
"amount": {
"$numberDecimal": "0"
},
"productId": {
"$oid": "64e84a9bc95e4c4ff915ace3"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
}
},
{
"amount": {
"$numberDecimal": "514.379"
},
"productId": {
"$oid": "64e84a9bc95e4c4ff915aced"
},
"timestamp": {
"$date": {
"$numberLong": "1577836800000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691193600000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691280000000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691366400000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691452800000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691539200000"
}
}
},
{
"productId": {
"$oid": "64e84a9ac95e4c4ff915acd5"
},
"timestamp": {
"$date": {
"$numberLong": "1691625600000"
}
}
}]
Here is the query I am using:
{
$densify: {
field: "effectiveDate",
partitionByFields: [
"productId"
],
range: {
step: NumberLong(1),
unit: "day",
bounds: [
ISODate("2023-08-06T00:00:00Z"),
ISODate("2023-08-09T00:00:00Z")
]
}
}
}
Thank you for the help.