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

MongoDB – Group and sum aggregation in a map structure

I’m pretty new to MongoDB and I need to group the sum of the total of all invoices for each unique date in all the documents in a collection. This is a summary of the collection, where data is stored in a key-value pair:

Document 1:
{
  "data": [
    {
      "key": "invoice-date",
      "value": {
        "$date": "2023-05-04T00:00:00.000Z"
      }
    },
    {
      "key": "invoice-total",
      "value": 110.29
    }
  ]
}

Document 2:
{
  "data": [
    {
      "key": "invoice-date",
      "value": {
        "$date": "2023-05-04T00:00:00.000Z"
      }
    },
    {
      "key": "invoice-total",
      "value": 47.18
    }
  ]
}

Document 3:
{
  "data": [
    {
      "key": "invoice-date",
      "value": {
        "$date": "2023-05-05T00:00:00.000Z"
      }
    },
    {
      "key": "invoice-total",
      "value": 22.05
    }
  ]
}

This should be grouped into a map and the result should be as follows:

"2023-05-04T00:00:00.000Z": 157.47
"2023-05-05T00:00:00.000Z": 22.05

This what I have tried thus far in Spring 3.1.4:


        MatchOperation matchStage = Aggregation.match(Criteria.where("data.key").in("invoice-date", "invoice-total"));

        
        GroupOperation groupStage = Aggregation.group("invoice-date")
                        .sum(ConditionalOperators.when(Criteria.where("data.key").is("invoice-total")).then("data.value")
                                        .otherwise(0))
                        .as("total");


        ProjectionOperation projectionStage = Aggregation.project()
                        .andExpression("_id").as("invoice-date")
                        .andInclude("total");


        Aggregation aggregation = Aggregation.newAggregation(
                        matchStage,
                        groupStage,
                        projectionStage);

        return mongoTemplate.aggregate(aggregation, "documents", Map.class);

However, the returned total is always 0 and the date is null. How can I make the aggregation work?

Tags:

2

Answers


  1. 0

    Your query is not working as you are trying to group by "invoice-date" which is a value but not a field.

    It can be solved by extracting the value from the data array as the invoiceDate and invoiceTotal fields (second stage).

    For converting the set of dates with respective total, you need to $group all documents into one document and convert into key-value pair.

    db.collection.aggregate([
  {
    $match: {
      "data.key": {
        $in: [
          "invoice-date",
          "invoice-total"
        ]
      }
    }
  },
  {
    $set: {
      invoiceDate: {
        $getField: {
          input: {
            $first: {
              $filter: {
                input: "$data",
                cond: {
                  $eq: [
                    "$$this.key",
                    "invoice-date"
                  ]
                }
              }
            }
          },
          field: "value"
        }
      },
      invoiceTotal: {
        $getField: {
          input: {
            $first: {
              $filter: {
                input: "$data",
                cond: {
                  $eq: [
                    "$$this.key",
                    "invoice-total"
                  ]
                }
              }
            }
          },
          field: "value"
        }
      }
    }
  },
  {
    $group: {
      _id: "$invoiceDate",
      total: {
        $sum: "$invoiceTotal"
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: {
          k: {
            $toString: "$_id"
          },
          v: "$total"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: "$data"
      }
    }
  }
])

    Demo @ Mongo Playground

    Sorry that I am not a Spring/Java MongoDB Developer, based on what I researched, the Spring code should be as:

    MatchOperation matchStage = Aggregation.match(Criteria.where("data.key").in("invoice-date", "invoice-total"));

AggregationOperation setStage = new AggregationOperation() {
    @Override
    public Document toDocument(AggregationOperationContext context) {
        return new Document("$set", 
            new Document("invoiceDate", 
                new Document("$getField", 
                    new Document("input", 
                        new Document("$first", 
                            new Document("$filter", 
                                new Document("input", "$data")
                                    .append("cond", 
    new Document("$eq", Arrays.asList("$$this.key", "invoice-date"))))))
                                    .append("field", "value")))
                .append("invoiceTotal", 
                    new Document("$getField", 
                        new Document("input", 
                            new Document("$first", 
                                new Document("$filter", 
                                    new Document("input", "$data")
                                        .append("cond", 
                                            new Document("$eq", Arrays.asList("$$this.key", "invoice-total"))))))
                        .append("field", "value"))));
    }
};

GroupOperation groupStage = Aggregation.group("invoiceDate")
    .sum("invoiceTotal")
    .as("total");

AggregationOperation secondGroupStage = new AggregationOperation() {
    @Override
    public Document toDocument(AggregationOperationContext context) {
        return new Document("$group", 
            new Document("_id",  new BsonNull())
                .append("data", 
                    new Document("$push", 
                        new Document("k", 
                            new Document("$toString", "$_id"))
                .append("v", "$total"))));
    }
};

AggregationOperation replaceRootStage = new AggregationOperation() {
    @Override
    public Document toDocument(AggregationOperationContext context) {
        return new Document("$replaceRoot", 
            new Document("newRoot", 
                new Document("$arrayToObject", "$data")));
    }
};

Aggregation aggregation = Aggregation.newAggregation(
    matchStage,
    setStage,
    groupStage,
    secondGroupStage,
    replaceRootStage);

return mongoTemplate.aggregate(aggregation, "documents", Map.class);
    Login or Signup to reply.
  2. 0

    Instead of $getField I would use $arrayToObject:

    db.collection.aggregate([
   { $match: { "data.key": { $in: ["invoice-date", "invoice-total"] } } },
   {
      $set: {
         data: {
            $arrayToObject: {
               $map: {
                  input: "$data",
                  in: { k: "$$this.key", v: "$$this.value" }
               }
            }
         }
      }
   },
   {
      $group: {
         _id: { $toString: "$data.invoice-date" },
         total: { $sum: "$data.invoice-total" }
      }
   },
   {
      $group: {
         _id: null,
         data: { $push: { k: "$_id", v: "$total" } }
      }
   },
   { $replaceWith: { $arrayToObject: "$data" } }
])

    Mongo Playground

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

Back To Top
Search