Question 226694 in Mongodb Question posted in
The official documentation can be found here.

Java spring-mongo-data Mongodb query for group and projection logic

I have below 3 sample records:

    {
      "_id" : "0cc5c468-2105-11ee-ad68-15d467e62by4d",
      "storeId" : "3070",
      "paymentType" : "cash",
      "total" : {"$numberDecimal": "100"}
    },
{
      "_id" : "1111111-2105-11ee-ad68-15d467e62by4d",
      "storeId" : "3070",
      "paymentType" : "cash",
      "total" : {"$numberDecimal": "100"}
    },
    {
      "_id" : "0cc5c468-2105-11ee-ad68-15d467e62by4d",
      "storeId" : "3070",
      "paymentType" : "creditCard",
      "total" : {"$numberDecimal": "50"}
    }

I am trying to write some grouping and projection logic in which the desired output from above sample data should be as below:

StoreId | Cash Payment | Card Payment | Total Amount Cash + Card
3070        200               50                250

The last column can ofcourse be ignored but i need this kind of output from mongodb in java.
I have tried many things and below is the code I have so far which is not giving desired output, it gives me two separate records for card payment and cash payment types.

GroupOperation groupingLogic() {
        return group("storeId", "paymentType")
                .first("storeId").as("storeId")
                .first("paymentType").as("paymentType")
                .sum(TOTAL).as(TOTAL);
    }

I am not sure how to get output as expected above with java mongodb code .

Please someone help.

Tags:

2

Answers


  1. 0

    Explanation:

    1. We need to group by 2 parameters (storeId + paymentType) and sum paymentType's total amount.
    2. We sum total amount for all paymentTypes for each storeId.
    3. We need to use $arrayToObject to pivot each paymentType into field.
    4. With other operators we assamble expected result.
    {
   "Total Amount": NumberDecimal("250"),
   "cash": NumberDecimal("200"),
   "creditCard": NumberDecimal("50"),
   "storeId": "3070"
}

    Note: cash, creditCard, check, etc. are dynamically filled. If any storeId has no such payment types, these fields may not be there.

    Try this one:

    db.collection.aggregate([
  {
    $group: {
      _id: {
        storeId: "$storeId",
        paymentType: "$paymentType"
      },
      total: {
        $sum: "$total"
      }
    }
  },
  {
    $group: {
      _id: "$_id.storeId",
      paymentType: {
        $push: {
          "k": "$_id.paymentType",
          "v": "$total"
        }
      },
      "total": {
        $sum: "$total"
      }
    }
  },
  {
    "$replaceWith": {
      "$arrayToObject": {
        "$concatArrays": [
          "$paymentType",
          [
            {
              "k": "storeId",
              "v": "$_id"
            },
            {
              "k": "Total Amount",
              "v": "$total"
            }
          ]
        ]
      }
    }
  }
])

    MongoPlayground

    Java – complex aggregation pipeline

    private static AggregationOperation replaceWith() {
    return ctx -> Document.parse("{" +
        "'$replaceWith': {" +
            "'$arrayToObject': {" +
                "..." +
            "}" +
        "}"+
    "}")
}
    Login or Signup to reply.
  2. 0

    If you want to group and add up some data from your MongoDB collection. Here’s how you can do it using Java with Spring Data MongoDB:

    If You want to group the data by the "storeId" and sum the amounts for different payment types ("cash" and "creditCard"). Here’s how you can do that:

    import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.GroupOperation;
import org.springframework.data.mongodb.core.query.Criteria;

// ...

GroupOperation groupingLogic() {
    return Aggregation.group("storeId")
        .sum(
            ConditionalOperators.when(Criteria.where("paymentType").is("cash")).then("$total.$numberDecimal").otherwise(0)
        ).as("cashPayment")
        .sum(
            ConditionalOperators.when(Criteria.where("paymentType").is("creditCard")).then("$total.$numberDecimal").otherwise(0)
        ).as("cardPayment")
        .sum("$total.$numberDecimal").as("totalAmountCashCard");
}

    To show in your format. Here’s how you can do that:

    import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;


ProjectionOperation projectionLogic() {
    return Aggregation.project()
        .and("storeId").as("StoreId")
        .and("cashPayment").as("Cash Payment")
        .and("cardPayment").as("Card Payment")
        .and("totalAmountCashCard").as("Total Amount Cash + Card");
}

    Putting It All Together:

    import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class YourService {

    private final MongoTemplate mongoTemplate;

    public YourService(MongoTemplate mongoTemplate) {
        this.mongoTemplate = mongoTemplate;
    }

    public List<YourOutputType> getGroupedData() {
        Aggregation aggregation = Aggregation.newAggregation(
            groupingLogic(),
            projectionLogic()
        );

        return mongoTemplate.aggregate(aggregation, "yourCollectionName", YourOutputType.class).getMappedResults();
    }
}

    Remember to replace yourCollectionName with the actual name of your MongoDB collection and YourOutputType with the appropriate class that matches the expected output.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search