How to make MongoDB do more of the work?

[James_C]

Fairly new to MongoDB and working on a simple project. Currently trying to make pie charts using counts of certain fields:value. What I have currently works but for some fields that have multiple possible values creating the chart take a very long time due to the iteration in Java. I’m thinking that MongoDB can do more of this work on its end. This what I’m doing currently:

Create the dataset for JFreeChart piechart.

private PieDataset getCreditsDataset() {
        DefaultPieDataset dataset = new DefaultPieDataset();
        String key = "Current Credits";
        for (int i = 0; i < 131; i++) {
            int value = i;
            double total = 0;
            try {
                total = sda.getIntTotal(key, value);
            } catch(NullPointerException npe) {
                //do nothing
            }
            if (total != 0) {
                dataset.setValue(String.valueOf(value), total); 
            } else {
                //do not add
            }
        }
        return dataset;
    }

Get count of value for key

public double getIntTotal(String key, int value) {
        String field = "$" + key;
        Document doc = collection.aggregate(
                Arrays.asList(
                        Aggregates.match(Filters.eq(key, value)),
                        Aggregates.group(field, Accumulators.sum("count", 1))
                )
        ).first();
        return (double) doc.getInteger("count", 0);
    }

Iterating like this takes quite a bit of time and I think MongoDB should be able to do more the work and reduce time but I don’t know how to structure the query. Is it possible for MongoDB to perform this count operation and return a document with the all of the results? Any help is appreciated.

[Takis]

Hello

Seems that you run 1 aggregate/per value , so 131 queries.
You can run 1 query only if you remove the match,and you will get the count of 131 groups.
like group1 count1 , group2 count2 …
Maybe i didn’t understand something,but i don’t see the reason for 1 query per value.
In case you only want the groups with value 0 to 131,you can filter before group.

[James_C]

Hello Takis,

Thanks for the reply. Thats exactly right, I shouldn’t need and don’t want to run 1 query per value, but thats the extent of my ability with MongoDB so far. Hopefully I can explain a bit better.

This is a student database and I’m trying to build a pie chart displaying the distribution of credits. Students can have any credit value between 0 and 131. So I need to build a dataset that has a count for each credit value for example there are 1000 students that have 65 credits, 100 student with 20 credits…etc.

I’m trying not to do this in the Java app because it takes time to do 1 query per value, but I’m not familiar enough with setting up the proper query/aggregate/filtering to achieve this.

What did you mean by filter before the group?

[Takis]

yes 1 group by can do that for all groups.
group by seperates the collections in many groups and in each group separatly apply the accumulator (here find the total members of the group)

Example data (20 students with credits 0 to 2 ) (you can have any students and credits 0 131)

{:student_name "name0", :credit 2}
 {:student_name "name1", :credit 2}
 {:student_name "name2", :credit 1}
 {:student_name "name3", :credit 0}
 {:student_name "name4", :credit 2}
 {:student_name "name5", :credit 0}
 {:student_name "name6", :credit 1}
 {:student_name "name7", :credit 1}
 {:student_name "name8", :credit 2}
 {:student_name "name9", :credit 0}
 {:student_name "name10", :credit 2}
 {:student_name "name11", :credit 0}
 {:student_name "name12", :credit 1}
 {:student_name "name13", :credit 0}
 {:student_name "name14", :credit 1}
 {:student_name "name15", :credit 1}
 {:student_name "name16", :credit 2}
 {:student_name "name17", :credit 1}
 {:student_name "name18", :credit 2}
 {:student_name "name19", :credit 0}

1 group by credit ,and i get 3 documents

{total_students 7, credit 1}
{total_students 7, credit 2}
{total_students 6, credit 0}

You only need this,it will return a cursor with the 131 documents.
each document = 1 credit value , with the “count” the number of students
Try it if you can.

collection.aggregate(
                Arrays.asList(
                        Aggregates.group("credit", Accumulators.sum("count", 1)));

[James_C]

Hello Takis!

I think I understand a little bit better now. Thanks so much for taking the time to reply and help me out, I really appreciate it.

Updated method:

public PieDataset getIntDataset(String key) {
        DefaultPieDataset dataset = new DefaultPieDataset();
        String field = "$" + key;

        AggregateIterable<Document> doc = collection.aggregate(
                Arrays.asList(
                        Aggregates.group(field, Accumulators.sum("count", 1))
                ));
        for (Document d : doc) {
            JSONObject jo = new JSONObject(d.toJson());
            dataset.setValue(String.valueOf(jo.getInt("_id")), jo.getDouble("count"));
        }
        return dataset;
    }

Works great. Went from 18 secs for a chart to 0.2 seconds (20k students)…huge improvement. I knew it shouldn’t take that long and MongoDB would be able to do it much faster. Thanks again!