group the json based on specific field

Question

I have a JSON response I want to get the username,geo,salesstage,count of particular user in specific geo in particular sales stage and pipeline value same as count.

Now count worked based on geo. Now is getting a wrong count.

eg  Jaison Clinton has 4 count for proposed submitted  in geo :nsu with pipeline value 12 
    Jaison Clinton has 1 count for qualified  in geo :nsu with pipeline value 3  
     Jaison Clinton has 1 count in geo :Us East with pipeline value 3 

  expected output
    [{"UserName":"Jaison Clinton", "Geo":"NSU", "SalesStage":"Proposal Submitted","count":"4","PipelineValue":12},
{"UserName":"Jaison Clinton", "Geo":"NSU", "SalesStage":"Qualified","count":"4","PipelineValue":3}
{"UserName":"Jaison Clinton", "Geo":"US East", "SalesStage":"Proposal Submitted","count":"1","PipelineValue":3}},
 {"UserName":"Kaviarasan Selvaraj", "Geo":"NSU", "SalesStage":"Proposal Submitted","count":"2","PipelineValue":9},
{"UserName":"Vivek Sharma", "Geo":"NSU", "SalesStage":"close won","count":"1","PipelineValue":13} ]

const data ={
   "pipeline":{
      "data":[
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3,"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"qualified",
            "PipelineValue":"3"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"US East",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"4"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"5"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3"
         },
         {
            "UserName":"Kaviarasan Selvaraj",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"7"
         },
         {
            "UserName":"Kaviarasan Selvaraj",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"2"
         },
         {
            "UserName":"Vivek Sharma",
            "Geo":"NSU",
            "SalesStage":"close won",
            "PipelineValue":"13"
            
        }
      ]
   }
};
const result = Object.entries(data.pipeline.data.reduce((acc,cur) => { 
      let k=cur.Geo+'|'+cur.SalesStage;
      (acc[k]=acc[k] || []).push(+cur.PipelineValue.replace(/,/g,''))
      return acc}, {}))
    .map(e=>{
      let k=e[0].split('|'); 
      return {Geo:k[0],SalesStage:k[1],count:e[1].length,PipelineValue:e[1].reduce((a,c)=>a+c)} 
     });

console.log(result);

Answer 1

You could group by the wanted keys and get the part of PipelineValue for summing and a count for the items of the same group.

var data = [{ UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "3," }, { UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "qualified", PipelineValue: "3" }, { UserName: "Jaison Clinton", Geo: "US East", SalesStage: "Proposal Submitted", PipelineValue: "4" }, { UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "5" }, { UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "3" }, { UserName: "Kaviarasan Selvaraj", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "7" }, { UserName: "Kaviarasan Selvaraj", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "2" }, { UserName: "Vivek Sharma", Geo: "NSU", SalesStage: "close won", PipelineValue: "13" }],
    keys = ['UserName', 'Geo', 'SalesStage'],
    result = Object.values(data.reduce((r, o) => {
        const key = keys.map(k => o[k]).join('|');
        if (!r[key]) r[key] = Object.fromEntries(keys.map(k => [k, o[k]]));
        r[key].count = (r[key].count || 0) + 1;                
        r[key].PipelineValue = (r[key].PipelineValue || 0) + +(o.PipelineValue || '0').toString().match(/\d+/)[0];
        return r;
    }, {}));

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }