CODEWITHSUNDEEP
mongodbaggregation-framework
Reece Langerock
Reece Langerock

Reputation: 13

MongoDB - Get Count of each unique value for each field in document

I've spent the past two days searching for a better solution for this, but haven't come up with anything.

Basically, I have a collection containing documents like the this: 

{ _id:5b5c92014fdd1f82c288530d
combine_id:1234
forty:4.65
broad:10.66
shuttle:4.18
threeCone:7.08
vert:40
bench:23
}

I want to be able to, in one query, get the sum of each unique value for each field. Something like: forty: [{time: 4.4, count: 7}, {time: 4.41, count: 11}, ...] but for all six fields in the document. What I'm trying to do is create a bell-curve of values for each field.

The query I have now is the below, but I would have to run it separately for each field so I think there has to be a better and more elegant solution to this.

db.combine.aggregate([
{   $group: {
        _id: {forty: '$forty'},
        count: { $sum: 1 }
} } ]);

Hopefully this is possible, and I've provided enough info. Thanks

Upvotes: 1

Views: 512

Answers (1)

chridam
chridam

Reputation: 103455

$facet allows you to create multi-faceted aggregation pipelines within a single stage on the same set of input documents thus you can get the counts for each field as follows:

db.combine.aggregate([
    { '$facet': {
        'forty':    [{ '$group': { '_id': '$forty', 'count': { '$sum': 1 } } }],
        'broad':    [{ '$group': { '_id': '$broad', 'count': { '$sum': 1 } } }],
        'shuttle':  [{ '$group': { '_id': '$shuttle', 'count': { '$sum': 1 } } }],
        'threeCone':[{ '$group': { '_id': '$threeCone', 'count': { '$sum': 1 } } }],
        'vert':     [{ '$group': { '_id': '$vert', 'count': { '$sum': 1 } } }],
        'bench':    [{ '$group': { '_id': '$bench', 'count': { '$sum': 1 } } }]
    } }
])

To replace the _id key you would need to append a $project pipeline stage for each facet i.e.

[
    { '$group': { 
        '_id': <facet_key>, 
        'count': { '$sum': 1 } 
    } },
    { '$project': {
        '_id': 0,
        'time': '$_id',
        'count': 1
    } }
]

With a bit of refactoring, if the six fields are known and fixed, you can dynamically create a pipeline as follows:

/* create the facet pipeline */
const getFacetPipeline = key => ([
    { '$group': { 
        '_id': '$'+key, 
        'count': { '$sum': 1 } 
    } },
    { '$project': {
        '_id': 0,
        'time': '$_id',
        'count': 1
    } }
]);

/* create the overall aggregate pipeline */
const getAggregationPipeline = keys => (
    keys.reduce((acc, key) => {
        acc['$facet'][key] = getFacetPipeline(key);
        return acc;
    }, { '$facet': {} })
);

/* get the pipeline for the six fields */
const pipeline = getAggregationPipeline([
    'forty', 
    'broad', 
    'shuttle', 
    'threeCone', 
    'vert', 
    'bench'
]);

/* run the aggregate pipeline */
db.combine.aggretate([pipeline]);

Upvotes: 2

Related Questions