MongoDB get count of particular key in an array

Question

In mongoDB, how can we get the count of particular key in an array

{
 "_id" : ObjectId("52d9212608a224e99676d378"),
 "business" : [
  {
   "name" : "abc",
   "rating" : 4.5
  },
  {
   "name" : "pqr"
  },
  {
   "name" : "xyz",
   "rating" : 3.6
  }
 ]
}

in the above example, business is an array (with "name" and/or "rating" keys)

How can i get the count of business array with only "rating" key existing ?

Expected output is : 2

vbo · Accepted Answer · 2014-01-20 09:01:19Z

8

Looks like you have to use Aggregation Framework. In particular you need to $unwind your array, then match only elements with rating field included, then $group documents back to original format.

Try something like this:

db.test.aggregate([
    { $match: { /* your query criteria document */ } },
    { $unwind: "$business" },
    { $match: {
        "business.rating": { $exists: 1 }
      }
    },
    { $group: {
        _id: "$_id",
        business: { $push: "$business" },
        business_count: { $sum: 1 }
      }
    }
])

Result will look like the following:

{
  _id: ObjectId("52d9212608a224e99676d378"),
  business: [
    { name: "abc", rating: 4.5 },
    { name: "xyz", rating: 3.6 }
  ],
  business_count: 2
}

UPD Looks like OP doesn't want to group results by wrapping document _id field. Unfortunately $group expression must specify _id value, otherwise it fails with exception. But, this value can actually be constant (e.g. plain null or 'foobar') so there will be only one resulting group with collection-wise aggregation.

edited Jan 20, 2014 at 9:01

answered Jan 17, 2014 at 13:12

vbo

13.6k1 gold badge27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ixe013 Over a year ago

I like your code sample (and it works, so +1). In the future, please use the test database (not foo) so we can save typing and editing in the mongo shell.... and SO comments ;)

vbo Over a year ago

@ixe013 OK ;) BTW foo is not a database name here - it's a collection name. And collection test does not have any special meaning (unlike test database which is the default one in mongo shell).

ixe013 Over a year ago

Yes, collection. When I read the question, I created the OP document in the default test collection. No biggie, I guess I'm just grumpy this morning after a short night!

Michal Gasek Over a year ago

I like the solution. But to filter out documents that do not have rating key in any element of business array I would add "{ $match : { business : { $elemMatch : { rating : { $exists : true } } } } }" before $unwind pipe. See Early filtering

vbo Over a year ago

@user3195726 Great suggestion! It's a kind of optimization though, and I am just showing the general technique here. So let's keep this as a comment.

|

Collectives™ on Stack Overflow

MongoDB get count of particular key in an array

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related