I have MongoDB's Aggregate query, which allows you to return every status and count them by year. I want to translate it into Java Aggregation class, using the mongoTemplate.aggregate() method. I try to get JSON with the AggregateIterable<Document> class, but my server is loading it slowly, because of 2 or more connections to the database. I need to do this with the Aggregation class, not AggregateIterable<Document>.

Query:

    [
        {
            "$addFields": {
                "__alias_0": {
                    "year": {
                        "$year": "$createdAt"
                    }
                }
            }
        },
        {
            "$addFields": {
                "__count_by_value": {
                    "k": "status",
                    "v": "$status"
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "__alias_0": "$__alias_0",
                    "__alias_1": "$__count_by_value.v"
                },
                "__alias_2": {
                    "$sum": {
                        "$cond": [
                            {
                                "$ne": [
                                    {
                                        "$type": "$status"
                                    },
                                    "missing"
                                ]
                            },
                            1,
                            0
                        ]
                    }
                }
            }
        },
        {
            "$project": {
                "_id": 0,
                "__alias_0": "$_id.__alias_0",
                "__alias_1": "$_id.__alias_1",
                "__alias_2": 1
            }
        },
        {
            "$project": {
                "Date": "$__alias_0",
                "Number": "$__alias_2",
                "status": "$__alias_1",
                "_id": 0
            }
        },
        {
            "$group": {
                "_id": {
                    "Date": "$Date"
                },
                "__grouped_docs": {
                    "$push": "$$ROOT"
                }
            }
        },
        {
            "$sort": {
                "_id.Date.year": 1
            }
        },
        {
            "$unwind": "$__grouped_docs"
        },
        {
            "$replaceRoot": {
                "newRoot": "$__grouped_docs"
            }
        },
        {
            "$limit": 5000
        }
    ]

I try many ways to create it, but I can't find the right way.

For example:
In my database, I have 3 fields - Year and Status (New, Rejected).

[
 {
  "year": {
         "$date": "2021-08-25T08:44:55.576Z"
  },
  "Status": "New"
},

{
  "year": {
         "$date": "2021-08-25T09:59:49.270Z"
  },
  "Status": "New"
},

{
  "year": {
         "$date": "2021-08-25T10:27:00.003Z"
  },
  "Status": "Rejected"
},

{
  "year": {
         "$date": "2022-01-10T08:12:25.816Z"
  },
  "Status": "Rejected"
},

{
  "year": {
         "$date": "2022-01-10T08:13:07.423Z"
  },
  "Status": "Rejected"
}

After sending a request to the database with Java Aggregation, I want to get this kind of answer from the database:

[
 {
  "year": 2021,
  "Status": "New",
  "Amount": 2
 },

 {
  "year": 2021,
  "Status": "Rejected",
  "Amount": 1
 },

 {
  "year": 2021,
  "Status": "Rejected",
  "Amount": 2
 }
]

CodePudding user response：

Try this one:

db.collection.aggregate([
  {
    $group: {
      _id: {
        year: { $year: "$year" },
        status: "$Status"
      },
      count: { $count: {} }
    }    
  },
  { $replaceWith: { $mergeObjects: [ "$_id", "$$ROOT" ] } },
  { $unset: "_id" }
])

Mongo Playground