How to bring age group base data from collection in mongodb i.e how many people are 0-18, 19-24, 25-34, 35

[
  {
    "_id": ObjectId("608be7c608c7de2367c89638"),
    "status": true,
    "gender": "Male",
    "first_name": "Vinter",
    "last_name": "R",
    "dob": "1-2-1999"
  },
  {
    "_id": ObjectId("608be7c608c7de2267c89639"),
    "status": true,
    "gender": "Male",
    "first_name": "Ray",
    "last_name": "Morgan",
    "dob": "1-2-2015"
  }....
]

See the mongo playground https://mongoplayground.net/p/4ydNg9Plh6P

CodePudding user response：

Interesting question!

Would like to credit to @Takis and @YuTing.

1. Good hint from @Takis's comment on $bucket.

2. @YuTing's answer is good.

Think this answer is shorter by utilizing the feature provided by MongoDB.

$toDate - Convert date string to Date (supported for version 4.0 above).

$dateDiff - Date subtraction and get the unit (Supported in version 5).

$$CURRENT - Variable to get the current iterated document. For adding into persons array field (via $push).

$switch - To display group value based on conditions (Optional).

db.collection.aggregate([
  {
    "$addFields": {
      "age": {
        $dateDiff: {
          startDate: {
            $toDate: "$dob"
          },
          endDate: "$$NOW",
          unit: "year"
        }
      }
    }
  },
  {
    $bucket: {
      groupBy: "$age",
      // Field to group by
      boundaries: [
        0,
        19,
        25,
        35
      ],
      // Boundaries for the buckets
      default: "Other",
      // Bucket id for documents which do not fall into a bucket
      output: {
        // Output for each bucket
        "count": {
          $sum: 1
        },
        "persons": {
          $push: "$$CURRENT"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      group: {
        $switch: {
          branches: [
            {
              case: {
                $lt: [
                  "$_id",
                  19
                ]
              },
              then: "0-18"
            },
            {
              case: {
                $lt: [
                  "$_id",
                  25
                ]
              },
              then: "19-24"
            },
            {
              case: {
                $lt: [
                  "$_id",
                  35
                ]
              },
              then: "25-34"
            }
          ],
          default: "35 "
        }
      },
      count: 1,
      persons: 1
    }
  }
])

Sample Mongo Playground

CodePudding user response：

use $bucket

db.collection.aggregate([
  {
    $bucket: {
      groupBy: {
        "$subtract": [
          {
            $year: new Date()
          },
          {
            $toInt: {
              $substr: [
                "$dob",
                {
                  $subtract: [
                    {
                      $strLenCP: "$dob"
                    },
                    4
                  ]
                },
                4
              ]
            }
          }
        ]
      },
      // Field to group by
      boundaries: [
        0,
        19,
        25,
        35,
        100
      ],
      // Boundaries for the buckets
      default: "Other",
      // Bucket id for documents which do not fall into a bucket
      output: {
        // Output for each bucket
        "count": {
          $sum: 1
        },
        "artists": {
          $push: {
            "name": {
              $concat: [
                "$first_name",
                " ",
                "$last_name"
              ]
            },
            "age": {
              "$subtract": [
                {
                  $year: new Date()
                },
                {
                  $toInt: {
                    $substr: [
                      "$dob",
                      {
                        $subtract: [
                          {
                            $strLenCP: "$dob"
                          },
                          4
                        ]
                      },
                      4
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
])

mongoplayground