Given the following data in a Mongo collection:

    {
        _id: "1",
        dateA: ISODate("2021-12-31T00:00.000Z"),
        dateB: ISODate("2022-01-11T00:00.000Z")
    },
    {
        _id: "2",
        dateA: ISODate("2022-01-02T00:00.000Z"),
        dateB: ISODate("2022-01-08T00:00.000Z")
    },
    {
        _id: "3",
        dateA: ISODate("2022-01-03T00:00.000Z"),
        dateB: ISODate("2022-01-05T00:00.000Z")
    },
    {
        _id: "4",
        dateA: ISODate("2022-01-09T00:00.000Z"),
        dateB: null
    },
    {
        _id: "5",
        dateA: ISODate("2022-01-11T00:00.000Z"),
        dateB: ISODate("2022-01-11T00:00.000Z")
    },
    {
        _id: "6",
        dateA: ISODate("2022-01-12T00:00.000Z"),
        dateB: null
    }

And given the range below:

ISODate("2022-01-01T00:00.000Z") .. ISODate("2022-01-10T00:00.000Z")

I want to find all values with dateA within given range, then I want to decrease the range starting it from the max dateB value, and finally fetching all documents that doesn't contain dateB.

In resume:

I'll start with range

ISODate("2022-01-01T00:00.000Z") .. ISODate("2022-01-10T00:00.000Z")

Then change to range

ISODate("2022-01-08T00:00.000Z") .. ISODate("2022-01-10T00:00.000Z")

Then find with

dateB: null

Finally, the result would be the document with

_id: "4"

Is there a way to find the document with _id: "4" in just one aggregate?

I know how to do it programmatically using 2 queries, but the main goal is to have just one request to the database.

CodePudding user response：

You can use $max to find the maxDateB first. Then perform a self $lookup to apply the $match and find doc _id: "4".

db.collection.aggregate([
  {
    $match: {
      dateA: {
        $gte: ISODate("2022-01-01"),
        $lt: ISODate("2022-01-10")
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "maxDateB": {
        "$max": "$dateB"
      }
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        start: "$maxDateB",
        end: ISODate("2022-01-10")
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $gte: [
                    "$dateA",
                    "$$start"
                  ]
                },
                {
                  $lt: [
                    "$dateA",
                    "$$end"
                  ]
                },
                {
                  $eq: [
                    "$dateB",
                    null
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "result"
    }
  },
  {
    "$unwind": "$result"
  },
  {
    "$replaceRoot": {
      "newRoot": "$result"
    }
  }
])

Here is the Mongo Playground for your

CodePudding user response：

Assuming the matched initial dateA range is not huge, here is alternate approach that exploits $push and $filter and avoids the hit of a $lookup stage:

db.foo.aggregate([
    {$match: {dateA: {$gte: new ISODate("2022-01-01"), $lt: new ISODate("2022-01-10")} }},

    // Kill 2 birds with one stone here.  Get the max dateB AND prep              
    // an array to filter later.  The items array will be as large                
    // as the match above but the output of this stage is a single doc:           
    {$group: {_id: null,
              maxDateB: {$max: "$dateB" },
              items: {$push: "$$ROOT"}
             }},

    {$project: {X: {$filter: {
        input: "$items",
        cond: {$and: [
                // Each element of 'items' is passed as $$this so use
                // dot notation to get at individual fields.  Note that
                // all other peer fields to 'items' like 'maxDateB' are
                // in scope here and addressable using '$':
                {$gt: [ "$$this.dateA", "$maxDateB"]},
                {$eq: [ "$$this.dateB", null ]}
               ]}
        }}
    }}
]);

This yields a single doc result (I added an additional doc _id 41 to test the null equality for more than 1 doc):

{
    "_id" : null,
    "X" : [
        {
            "_id" : "4",
            "dateA" : ISODate("2022-01-09T00:00:00Z"),
            "dateB" : null
        },
        {
            "_id" : "41",
            "dateA" : ISODate("2022-01-09T00:00:00Z"),
            "dateB" : null
        }
    ]
}

It is possible to $unwind and $replaceRoot after this but there is little need to do so.