CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alex
Alex

Reputation: 2959

How to group by the first element of an array?

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 ?

Upvotes: 10

Views: 9564

Answers (2)

Xavier Guihot
Xavier Guihot

Reputation: 61646

Starting Mongo 4.4, the aggregation operator $first can be used to access the first element of an array.

Which gives in our case, within a group stage:

// { "yearpublished": ["1979", "2003"] }
// { "yearpublished": ["1954", "1979"] }
// { "yearpublished": ["1954"] }
db.collection.aggregate([
  { $group: {
    "_id": { $first: "$yearpublished" },
    "count": { $sum: 1 }
  }}
])
// { "_id" : "1954", "count" : 2 }
// { "_id" : "1979", "count" : 1 }

Upvotes: 1

Blakes Seven
Blakes Seven

Reputation: 50406

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.

Upvotes: 13

Related Questions