group array with sub array

Question

I'm trying to find the easiest way to take an array that has like information and create an array with sub array. The newly created parent array should also sum the values that are found in the sub-array. This is the equivalent of an SQL statement that uses sum/group by.

This is the original array

[
    {
        'userId':1,
        'userName':'bob',
        'category':'shoes',
        'count':2
    },
    {
        'userId':1,
        'userName':'bob',
        'category':'rocks',
        'count':4
    },
    {
        'userId':1,
        'userName':'bob',
        'category':'bags',
        'count':3
    },
    {
        'userId':2,
        'userName':'sue',
        'category':'shoes',
        'count':1
    },
    {
        'userId':2,
        'userName':'sue',
        'category':'rocks',
        'count':7
    },
    {
        'userId':2,
        'userName':'sue',
        'category':'bags',
        'count':4
    },
]

This is what I want the newly created array to look like:

[
    {
        'userId':1,
        'userName':'bob',
        'purchases': [
            {'category':'shoes','count':'2'},
            {'category':'rocks','count':'4'},
            {'category':'bags','count':'3'}
        ],
        'totalCount' : 9
    },
    {
        'userId':2,
        'userName':'sue',
        'purchases': [
            {'category':'shoes','count':'1'},
            {'category':'rocks','count':'7'},
            {'category':'bags','count':'4'}
        ],
        'totalCount' : 12
    },  
]

Answer 1

You can do it with Alasql JavaScript SQL library:

This is an example:

var res = alasql('SELECT userId, FIRST(userName) as userName, \
                     ARRAY({category:category,[count]:[count]}) AS purchases,\
                     SUM([count]) AS totalCount FROM ? GROUP BY userId',[data]);

Try this example at jsFiddle. Compare speed with Lodash' solution at jsPerf.

Comments:

1. I put [count] into square brackets, because COUNT is SQL reserved keyword
2. ARRAY() aggregator produce an array with values

Answer 2

First groupBy userId and then map the groups to get the desired structure:

    var groups = _.groupBy(list, 'userId');

    var result = _.map(groups, function(user){
        return {
            userId: user[0].userId,
            userName: user[0].userName,
            purchases: _.map(user, function(purchase){
                return {
                    category: purchase.category,
                    count: purchase.count
                }
            }),
            totalCount: _.reduce(user, function(memo, purchase){
                return memo + purchase.count;
            }, 0)
        }
    });