Group and Aggregate List of Map

Question

I have a List<Map<String, Object>> input like below:

[{
    CURRENCY = USD,
    STATUS = NEW,
    PUBLISH_REGION = DEL,
    SOURCE = ALADDIN,
    RECON_STATUS = null,
    JOB_ID_COUNT = 783
}, {
    CURRENCY = USD,
    STATUS = IN_PROGRESS,
    PUBLISH_REGION = DEL,
    SOURCE = ALADDIN,
    RECON_STATUS = null,
    JOB_ID_COUNT = 462
}, {
    CURRENCY = USD,
    STATUS = NEW,
    PUBLISH_REGION = DEL,
    SOURCE = GROUP,
    RECON_STATUS = null,
    JOB_ID_COUNT = 4
}]

I am trying to create another List<Map<String, Object>> by grouping on CURRENCY, PUBLISH_REGION, SOURCE and RECON_STATUS columns. And add all unique STATUS values as pivot to the output map and use JOB_ID_COUNT to summarize/aggregate the count.

List<String> groups = new ArrayList<>(asList("SOURCE", "RECON_STATUS", "PUBLISH_REGION", "CURRENCY"));
List<Map<String, Object>> = input.stream()
    .collect(groupingBy(row -> row.get(groups.get(0)), mapping(map -> map.get(groups.get(0)), toList())));

I am expecting below response: Output:

 [{
        CURRENCY = USD,
        PUBLISH_REGION = DEL,
        SOURCE = ALADDIN,
        RECON_STATUS = null,
        NEW = 783,
        IN_PROGRESS = 462
    }, {
        CURRENCY = USD,
        PUBLISH_REGION = DEL,
        SOURCE = GROUP,
        RECON_STATUS = null,
        NEW = 4,
        IN_PROGRESS = 0
    }]

I am getting compile time error when trying to group by multiple map fields. Single field groupingBy is working fine. Any help is greatly appriciated.

Did you try this solutions [Group by multiple field names in java 8](https://stackoverflow.com/a/41283501/4207306) [Java groupingBy: sum multiple fields](https://stackoverflow.com/questions/51452737/java-groupingby-sum-multiple-fields) ? — Eklavya, Sep 17 '20 at 12:26
@Nikolas: STATUS is a pivot column, so all unique values of STATUS are added to the resulting response map. JOB_ID_COUNT is the aggregator, so the values are aggregated in the response. Rest of the cloumns are grouping fields. Hope its clear now. — Pravat Panda, Sep 18 '20 at 06:17

score 1 · Answer 1 · answered Sep 17 '20 at 17:20

Tried this solution and it is working

Stream the source List
Map each value of map in the list to Class MapWrapper(a pojo where each key is a field)
GroupBy using the groupByKey defined in MapWrapper(uses CURRENCY, PUBLISH_REGION, SOURCE and RECON_STATUS columns) 3.a The result is a Map<String, List<MapWrapper>> 4.Stream through the entry set
map - and get the value alone from (Map<String, List<MapWrapper>>)
Map - convert from List<MapWrapper> to Map<String, Object> using MapWrapper::map
Collect to a list

In Short the solution is

List<Map<String, Object>> value = lst.stream()
                .map(map -> new MapWrapper(map))
                .collect(groupingBy(MapWrapper::groupByKey))
                .entrySet()
                .stream()
                .map(e -> e.getValue())
                .map(MapWrapper::map).collect(toList());

Working Code

public class MultipleFieldSorting {
    private static Map<String, Object> map, map1, map2;
    private static List<Map<String, Object>> lst = new ArrayList<>();
    static {
        map = new HashMap<>();
        map.put("CURRENCY", "USD");
        map.put("STATUS", "NEW");
        map.put("PUBLISH_REGION", "DEL");
        map.put("SOURCE", "ALADDIN");
        map.put("RECON_STATUS", null);
        map.put("JOB_ID_COUNT", "783");

        map1 = new HashMap<>();
        map1.put("CURRENCY", "USD");
        map1.put("STATUS", "IN_PROGRESS");
        map1.put("PUBLISH_REGION", "DEL");
        map1.put("SOURCE", "ALADDIN");
        map1.put("RECON_STATUS", null);
        map1.put("JOB_ID_COUNT", "462");

        map2 = new HashMap<>();
        map2.put("CURRENCY", "USD");
        map2.put("STATUS", "NEW");
        map2.put("PUBLISH_REGION", "DEL");
        map2.put("SOURCE", "GROUP");
        map2.put("RECON_STATUS", null);
        map2.put("JOB_ID_COUNT", "4");

        lst.add(map);
        lst.add(map1);
        lst.add(map2);
    }

    public static void main(String[] args) {
        List<Map<String, Object>> value = lst.stream()
                .map(map -> new MapWrapper(map))
                .collect(groupingBy(MapWrapper::groupByKey))
                .entrySet()
                .stream()
                .map(e -> e.getValue())
                .map(MapWrapper::map).collect(toList());

        System.out.println(value);
    }

}

class MapWrapper {
    private String currency;
    private String status;
    private String publish;
    private String source;
    private String recon_status;
    private String job_id;

    public MapWrapper(Map<String, Object> map) {
        this.currency = (String) map.get("CURRENCY");
        this.status = (String) map.get("STATUS");
        this.publish = (String) map.get("PUBLISH_REGION");
        this.source = (String) map.get("SOURCE");
        this.recon_status = (String) map.get("RECON_STATUS");
        this.job_id = (String) map.get("JOB_ID_COUNT");
    }

    String groupByKey() {
        return new StringBuilder().append(this.getCurrency()).append("-").append(this.publish).append("-")
                .append(this.source).append("-").append(this.recon_status).toString();
    }

    public static Map<String, Object> map(List<MapWrapper> lst){
            Map<String, Object> res = new HashMap<>();
            res.put("CURRENCY",lst.get(0).getCurrency());
            res.put("PUBLISH_REGION",lst.get(0).getPublish());
            res.put("SOURCE",lst.get(0).getSource());
            res.put("RECON_STATUS",lst.get(0).getRecon_status());
            for(MapWrapper m : lst){
                res.put(m.getStatus(), m.getJob_id());
            }
            if(res.get("NEW")==null){
                res.put("NEW", 0);
            }
            if(res.get("IN_PROGRESS")==null){
                res.put("IN_PROGRESS", 0);
            }
            return res;
            
        }

    String getCurrency() {
        return currency;
    }

    void setCurrency(String currency) {
        this.currency = currency;
    }

    String getStatus() {
        return status;
    }

    void setStatus(String status) {
        this.status = status;
    }

    String getPublish() {
        return publish;
    }

    void setPublish(String publish) {
        this.publish = publish;
    }

    String getSource() {
        return source;
    }

    void setSource(String source) {
        this.source = source;
    }

    String getJob_id() {
        return job_id;
    }

    void setJob_id(String job_id) {
        this.job_id = job_id;
    }

    String getRecon_status() {
        return recon_status;
    }

    void setRecon_status(String recon_status) {
        this.recon_status = recon_status;
    }

}

Is there any way to make the code generic without using MapWrapper so that the code can accomodate any no of column changes dynamically. — Pravat Panda, Sep 18 '20 at 06:12
@PravatPanda I tried a more generic approach without using MapWrapper custom class. but the **tradeoff** is that the approach below is not using collectors framework. — Sasi Kumar, Sep 18 '20 at 09:07

score 1 · Accepted Answer · answered Sep 18 '20 at 09:08

Without Using Custom Class

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MultipleFieldSorting2 {
    private static Map<String, Object> map, map1, map2;
    private static List<Map<String, Object>> lst = new ArrayList<>();
    static {
        map = new HashMap<>();
        map.put("CURRENCY", "USD");
        map.put("STATUS", "NEW");
        map.put("PUBLISH_REGION", "DEL");
        map.put("SOURCE", "ALADDIN");
        map.put("RECON_STATUS", null);
        map.put("JOB_ID_COUNT", "783");

        map1 = new HashMap<>();
        map1.put("CURRENCY", "USD");
        map1.put("STATUS", "IN_PROGRESS");
        map1.put("PUBLISH_REGION", "DEL");
        map1.put("SOURCE", "ALADDIN");
        map1.put("RECON_STATUS", null);
        map1.put("JOB_ID_COUNT", "462");

        map2 = new HashMap<>();
        map2.put("CURRENCY", "USD");
        map2.put("STATUS", "NEW");
        map2.put("PUBLISH_REGION", "DEL");
        map2.put("SOURCE", "GROUP");
        map2.put("RECON_STATUS", null);
        map2.put("JOB_ID_COUNT", "4");

        lst.add(map);
        lst.add(map1);
        lst.add(map2);
    }
    
    public static Map<String, Object> mapper(Map<String, Object> e){

        String key = e.get("CURRENCY") + "-" + e.get("PUBLISH_REGION") + "-" + e.get("SOURCE") + "-" + e.get("RECON_STATUS");
        Map<String, Object> groupedValue = res.get(key);
        if(groupedValue!=null){
            groupedValue.put((String) e.get("STATUS"), groupedValue.get("STATUS")!=null ? groupedValue.get("STATUS")+","+e.get("JOB_ID_COUNT") : e.get("JOB_ID_COUNT"));
            if(groupedValue.get("NEW")==null){
                groupedValue.put("NEW", 0);
            }
            if(groupedValue.get("IN_PROGRESS")==null){
                groupedValue.put("IN_PROGRESS", 0);
            }
        }else{
            groupedValue = new HashMap<>();
            res.put(key, groupedValue);
            groupedValue.put("CURRENCY", e.get("CURRENCY"));
            groupedValue.put("PUBLISH_REGION", e.get("PUBLISH_REGION"));
            groupedValue.put("SOURCE", e.get("SOURCE"));
            groupedValue.put("RECON_STATUS", e.get("RECON_STATUS"));
            groupedValue.put((String) e.get("STATUS"), e.get("JOB_ID_COUNT"));
        }
        return groupedValue;
    
    }
    static Map<String, Map<String, Object>> res = new HashMap<>();
    
    public static void main(String[] args) {
        List<Map<String, Object>> finalResult = new ArrayList<>();
        lst.stream()
        .map(MultipleFieldSorting2::mapper)
        .forEach(result -> {
            if(!finalResult.contains(result))
                finalResult.add(result);
        });

        System.out.println(finalResult);
    }

}

@PravatPanda I tried a more generic approach without using MapWrapper custom class. but the tradeoff is that the approach below is not using collectors framework — Sasi Kumar, Sep 19 '20 at 05:21

Group and Aggregate List of Map

2 Answers2