How to use aggregation with spring data mongodb

Question

I've a collection of document. Each document has two fields - code and status. My mongodb collection contains some documents like the following:

[{
    "code":"1234",
    "status":"A"
},
{
    "code":"1234",
    "status":"A"
}
{
    "code":"1234",
    "status":"B"
},
{
    "code":"1235",
    "status":"A"
}]

I want to find count by status per code. My desired output is something like the following:

[
    {"code":"1234", "counts": {"A":2, "B":1}},
    {"code":"1235", "counts": {"A":1, "B":0}}
]

How can I do this with spring data mongodb? I'm very new to mongodb.

Update I've managed to write the mongodb query. Here it is:

db.mycollection.aggregate(
[
{"$group": {"_id": {"code":"$code", "status":"$status"}, "total": {"$sum":1}}},
{"$group": {"_id": "$_id.code", "counts": {"$push": {"status": "$_id.status", "count":"$total" }}}},
{"$project" : {"code":"$_id", _id: 0, counts:1}}
])

Can anyone help on how to write this query in spring data mongodb?

Answer 1

Please check out with following code

DBObject  counts= new BasicDBObject();
counts.put("status", "status");
counts.put("count", "count");

AggregationResults<Output> results = operations.aggregate(
    newAggregation(Codes.class, 
        group("code","status").count().as("count"),
        group("code").push(counts).as("counts")
), Output.class);

I considered Codes.java class as your input document class and Output.java class as your desired result class.

Answer 2

Tricky query but this should work for you with just one more step outside mongodb. As for the Spring part of your question, there are plenty of tutorials about it.

db.codes.aggregate(
  {$group: {_id: {code: "$code", status: "$status"}, count: {$sum: 1}}},
  {$group: {_id: "$_id.code", "counts": {$push: {status: "$_id.status", count: "$count"}}}})

{ "_id" : 123, "counts" : [ { "status" : "B", "count" : 1 }, { "status" : "A", "count" : 1 } ] }
{ "_id" : 124, "counts" : [ { "status" : "C", "count" : 2 }, { "status" : "B", "count" : 1 } ] }