JS sum JSON value based on date month

by

JSON Data:

{
"client 1": [
    {
        "customer_branch_id": 128,
        "date": "2020-11-15",
        "total_after_tax": 174060
    },
    {
        "customer_branch_id": 128,
        "date": "2020-11-07",
        "total_after_tax": 94904
    }
],
"client 2": [
    {
        "customer_branch_id": 127,
        "date": "2020-11-16",
        "total_after_tax": 128160
    },
    {
        "customer_branch_id": 127,
        "date": "2020-11-07",
        "total_after_tax": 82320
    }
]

}

I am trying to sum total_after_tax data based on month from data field.

Also I want to set total_after_tax value to 0 if month doesn’t exist.

For example for client 1:

{
"client 1": [
    {
        "customer_branch_id": 128,
        "date": "1",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "2",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "3",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "4",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "5",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "6",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "7",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "8",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "9",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "10",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "11",
        "total_after_tax": 268964
    },{
        "customer_branch_id": 128,
        "date": "12",
        "total_after_tax": 0
    }
]

}

All months total_after_tax=0 except date=11 because its the only month that have result.

21 thoughts on “JS sum JSON value based on date month”

  1. I think a good approach would be to iterate through all the client keys of your JSON using a for…in loop.

    Inside this for, you can create an Array of 12 elements using the spread operator and map them to introduce the desired content.

    I’ve supposed the customer branch id is unique for each client, so that’s why i relied on the first element to set it.

    Finally, with the reduce method you can compare each month with the current index given by the .map. If it’s the same, then you can add the value of total_after_tax to the accumulator.

    In order to parse the date from the current value (curr.date) you can use the Date class as new Date(curr.date)

    Note that the getMonth method gives the month from 0 to 11, that’s why I added 1 in the if statement as well as in the initial value of the accumulator (month: index + 1)

    EDIT: I’ve assumed all the data is contained within a calendar year.

    const data = {
    "client 1": [
        {
            "customer_branch_id": 128,
            "date": "2020-11-15",
            "total_after_tax": 174060
        },
        {
            "customer_branch_id": 128,
            "date": "2020-11-07",
            "total_after_tax": 94904
        }
    ],
    "client 2": [
        {
            "customer_branch_id": 127,
            "date": "2020-11-16",
            "total_after_tax": 128160
        },
        {
            "customer_branch_id": 127,
            "date": "2020-11-07",
            "total_after_tax": 82320
        }] 
}
const result = {};

for (let client in data) {
    result[client] = [...Array(12)].map((element, index) => {
        return ({
            customer_branch_id: data[client][0].customer_branch_id,
            ...data[client].reduce((acc, curr) => {
                const date = new Date(curr.date);
                if (acc.date === date.getMonth() + 1) {
                    acc.total_after_tax += curr.total_after_tax;
                } 
                return acc
            }, {
                date: index + 1,
                total_after_tax: 0
            })
        })
    })
}
        
console.log(result)
    Reply

  2. You could try this for a start . However, you have to do more modifications to this code.

    const data = {
"client_1": [
    {
        "customer_branch_id": 128,
        "date": "1",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "2",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "3",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "4",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "5",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "6",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "7",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "8",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "9",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "10",
        "total_after_tax": 0
    },{
        "customer_branch_id": 128,
        "date": "11",
        "total_after_tax": 268964
    },{
        "customer_branch_id": 128,
        "date": "12",
        "total_after_tax": 2
    }
]};
const date = '12';
const total = data['client_1'].filter(record => record['date'] === date)
.map((record) => parseFloat(record['total_after_tax']))
.reduce((accumulator, current) => accumulator + current)

console.log(total)
    Reply

  3. This approach assumes that you want to extract the monthly data for a given client and a specific year. It could easily be extended to create an object keyed by client (and year?) with the same structure.

    It makes the assumption that a client has a single branch id associated. If not, we would need to understand the required output format for multiples.

    const sum = (ns) =>
  ns.reduce ((a, b) => a + b, 0)
  
const clientMonthlyTotal = (input, client, year) => {
  const custData = (input [client] || [])
    .filter (({date}) => date.slice(0, 4) == year)
  return [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] .map (month => ({
    customer_branch_id: (custData [0] || {}) .customer_branch_id,
    date: month,
    total_after_taxes: sum (
      custData .filter (
        ({date}) => date .startsWith (`${year}-${String (month) .padStart (2, '0')}`)
      ) .map (x => x .total_after_tax)
    )
  }))
}
                                                                 
const input = {"client 1": [{customer_branch_id: 128, date: "2020-11-15", total_after_tax: 174060}, {customer_branch_id: 128, date: "2020-11-07", total_after_tax: 94904}], "client 2": [{customer_branch_id: 127, date: "2020-11-16", total_after_tax: 128160}, {customer_branch_id: 127, date: "2020-11-07", total_after_tax: 82320}]}

console .log (
  clientMonthlyTotal (input, 'client 1', '2020')
)
    .as-console-wrapper {max-height: 100% !important; top: 0}

    We include the helper function sum, which simply totals an array of numbers.

    The main function first extracts the results for the client and filters to the relevant year. Then we map over the months, returning an object for each, with a branch_id taken from the first result (see the assumption above), a date referring to the month, and a total field found by filtering the customer data to those records matching the month, then extracting their total properties, and totaling the results with sum.

    There is an inefficiency here if this is to be used on really large data set (client object with hundreds of thousands of records). We run the filter function against the whole set for each month. An alternative would be to group the records by their month fields. While that would not be terribly difficult, it would add complexity to the code.

    If your data is constrained to a single year, we could simplify this a bit.

    Reply

Leave a Comment