Grouping operation on a field and putting the list of the liked field in an array

Question

I am working on a JHipster project with an AngularJS front-end and a Java back-end. I am using Spring data with the MongoDb database.

I did a grouping operation on the field budgetCode. So, for each budgetCode, I succeded to have the list of all the linked taskCodes.

Here, the method aggregateAllTaskCodes which does the grouping operation:

Repository layer

public class ClarityResourceAffectationRepositoryImpl implements ClarityResourceAffectationRepositoryCustom {
    @Override
        public List<ClarityResourceAffectationReport> aggregateAllTaskCodes() {

            Aggregation aggregation = newAggregation(
                    group("budgetCode").addToSet("budgetCode").as("budgetCode").addToSet("taskCode").as("taskCode"),
                    sort(Sort.Direction.ASC, previousOperation(),"budgetCode"));

            AggregationResults groupResults = mongoTemplate.aggregate(aggregation, ClarityResourceAffectation.class,
                    ClarityResourceAffectationReport.class);
            List<ClarityResourceAffectationReport> clarityResourceAffectationReports = groupResults.getMappedResults();

            return clarityResourceAffectationReports;
        }
    }

Service layer

public class ClarityResourceAffectationServiceImpl implements ClarityResourceAffectationService{
    @Override
    public List<ClarityResourceAffectationReport> aggregateAllTaskCodes() {
        log.debug("Request to aggregateByCodeBudgetForCodeTache : {}");
        List<ClarityResourceAffectationReport> result = clarityResourceAffectationRepository
                .aggregateAllTaskCodes();

        return result;
    }
}

REST API layer

public class ClarityResourceAffectationResource {
    @GetMapping("/clarity-resource-affectations/list-task-codes")
    @Timed
    public ResponseEntity<List<ClarityResourceAffectationReport>> aggregateTabAllTaskCodes() {
        log.debug("REST request to get aggregateTabAllTaskCodes : {}");
        List<ClarityResourceAffectationReport> result = clarityResourceAffectationService.aggregateAllTaskCodes();
        return new ResponseEntity<>(result, HttpStatus.OK);
    }
}

ClarityResourceAffectation

@Document(collection = "clarity_resource_affectation")
public class ClarityResourceAffectation implements Serializable {

    @Id
    private String id;

    @Field("budget_code")
    private String budgetCode;

    @Field("task_code")
    private String taskCode;

    public String getBudgetCode() {
        return budgetCode;
    }

    public void setBudgetCode(String budgetCode) {
        this.budgetCode = budgetCode;
    }

    public String getTaskCode() {
        return taskCode;
    }

    public void setTaskCode(String taskCode) {
        this.taskCode = taskCode;
    }
}

ClarityResourceAffectationReport

public class ClarityResourceAffectationReport implements Serializable {

    private static final long serialVersionUID = 1L;

    private String budgetCode;
    private String taskCode;
    private String listTaskCode;

    public String getBudgetCode() {
        return budgetCode;
    }

    public void setBudgetCode(String budgetCode) {
        this.budgetCode = budgetCode;
    }

    public String getTaskCode() {
        return taskCode;
    }

    public void setTaskCode(String taskCode) {
        this.taskCode = taskCode;
    }
    public String[] getListTaskCode() {
        return listTaskCode;
    }

    public void setListTaskCode(String[] listTaskCode) {
        this.listTaskCode = listTaskCode;
    }
}

clarity-resource-affectation.service.js

(function() {
    'use strict';
    angular
        .module('dashboardApp')
        .factory('ClarityResourceAffectation', ClarityResourceAffectation);

    ClarityResourceAffectation.$inject = ['$resource'];

    function ClarityResourceAffectation ($resource) {
        var resourceUrl =  'clarity/' + 'api/clarity-resource-affectations/:id';

        return $resource(resourceUrl, {}, {
            'query': { method: 'GET', isArray: true},
            'aggregateAllTaskCodes': {
                method: 'GET',
                isArray: true,
                url: 'clarity/api/clarity-resource-affectations/list-task-codes'
            }
        });
    }
})();

When I call the function in the AngularJS front-end and I display that on a table, for each budgetCode, I have the list of the taskCodes in an array of one element. For example, for the budgetCode [ "P231P00"] I can have this list of taskCodes: [ "61985" , "43606" , "60671" , "43602"]

Well, I would like to have the list of the linked taskCodes, not in an array of one element but in an array of several elements like that: [ ["61985"] , ["43606"] , ["60671"] , ["43602"] ]

What do I have to change in my code in order to do that?

Just for information, my javascript code which create the array based on the aggregate function:

clarity-resource-affectation-list-task-codes.controller.js

(function() {
    'use strict';

    angular
        .module('dashboardApp')
        .controller('ClarityResourceAffectationTableauBordNbCollaborateursController', ClarityResourceAffectationTableauBordNbCollaborateursController);

    ClarityResourceAffectationTableauBordNbCollaborateursController.$inject = ['$timeout', '$scope', '$stateParams', 'DataUtils', 'ClarityResourceAffectation'];

    function ClarityResourceAffectationTableauBordNbCollaborateursController ($timeout, $scope, $stateParams, DataUtils, ClarityResourceAffectation) {
        var vm = this;

        //Call of the function    
        allTaskCodes()

        function allTaskCodes()
        {
            ClarityResourceAffectation.aggregateAllTaskCodes(function(readings) {

                var dataAllTaskCodes;
                dataAllTaskCodes = [];

                alert(readings);

                readings.forEach(function (item) {
                    dataAllTaskCodes.push({
                        label: item.budgetCode,
                        value: item.taskCode,
                        listvalue: item.listTaskCode
                    });
                });

                vm.dataAllTaskCodes = dataAllTaskCodes;
            });
        }
    }
})();

Temporary solution: Actually, I found a temporary solution by completing the method I created in the Service Layer:

@Override
public List<ClarityResourceAffectationReport> aggregateAllTaskCodes() {
    log.debug("Request to aggregateAllTaskCodes : {}");
    List<ClarityResourceAffectationReport> result = clarityResourceAffectationRepository
            .aggregateAllTaskCodes();

    Iterator<ClarityResourceAffectationReport> iterator = result.iterator();
    while (iterator.hasNext())
    {
        ClarityResourceAffectationReport resAffectationReport = iterator.next();

        String taskCodes = resAffectationReport.getTaskCode();

        //Delete all exept letters, numbers and comma
        taskCodes = taskCodes.replaceAll("[^a-zA-Z0-9,]","");

        String[] listTaskCodes = taskCodes.split(",");

        resAffectationReport.setListTaskCodes(listTaskCodes);
    }

    return result;
}

Also, I added an additional field to ClarityResourceAffectationReport which is listTaskCode. I updated the report class above. Finally, when I do an alert: alert(readings[1].listvalue[0]), I have a result like 2630. So, I succeeded to have the first taskCode of a particular budgetCode.

I understood that what is important here is not the fact that as I told above for a budgetCode like [ "P231P00"], I must have a list like: [ "61985" , "43606" , "60671" , "43602"] or [ ["61985"] , ["43606"] , ["60671"] , ["43602"] ]. I just must have an array, not a string.

When I display alert(readings[1].listvalue), I have["2630","61297","61296","61299"] which is clearly an array because I can access each of the elements by calling alert(readings[1].listvalue[0]), alert(readings[1].listvalue[1]], etc...

I tried what you advised me

But, it is still not working. Here, my repository code:

@Override
public List<ClarityResourceAffectationReport> aggregateAllTaskCode() {

    AggregationOperation project = new AggregationOperation() {
        @Override
        public DBObject toDBObject(AggregationOperationContext aggregationOperationContext) {
            return new BasicDBObject("$project", new BasicDBObject("budgetCode", "$budget_code").append("taskCode", Arrays.asList("$task_code")));
        }
    };

    Aggregation aggregation = newAggregation(project,
            group("budgetCode").addToSet("budgetCode").as("budgetCode").addToSet("taskCode").as("taskCode"),
            sort(Sort.Direction.ASC, previousOperation(),"budgetCode"));


    AggregationResults groupResults = mongoTemplate.aggregate(aggregation, ClarityResourceAffectation.class,
            ClarityResourceAffectationReport.class);
    List<ClarityResourceAffectationReport> clarityResourceAffectationReports = groupResults.getMappedResults();

    log.debug("clarityResourceAffectationReports.size() => " + clarityResourceAffectationReports.size());
    log.debug("aggregation.toString() => " + aggregation.toString());

    return clarityResourceAffectationReports;
}

Here, you can find the logs:

clarityResourceAffectationReports.size() => 1
aggregation.toString() => {"aggregate" : "__collection__" , "pipeline" : [ { "$project" : { "budgetCode" : "$budget_code" , "taskCode" : [ "$task_code"]}} , { "$group" : { "_id" : "$budgetCode" , "budgetCode" : { "$addToSet" : "$budgetCode"} , "taskCode" : { "$addToSet" : "$taskCode"}}} , { "$sort" : { "_id" : 1 , "budgetCode" : 1}}]}

Thanks in advance

score 1 · Accepted Answer · answered Jun 06 '17 at 16:04

1

You need to use $project to change the taskCodes value into array of single value before $group.

I don't see any hook in the api to address this.

You can use AggregationOperation to create $project stage using mongodb (BasicDBObject) types.

AggregationOperation project = new AggregationOperation() {
       @Override
       public DBObject toDBObject(AggregationOperationContext aggregationOperationContext) {
          return new BasicDBObject("$project", new BasicDBObject("budgetCode", 1).append("taskCode", Arrays.asList("$taskCode")));
    } 
};

Something like

Aggregation aggregation = newAggregation(project,
                    group("budgetCode").addToSet("budgetCode").as("budgetCode").addToSet("taskCode").as("taskCode"),
                    sort(Sort.Direction.ASC, previousOperation(), "budgetCode"));

Using lambda

 Aggregation aggregation = newAggregation(
                aggregationOperationContext -> new BasicDBObject("$project", new BasicDBObject("budgetCode", 1).append("taskCode", Arrays.asList("$taskCode"))),
                group("budgetCode").addToSet("budgetCode").as("budgetCode").addToSet("taskCode").as("taskCode"),
                sort(Sort.Direction.ASC, previousOperation(), "budgetCode"));

answered Jun 06 '17 at 16:04

s7vr

73,656
11
106
127

I put the link between the front-end and the back-end and more details.I will try some more things before asking you again because I did not succeed to have what I want. I gave you more details on my code. – stephansav Jun 06 '17 at 17:30
What error do you get and try adding `ClarityResourceAffectationReport` pojo to the post ? – s7vr Jun 06 '17 at 17:31
I added the ClarityResourceAffectationReport. I tried your code. After calling the function: an alert(readings) and an alert(readings[0]) gave me `[object Object]`. Moreover, readings[0].budgetCode gave me: `[ ]`. Something must be changed somewhere. – stephansav Jun 07 '17 at 08:48
Thanks for information. You've to update your fields to arrays for spring to map it correctly. `private String[] budgetCode; private String[][] taskCode;`. Adjust the setters and getters too. – s7vr Jun 07 '17 at 12:14
Yes, I updated `ClarityResourceAffectationReport` and I put `private String[] budgetCode; private String[][] taskCode` with the corresponding getters and setters. But, when I do `alert(readings)` and `alert(readings[0])`, I have `[object Object]` and `alert(readings.length);` gave me 1. So, something is wrong. I continue to investigate. Anyway, I added an alternative solution by completing the **Service layer** method. I updated the post. – stephansav Jun 07 '17 at 16:44
I can't speak for angular part, but I have verified java code with documents like `{"budgetCode":"P231P00", taskCode: "43606" }` and it works for me as expected. So check the documents and try verifying the response after you get the data from db. – s7vr Jun 07 '17 at 18:03
I have documents like `{"budget_code": "P24DCDSA01","task_code": "61427"}`. I used the eclipse debugger in order to see the values of `List clarityResourceAffectationReports = groupResults.getMappedResults();` and I have only one element which is not null the first element. So, I have only one ClarityResourceAffectationReport. And in this element `budgetCode[0]` is empty and `taskCode[0][]` is empty. Is there a way to test this aggregate method on data created directly in the code code? Because, actually I have several other fields too. – stephansav Jun 08 '17 at 13:02
Alright you have to update the `$project` stage to include the field names as this is the outside the spring machinery. Something like `return new BasicDBObject("$project", new BasicDBObject("budgetCode", "$budget_code").append("taskCode", Arrays.asList("$task_code")));`. You can log the `aggregation.toString()` to see what aggregate query spring is issuing and you can log `clarityResourceAffectationReports.size()` to check the return result count and log all the necessary information you want to test. – s7vr Jun 08 '17 at 13:07
I changed the Repository method and I added it in the post. Also, I forgot to tell you that I added the ClarityResourceAffectation class in the post too. By this way, you see the `@Field` mapping. It did not work. I added the two logs, you advised me in the post. I will continue to see where is the problem. – stephansav Jun 08 '17 at 14:52
I think I see the issue. Looks like spring doesn't track of alias we have created in `$project` stage. I tried defining alias as field names and it seemed to work. Something like `new BasicDBObject("$project", new BasicDBObject("budget_code", "$budget_code").append("task_code", Arrays.asList("$task_code"))`. To answer your earlier question about checking the data created directly you can use `DBObject dbObject = groupResults.getRawResults(); BasicDBList values = (BasicDBList) dbObject.get("result"); ` and log `values.size()` – s7vr Jun 08 '17 at 15:41
You can consider using mongo template variant of `AggregationResults aggregate(Aggregation var1, String var2, Class var3)`; which takes second parameter as "collection_name" and the old code would work. This essentially bypasses the conversion between alias and field names. – s7vr Jun 08 '17 at 15:57
Thank you! Now, it works. Anyway, I have another question. I would like to know if it is possible to have an other grouping operation using `taskCode`. For example, for a budgetCode `["P24DCDSD06"]`, you have three linked taskCodes `[["62660"],["63376"],["62661"]]` and for each of these linked taskCodes, you have a list of actionCode. – stephansav Jun 08 '17 at 16:31
Np. You will use two groups, one group on `budgetCode` and `taskCodes` and push `actionCode` and other group on `budgetCode` and push both `taskCodes` and `actionCode` from previous group. – s7vr Jun 08 '17 at 16:51
Ok, thank you. I tried an algorithm and I put that in the post. Can you tell me if it is correct please? Shall I develop two aggregation operations and two project operations? How can I form the `AggregationResults groupResults` with two aggregation operations? – stephansav Jun 12 '17 at 12:18
You are very welcome. Please consider creating a separate question. I appreciate your understanding. Its not advisable to mix many questions into single post. – s7vr Jun 12 '17 at 12:20
Ok, I will create a new one for that now. – stephansav Jun 12 '17 at 12:24
I created another post: _Grouping operations on two levels with for each level an element - array association_ Also, I deleted things relative to the new post. – stephansav Jun 12 '17 at 15:59

Grouping operation on a field and putting the list of the liked field in an array

1 Answers1