CODEWITHSUNDEEP
javamongodbmongodb-queryaggregation-framework
duknust
duknust

Reputation: 174

How to run a .group() with Java

I have this query in mongoDB and that produces the result that I want. But now I'm trying to use that in java.

This is the query in MongoDB:

var red = function(doc, out) {
out.count_order++;
out.sum_qty += doc.quantity;
out.sum_base_price += doc.extendedprice;
out.sum_disc_price += doc.extendedprice * (1 - doc.discount);
out.sum_charge += doc.extendedprice * (1 - doc.discount) * (1 + doc.tax);
out.avg_disc += doc.discount;
};

var avg = function(out) {
out.avg_qty = out.sum_qty / out.count_order;
out.avg_price = out.sum_base_price / out.count_order;
out.avg_disc = out.avg_disc / out.count_order;
};

db.lineitems.group( {
key : { returnflag : true, linestatus : true},
cond : { "shipdate" : {$lte: 19980801}},
initial: { count_order : 0, sum_qty : 0, sum_base_price : 0, sum_disc_price : 0,
sum_charge : 0, avg_disc : 0},
reduce : red,
finalize : avg
});

And now the way I'm using it in Java but I don't know how to use the avg function.

    String avg = "var avg = function(out) {"
            + "out.avg_qty = out.sum_qty / out.count_order;"
            + "out.avg_price = out.sum_base_price / out.count_order;"
            + "out.avg_disc = out.avg_disc / out.count_order;};";

    String reduce = "function(doc, out) {"
            + "out.count_order++;"
            + "out.sum_qty += doc.quantity;"
            + "out.sum_base_price += doc.extendedprice;"
            + "out.sum_disc_price += doc.extendedprice * (1 - doc.discount);"
            + "out.sum_charge += doc.extendedprice * (1 - doc.discount) * (1 + doc.tax);"
            + "out.avg_disc += doc.discount;};";

    String finalize = "function(out) {"
            + "out.avg_qty = out.sum_qty / out.count_order;"
            + "out.avg_price = out.sum_base_price / out.count_order;"
            + "out.avg_disc = out.avg_disc / out.count_order;};";

    MapReduceIterable<Document> iterable = collection.mapReduce(**????**, reduce).finalizeFunction(finalize);

How can I use that function?

Upvotes: 3

Views: 101

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50436

You would be better off using the aggreation framework for this than calling the .group() method, which is of course an older implementation before the aggregation framework actually existed.

The logic as applied in the shell would be:

db.lineitems.aggregate([
    { "$match": { "shipdate": { "$lte": 19980801 } },
    { "$group": {
        "_id": {
            "returnflag": "$returnflag",
            "linestatus": "$linestatus"
        },
        "count": { "$sum": 1 },
        "sum_qty": { "$sum": "$quantity" },
        "avg_qty": { "$avg": "$quantity" },
        "sum_base_price": { "$sum": "$extendedprice" },
        "avg_base_price": { "$avg": "$extendedprice" },
        "sum_disc_price": {
            "$sum": {
                "$multiply": [
                    "$extendedprice",
                    { "$subtract": [ 1, "$discount" ] }
                ]
            }
        },
        "avg_disc_price": {
            "$avg": {
                "$multiply": [
                    "$extendedprice",
                    { "$subtract": [ 1, "$discount" ] }
                ]
            }
        },
        "sum_charge": {
            "$sum": {
                "$multiply": [
                    "$extendedprice",
                    { "$subtract": [ 1, "$discount" ] },
                    { "$add": [ 1, "$tax" ] }
                ]
            }
        },
        "avg_disc": { "$avg": "$discount" }
    }}
])

The aggregation framework as a very efficient $group pipeline stage operator which does all the same things in native coded operations. There is $sum and $avg accumulators built in as well as the other math operations.

In short, as "natively coded" and not relying on "JavaScript interpretation", the execution is much faster than what .group() can provide. Plus it really should be fairly straightforward to understand.

Translating that to Java would be something like

List<Document> pipeline = Arrays.<Document>asList(
  new Document(
    "$match", new Document(
      "shipdate", new Document(
        "$lte", 19980801             
      )
    )
  ),
  new Document(
    "$group", new Document(
      "_id", new Document(
        "returnflag", "$returnflag"
      ).append( "linestatus", "$linestatus" )
    ).append(
      "count", new Document( "$sum", 1 )
    ).append(
      "sum_qty", new Document( "$sum", "$quantity" )
    ).append(
      "avg_qty", new Document( "$avg", "$quantity" )
    ).append(
      "sum_base_price", new Document( "$sum", "$extendedprice" )
    ).append(
      "avg_base_price", new Document( "$avg", "$extendedprice" )
    ).append(
      "sum_disc_price", new Document(
        "$sum", new Document(
          "$multiply", Arrays.asList(
            "$extendedprice",
            new Document(
              "$subtract", Arrays.asList(1, "$discount")
            )
          )
        )
      )
    ).append(
      "avg_disc_price", new Document(
        "$avg", new Document(
          "$multiply", Arrays.asList(
            "$extendedprice",
            new Document(
              "$subtract", Arrays.asList(1, "$discount")
            )
          )
        )
      )
    ).append(
      "sum_charge", new Document(
        "$sum", new Document(
          "$multiply", Arrays.asList(
            "$extendedprice",
            new Document(
              "$subtract", Arrays.asList(1, "$discount")
            ),
            new Document(
              "$add", Arrays.asList(1, "$tax")
            )
          )
        )
      )
    ).append(
      "avg_disc", new Document( "$avg", "$discount" )
    )
  )
);

AggregateIterable<Document> result = collection.aggregate(pipeline);

Upvotes: 1

Related Questions