The data of a collection (named type_properties) in the MongoDB database is as follows:

[
    { type: "agency", properties: 4 },
    { type: "host", properties: 5 },
    { type: "agency", properties: 4 },
    { type: "landlord", properties: 5 },
    { type: "agency", properties: 8 },
    { type: "tenant", properties: 2 },
    { type: "host", properties: 1 },
    { type: "host", properties: 1 },
    { type: "agency", properties: 8 },
    { type: "host", properties: 9 },
    { type: "host", properties: 5 },
    { type: "agency", properties: 1 },
    { type: "agency", properties: 2 },
    { type: "tenant", properties: 2 },
    { type: "agency", properties: 1 },
    { type: "tenant", properties: 4 },
    { type: "tenant", properties: 7 }
]

Requirement: Group by type and count 5 indicators within the group, as follows:
count： The number of records within the group, taking tenant as an example, is 4
“0 properties”: The number of records with PROPERTIES=0 within the group is 0 in this example
“1 property”: The number of records with PROPERTIES=1 within the group is 0 in this example
“2 properties”: The number of records with PROPERTIES=2 within the group is 2 in this example
“3 or more properties”: The number of records with Properties>=3 within the group is 2 in this example

The expected results are as follows:

[
    {
        type: "tenant",
        count: 4, 		// THERE ARE 4 TENANTS TOTAL
        "0 properties": 0, 	// 0 TENANTS WHERE PROPERTIES = 0
        "1 property": 0, 	// 0 TENANTS WHERE PROPERTIES = 1
        "2 properties": 2, 	// 2 TENANTS WHERE PROPERTIES = 2
        "3 or more properties": 2 	// 2 TENANTS WHERE PROPERTIES >= 3
    },
    {
        type: "landlord",
        count: 1, 
        "0 properties": 0,
        "1 property": 0,
        "2 properties": 0,
        "3 or more properties": 1
    },
    {
        type: "agency",
        count: 7, 
        "0 properties": 0,
        "1 property": 2,
        "2 properties": 1,
        "3 or more properties": 4
    },
    {
        type: "host",
        count: 5, 
        "0 properties": 0,
        "1 property": 2,
        "2 properties": 0,
        "3 or more properties": 3
    }
]

MongoDB query statement is lengthy and cumbersome. A relatively simple way to write it is as follows:

db.type_properties.aggregate([
  {
    $group: {
      _id: "$type",
      count: { $count: {} },
      num_properties: { $push: "$properties" }
    }
  },
  {
    $set: {
      "0 properties": {
        $size: {
          $filter: { input: "$num_properties", cond: { $eq: ["$$this", 0] } }
        }
      },
      "1 property": {
        $size: {
          $filter: { input: "$num_properties", cond: { $eq: ["$$this", 1] } }
        }
      },
      "2 properties": {
        $size: {
          $filter: { input: "$num_properties", cond: { $eq: ["$$this", 2] } }
        }
      },
      "3 or more properties": {
        $size: {
          $filter: { input: "$num_properties", cond: { $gte: ["$$this", 3] } }
        }
      },
      type: "$_id",
      _id: "$$REMOVE",
      num_properties: "$$REMOVE"
    }
  }
])

The count function provided by SPL can take filtering conditions as parameters, so it can implement it in one line of grouping code:

	A
1	=mongo_open@d("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'type_properties','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.groups(type;count(1):count,count(properties==0):'0 properties', count(properties==1):'1 properties', count(properties==2):'2 properties', count(properties>=3):'3 or more properties')
5	=json(A4)

The first three lines are readings, and the calculation code is only A4. A5 converts the results back to the json format, and the output results are identical to the original requirements.

Question source：https://stackoverflow.com/questions/78478388/mongodb-aggregation-nested-group