How to group by the first element of an array?

Question

I work with documents such as:

{
    "foo" : { "objectid" : "39", "stuff" : "65" },
    "yearpublished" : ["1979"],
    "bar" : "1263"
}

yearpublished is an array and I would like to group my collection by the first value in this array and then count the amount of documents related.

I have written this query:

db.foobar.aggregate([
    { $group : {
        _id : '$yearpublished',
        count: { $sum: 1 }
    }}
])

and get:

{
    "result" : [ 
        { "_id" : ["1923"], "count" : 1.0000000000000000 }, 
        { "_id" : ["1864"], "count" : 1.0000000000000000 }
    ]
}

But I'm looking for this kind of result (i. e. first element only):

{
    "result" : [ 
        { "_id" : "1923", "count" : 1.0000000000000000 }, 
        { "_id" : "1864", "count" : 1.0000000000000000 }
    ]
}

I've also tried _id : { $first: '$yearpublished.0' }, or _id : { $first: '$yearpublished[0]' }, without success.

How can I group by the first element of the array yearpublished ?

Answer 1

Unfortunately right now the only way to do this is to extract the $first element from the array after processing $unwind. Then of course you would have to $group again:

db.foobar.aggregate([
    { "$unwind": "$yearpublished" },
    { "$group": {
        "_id": "$_id",
        "yearpublished": { "$first": "$yearpublished" }
    }},
    { "$group": {
        "_id": "$yearpublished",
        "count": { "$sum": 1 }
    }}
])

Thats the only current way to get the "first" element from an array, by deconstructing it and using the operator to get the entry.

Future releases will have $arrayElemAt which can do this by index within a single stage:

db.foobar.aggregate([
    { "$group": {
        "_id": { "$arrayElemAt": [ "$yearpublished", 0 ] },
        "count": { "$sum": 1 }
    }}
])

But presently the aggregation framework does not deal with "dot notation" index usage such as standard "projection" with .find() does, and will not, hence the new operations.