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.
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.
You could try this for a start . However, you have to do more modifications to this code.
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.
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), adate
referring to the month, and atotal
field found by filtering the customer data to those records matching the month, then extracting theirtotal
properties, and totaling the results withsum
.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.