Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongo – query for documents in range of date and also the nearest one to this range

I want to query mongo for documents in date range and also I want to get nerest one but outside of this range. For example:
Documents in db:

{id: 1, dDate: 2024.01.10}
{id: 2, dDate: 2024.01.11}
{id: 3, dDate: 2024.01.22}
{id: 4, dDate: 2024.01.23}
{id: 5, dDate: 2024.01.24}
{id: 6, dDate: 2024.01.30}
{id: 7, dDate: 2024.01.31}

I want to query for dates – gte: 2024.01.22 and lte: 2024.01.24
and in a result I want to receive documents:

{id: 2, dDate: 2024.01.11}
{id: 3, dDate: 2024.01.22}
{id: 4, dDate: 2024.01.23}
{id: 5, dDate: 2024.01.24}
{id: 6, dDate: 2024.01.30}

so basicity I want to get documents in range and also the nearest one (in the both sides) to this range

Tags:

2

Answers


  1. 0

    1. $facet – Allow multiple pipelines to be executed in an aggregate query.

      1.1. "inRange" – Get the document with dDate within the search date range.

      1.2. "beforeRange" – Get the first matching document with dDate is before the starting search date range.

      1.3. "afterRange" – Get the first matching document with dDate is after the ending search date range.

    2. $set – Add the docs field by combining the three array fields into a single array.

    3. $unwind – Deconstruct the docs array field into multiple documents.

    4. $replaceWith – Replace the input document with the docs object.

    5. $sort – Order the document by the dDate field ascending.

    db.collection.aggregate([
  {
    $facet: {
      inRange: [
        {
          $match: {
            dDate: {
              $gte: ISODate("2024-01-22"),
              $lte: ISODate("2024-01-24")
            }
          }
        }
      ],
      beforeRange: [
        {
          $match: {
            dDate: {
              $lt: ISODate("2024-01-22")
            }
          }
        },
        {
          $sort: {
            dDate: -1
          }
        },
        {
          $limit: 1
        }
      ],
      afterRange: [
        {
          $match: {
            dDate: {
              $gt: ISODate("2024-01-24")
            }
          }
        },
        {
          $sort: {
            dDate: 1
          }
        },
        {
          $limit: 1
        }
      ]
    }
  },
  {
    $set: {
      docs: {
        $concatArrays: [
          "$beforeRange",
          "$inRange",
          "$afterRange"
        ]
      }
    }
  },
  {
    $unwind: "$docs"
  },
  {
    $replaceWith: "$docs"
  },
  {
    $sort: {
      dDate: 1
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    You can use $setWindowFields to get the previous and next date for each entry. Afterwards, you can decide whether a document should be included in the result by checking whether any of the dates is in your range:

    db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "dDate": 1
      },
      "output": {
        "prev": {
          "$first": "$dDate",
          "window": {
            "documents": [
              -1,
              -1
            ]
          }
        },
        "next": {
          "$first": "$dDate",
          "window": {
            "documents": [
              1,
              1
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      include: {
        $gt: [
          {
            $size: {
              $filter: {
                input: [
                  "$dDate",
                  "$prev",
                  "$next"
                ],
                cond: {
                  $and: [
                    {
                      $gte: [
                        "$$this",
                        ISODate("2024-01-22T00:00:00Z")
                      ]
                    },
                    {
                      $lte: [
                        "$$this",
                        ISODate("2024-01-24T00:00:00Z")
                      ]
                    }
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  },
  {
    $match: {
      include: true
    }
  },
  {
    $unset: [
      "include",
      "next",
      "prev"
    ]
  }
])

    Above aggregation pipeline first sets the prev and next fields to the corresponding dates; then it adds a temporary field include that is set to true if the document should be included in the output. After filtering the documents with a $match stage, the temporary fields are removed from the documents so that the result is ready:

    [
  {
    "_id": 2,
    "dDate": ISODate("2024-01-11T00:00:00Z")
  },
  {
    "_id": 3,
    "dDate": ISODate("2024-01-22T00:00:00Z")
  },
  {
    "_id": 4,
    "dDate": ISODate("2024-01-23T00:00:00Z")
  },
  {
    "_id": 5,
    "dDate": ISODate("2024-01-24T00:00:00Z")
  },
  {
    "_id": 6,
    "dDate": ISODate("2024-01-30T00:00:00Z")
  }
]

    You can check the mongoplayground here.

    The pipeline can be optimized a bit by checking the include-condition directly in the match and omitting the temporary field; however, I hope that the query engine is able to sort this out. I’ve kept the field for demonstration purposes.

    Sorting the data is necessary only once during the $setWindowFields stage.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search