Mongodb - Applying group by and sum on array of objects in javascript

AtulKumar
July 15, 2022
268 views
2 votes
3 Answers

I need some advice on the proper way to attain my goal of applying group by and sum on JSON data.

Some server-side code actually generates a JSON that I have to work with :

[
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0003"
        },
        "printingMaterialCode": "400000033",
        "printingQuantity": 400,
        "approved": true
    },
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0002"
        },
        "printingMaterialCode": "400000033",
        "printingQuantity": 600,
        "campaignId": "DATAS00002",
    },
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0001"
        },
        "printingMaterialCode": "400000034",
        "printingQuantity": 300,
        "campaignId": "DATAS00002",
    }
]

Now, I need to apply some operation (maybe groupby and sum) to get the below result :

[
    {
        "printingMaterialCode": "400000033",
        "printingQuantity": 1000
        "approvedQuantity": 400
    },
    {
        "printingMaterialCode": "400000034",
        "printingQuantity": 300,
    }
]

Basically, the output is printingQuantity and approvedQuantity SUM per printingMaterialCode.

Answers

const myData = [
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0003"
        },
        "printingMaterialCode": "400000033",
        "printingQuantity": 400,
        "approved": true
    },
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0002"
        },
        "printingMaterialCode": "400000033",
        "printingQuantity": 600,
        "campaignId": "DATAS00002",
    },
    {
        "siteDetails": {
            "printerCode": "660103684",
            "siteId": "UTT212303-STB-2040-0001"
        },
        "printingMaterialCode": "400000034",
        "printingQuantity": 300,
        "campaignId": "DATAS00002",
    }
]

function elaborateMyData(myData) {
  const approvedQuantity = {};
  const printingQuantity = {};
  const myOutput = [];

  myData.forEach((v) => {
    printingQuantity[v.printingMaterialCode] = printingQuantity[v.printingMaterialCode] || 0;
    approvedQuantity[v.printingMaterialCode] = approvedQuantity[v.printingMaterialCode] || 0;

    printingQuantity[v.printingMaterialCode] += v.printingQuantity;

    if (v.approved) {
      approvedQuantity[v.printingMaterialCode] += v.printingQuantity
    }

  })

  Object.keys(printingQuantity).forEach((v) => {
    myOutput.push({
      printingMaterialCode: v,
      printingQuantity: printingQuantity[v],
      approvedQuantity: approvedQuantity[v],
    })
  })

  return myOutput
}

console.log(elaborateMyData(myData))

You can make use of Map as:

const arr = [
    {
        siteDetails: {
            printerCode: '660103684',
            siteId: 'UTT212303-STB-2040-0003',
        },
        printingMaterialCode: '400000033',
        printingQuantity: 400,
        approved: true,
    },
    {
        siteDetails: {
            printerCode: '660103684',
            siteId: 'UTT212303-STB-2040-0002',
        },
        printingMaterialCode: '400000033',
        printingQuantity: 600,
        campaignId: 'DATAS00002',
    },
    {
        siteDetails: {
            printerCode: '660103684',
            siteId: 'UTT212303-STB-2040-0001',
        },
        printingMaterialCode: '400000034',
        printingQuantity: 300,
        campaignId: 'DATAS00002',
    },
];

const map = new Map();
arr.forEach((o) => {
    const { printingMaterialCode, printingQuantity, approved } = o;
    const objInMap = map.get(printingMaterialCode);
    if (objInMap) {
        if (approved) {
            if (objInMap.approvedQuantity) objInMap.approvedQuantity = (objInMap.approvedQuantity ?? 0) + printingQuantity;
        }
        objInMap.printingQuantity += printingQuantity;
    } else {
        const newObj = { printingMaterialCode, printingQuantity };
        if (approved) newObj.approvedQuantity = printingQuantity;
        map.set(o.printingMaterialCode, newObj);
    }
});

const result = [...map.values()];
console.log(result);

We can build this on reduce in a fairly straightforward manner, with something like this:

const combine = (xs) => Object .entries (xs .reduce (
  (a, {printingMaterialCode, printingQuantity, approved = false}) => {
    const pmc = a [printingMaterialCode] || {printingQuantity: 0}
    pmc .printingQuantity += printingQuantity
    if (approved) pmc .approvedQuantity = (pmc .approvedQuantity || 0) + printingQuantity
    a [printingMaterialCode] = pmc
    return a
  },
  {}
)) .map (([printingMaterialCode, rest]) => ({printingMaterialCode, ...rest}))

const input = [{siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0003"}, printingMaterialCode: "400000033", printingQuantity: 400, approved: !0}, {siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0002"}, printingMaterialCode: "400000033", printingQuantity: 600, campaignId: "DATAS00002"}, {siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0001"}, printingMaterialCode: "400000034", printingQuantity: 300, campaignId: "DATAS00002"}]

console .log (combine (input))

But I think it’s cleaner to use some generic helper functions to handle the grouping and summing, and make your code a little more specific to your requirements, with something like this:

const group = (fn) => (xs) => Object .values (
  xs .reduce ((a, x, _, __, k = fn (x)) => ((a [k] = [... a [k] || [], x]), a), {})
)

const sum = (ns) => ns .reduce ((a, b) => a + b, 0)

const combine = (xs) => 
  group (x => x.printingMaterialCode) (xs)
    .map ((xs) => ({
      printingMaterialCode : xs [0] .printingMaterialCode, 
      printingQuantity: sum (xs .map (x => x .printingQuantity)),
      approvedQuantity: sum (xs .filter (x => x.approved) .map (x => x .printingQuantity))
    }))

const input = [{siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0003"}, printingMaterialCode: "400000033", printingQuantity: 400, approved: !0}, {siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0002"}, printingMaterialCode: "400000033", printingQuantity: 600, campaignId: "DATAS00002"}, {siteDetails: {printerCode: "660103684", siteId: "UTT212303-STB-2040-0001"}, printingMaterialCode: "400000034", printingQuantity: 300, campaignId: "DATAS00002"}]

console .log (combine (input))

Here, group takes a key-extraction function and returns a function which takes an array of values and return an array of arrays of the elements which map to the same key. So for instance, to group an array of numbers by their last digits, we could do this:

group (n => n % 10) ([1, 12, 3, 52, 11, 56, 13, 32])
  //=> [[1, 11], [12, 52, 32], [3, 13], [56]]

If you’re using a library that has a groupBy function, then you could replace this with something like

const group = (fn) => (xs) => Object .values (groupBy (fn, xs))
                                      // or   groupBy (xs, fn))
                                      // or   groupBy (fn) (xs))
                                      // based on `groupBy`'s sig

sum, of course, just totals an array of numbers.

So combine then groups the numbers into like printingMaterialCode values, then maps the result into an object with your requested properties.

This has one difference from your requested structure, and that is that the group that has no approved entries, still has an approvedQuantity field; it’s just set to zero. I personally prefer this behavior, as consistent data is always a win. But if you wanted to change that, you could replace this line:

      approvedQuantity: sum (xs .filter (x => x.approved) .map (x => x .printingQuantity))

with this:

      ... (xs .some (x => x.approved) 
        ? {approvedQuantity: sum (xs .filter (x => x.approved) .map (x => x .printingQuantity))} 
        : {}
      )

However, that introduces some ugliness to our function.

Please signup or login to give your own answer.

Click here to cancel reply.

Mongodb – Applying group by and sum on array of objects in javascript

Answers