Group sum and transform json object with values in nested array

Question

I am trying to aggregate and transform the following json :

[
{ 
    "orderId" : "01",
    "date" : "2017-01-02T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 12,
            "itemQuantity": 10
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 4
        }
    ]
},
{
    "orderId": "02",
    "date" : "2017-01-08T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 15,
            "itemQuantity": 2
        },
        {
            "itemId": 101,
            "itemCost": 20,
            "itemQuantity": 5
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 1
        }
    ]
},
{
    "orderId": "03",
    "date" : "2017-02-08T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 15,
            "itemQuantity": 2
        },
        {
            "itemId": 101,
            "itemCost": 20,
            "itemQuantity": 5
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 1
        }
    ]
}]

into an object that is grouped by itemId, and then aggregated by quantity, and aggregated by total cost (item cost * item quantity for each order) by month. Example:

[
    {
        "itemId": 100,
        "period": [
            {
                "month": "01/17",
                "quantity": 12,
                "cost": 130
            }
        ]
    },
    {
        "itemId": 101,
        "period": [
            {
                "month": "01/17",
                "quantity": 5,
                "cost": 100
            },
            {
                "month": "02/17",
                "quantity": 5,
                "cost": 100
            }
        ]
    },
    {
        "itemId": 102,
        "period": [
            {
                "month": "01/17",
                "quantity": 5,
                "cost": 125
            },
            {
                "month": "02/17",
                "quantity": 1,
                "cost": 25
            }
        ]
    }
]

I have a small indention on my desk in which I have been beating my head trying to figure how to do this using native map/reduce or lodash.

Answer 1

You can do like this:

var orders = [{orderId:"01",date:"2017-01-02T06:00:00.000Z",items:[{itemId:100,itemCost:12,itemQuantity:10},{itemId:102,itemCost:25,itemQuantity:4}]},{orderId:"02",date:"2017-01-08T06:00:00.000Z",items:[{itemId:100,itemCost:15,itemQuantity:2},{itemId:101,itemCost:20,itemQuantity:5},{itemId:102,itemCost:25,itemQuantity:1}]},{orderId:"03",date:"2017-02-08T06:00:00.000Z",items:[{itemId:100,itemCost:15,itemQuantity:2},{itemId:101,itemCost:20,itemQuantity:5},{itemId:102,itemCost:25,itemQuantity:1}]}];

// First, map your orders by items
var items = {};
orders.forEach(function(order) {

    // set the month of each order
    var month = new Date(order.date);
    month = ('0' + (month.getMonth() + 1)).slice(-2) + '/' +  String(month.getFullYear()).slice(-2);
    
    // for each item in this order
    order.items.forEach(function(item) {
    
        // here we already have both keys: "id" and "month"
        // then, we make sure they have an object to match
        var id = item.itemId;
        if (!items[id]) {
            items[id] = {};
        }
        if (!items[id][month]) {
            items[id][month] = { cost:0, quantity:0 };
        }
        
        // keep calculating the total cost
        items[id][month].cost += item.itemCost * item.itemQuantity;
        items[id][month].quantity += item.itemQuantity;
    });
});

// Now, we format the calculated values to your required output:
var result = Object.keys(items).map(function(id) {
    var obj = {
        itemId: id,
        period: Object.keys(items[id]).map(function(month) {
            items[id][month].month = month;
            return items[id][month];
        }),
    };
    return obj;
});

console.log(result);

Hope it helps.

Answer 2

You could use this transformation:

const result = Object.values(myList.reduce( (acc, o) => {
    const month = o.date.substr(5,2) + '/' + o.date.substr(2,2);
    return o.items.reduce ( (acc, item) => {
        const it = acc[item.itemId] || {
                itemId: item.itemId,
                period: {}
            }, 
            m = it.period[month] || {
                month: month,
                quantity: 0,
                cost: 0
            };
        m.cost += item.itemCost * item.itemQuantity;
        m.quantity += item.itemQuantity;
        it.period[month] = m;
        acc[item.itemId] = it;
        return acc;
    }, acc);
}, {})).map( o => 
    Object.assign({}, o, { period: Object.values(o.period) }) 
);

const myList = [
{ 
    "orderId" : "01",
    "date" : "2017-01-02T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 12,
            "itemQuantity": 10
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 4
        }
    ]
},
{
    "orderId": "02",
    "date" : "2017-01-08T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 15,
            "itemQuantity": 2
        },
        {
            "itemId": 101,
            "itemCost": 20,
            "itemQuantity": 5
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 1
        }
    ]
},
{
    "orderId": "03",
    "date" : "2017-02-08T06:00:00.000Z",
    "items" : [
        {
            "itemId": 100,
            "itemCost": 15,
            "itemQuantity": 2
        },
        {
            "itemId": 101,
            "itemCost": 20,
            "itemQuantity": 5
        },
        {
            "itemId": 102,
            "itemCost": 25,
            "itemQuantity": 1
        }
    ]
}];

const result = Object.values(myList.reduce( (acc, o) => {
    const month = o.date.substr(5,2) + '/' + o.date.substr(2,2);
    return o.items.reduce ( (acc, item) => {
        const it = acc[item.itemId] || {
                itemId: item.itemId,
                period: {}
            }, 
            m = it.period[month] || {
                month: month,
                quantity: 0,
                cost: 0
            };
        m.cost += item.itemCost * item.itemQuantity;
        m.quantity += item.itemQuantity;
        it.period[month] = m;
        acc[item.itemId] = it;
        return acc;
    }, acc);
}, {})).map( o => 
    Object.assign({}, o, { period: Object.values(o.period) }) 
);

console.log(result);

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