I've trying to create an endpoint in my API which return the youngest and oldest person from my database using mongodb e mongoose.

My database looks like that:

{
"message": "Employees list",
"result": [
    {
        "_id": "62562137bd9acaa47a13e2d2",
        "name": "Anakin Skywalker",
        "email": "[email protected]",
        "department": "Architecture",
        "salary": 4000,
        "birth_date": "1983-01-01T03:00:00.000Z",
        "__v": 0
    },
    {
        "_id": "625873509d27198519c460cb",
        "name": "pedro",
        "email": "[email protected]",
        "department": "Architecture",
        "salary": 700000,
        "birth_date": "1988-01-01T02:00:00.000Z",
        "__v": 0
    },
    {
        "_id": "6258b2bb6f4786b217624c2d",
        "name": "Henrique Skywalker",
        "email": "[email protected]",
        "department": "INfra",
        "salary": 9000,
        "birth_date": "1991-01-01T02:00:00.000Z",
        "__v": 0
    },
    {
        "_id": "6258b3066f4786b217624c31",
        "name": "perycles Skywalker",
        "email": "[email protected]",
        "department": "INfra",
        "salary": 9000000,
        "birth_date": "1990-01-01T02:00:00.000Z",
        "__v": 0
    },
    {
        "_id": "6258c38dca6a1013e80dbd2f",
        "name": "pdsdss Skywalker",
        "email": "[email protected]",
        "department": "INfra",
        "salary": 9000000,
        "birth_date": "1990-01-01T02:00:00.000Z",
        "__v": 0
    },
    {
        "_id": "625cbdae195853285e21fead",
        "name": "leia Skywalker",
        "email": "[email protected]",
        "department": "Data",
        "salary": 4500,
        "birth_date": "1978-01-01T03:00:00.000Z",
        "__v": 0
    }
]

And I need a query response like this one bellow:

{
  "younger": {
    "id": "1",
    "name": "Anakin Skywalker",
    "email": "[email protected]",
    "department": "Architecture",
    "salary": "4000.00",
    "birth_date": "01-01-1983"},
  "older": {
    "id": "2",
    "name": "Obi-Wan Kenobi",
    "email": "[email protected]",
    "department": "Back-End",
    "salary": "3000.00",
    "birth_date": "01-01-1977"},
  "average": "40.00"
}

Nonetheless I don't be able to customize the data form and sort it as well. I appreciate any help in advantage!

CodePudding user response：

db.getCollection('').find({}).sort({birth_date:-1})

This will sort your collection in descending order by birth_date

CodePudding user response：

Find max or min then filter the array.

db.collection.aggregate([
  {
    $match: { message: "Employees list" }
  },
  {
    $project: {
      younger: {
        $first: {
          $filter: {
            input: "$result",
            as: "r",
            cond: { $eq: [ "$$r.birth_date", { $max: "$result.birth_date" } ] }
          }
        }
      },
      older: {
        $first: {
          $filter: {
            input: "$result",
            as: "r",
            cond: { $eq: [ "$$r.birth_date", { $min: "$result.birth_date" } ] }
          }
        }
      },
      avg: {
        $subtract: [
          { $year: "$$NOW" },
          {
            $year: {
              $toDate: {
                $avg: {
                  $map: {
                    input: "$result",
                    as: "r",
                    in: { $toLong: { $toDate: "$$r.birth_date" } }
                  }
                }
              }
            }
          }
        ]
      }
    }
  }
])

mongoplayground