CODEWITHSUNDEEP
mongodbaggregation-framework
pshemu
pshemu

Reputation: 169

MongoDB filling missing results

Within my aggregation pipeline, I get to the point where I may get the following intermediate documents:

{ date: "01-01-2018", value: 1 }
{ date: "04-01-2018", value: 2 }
{ date: "07-01-2018", value: 3 }

Depending on the data I have, I may get (as shown above) missing data points

Is there a way in my aggregation pipeline to provide some default documents (or default values like nulls) for the missing days?

Such that I'd get:

{ date: "01-01-2018", value: 1 }
{ date: "02-01-2018", value: null }
{ date: "03-01-2018", value: null }
{ date: "04-01-2018", value: 2 }
{ date: "05-01-2018", value: null }
{ date: "06-01-2018", value: null }
{ date: "07-01-2018", value: 3 }

Maybe $addToSet operator, but how can I add those artificial docs?

Is filling the gaps in business logic the only way?

Upvotes: 2

Views: 834

Answers (2)

Xavier Guihot
Xavier Guihot

Reputation: 61666

Starting in Mongo 5.1, it's a perfect use case for the new $densify aggregation operator:

// { date: ISODate("2018-01-01"), value: 1 }
// { date: ISODate("2018-01-04"), value: 2 }
// { date: ISODate("2018-01-07"), value: 3 }
db.test.aggregate([
  { $densify: {
    field: "date",
    range: { step: 1, unit: "day", bounds: "full" }
  }}
])
// { date: ISODate("2018-01-01"), value: 1 }
// { date: ISODate("2018-01-02") }
// { date: ISODate("2018-01-03") }
// { date: ISODate("2018-01-04"), value: 2 }
// { date: ISODate("2018-01-05") }
// { date: ISODate("2018-01-06") }
// { date: ISODate("2018-01-07"), value: 3 }

This densifies documents ($densify) by creating new documents in a sequence of documents where certain values for a field (in our case field: "date") are missing:

  • the step for our densification is 1 day: range: { step: 1, unit: "day", ... }
  • and we densify within the range of dates defined by our documents: bounds: "full"

Of course, to make this work with your specific example, you can switch from dates to strings with $dateToString and back to dates with $dateFromString.

Upvotes: 3

Sagar Chaudhary
Sagar Chaudhary

Reputation: 1403

You can use the below query:

db.collection.aggregate([
 {"$addFields":{ "datefield":"$datefield"}}, 
])

I think this will add null values if the field does not exist in a document. However artificial docs cannot be added in mongo.

Upvotes: 0

Related Questions