I'm totally new to MongoDB and e from an SQL background.

I'm trying to do this :

Get the top Artists, based on the number of Dubs.

Data Structure :

Artists = [
  {
    "dubs": [{...},{...},{...}],
    "name": "The Doors",
    "createdAt": "2014-12-15T15:24:26.216Z",
    "updatedAt": "2014-12-15T15:24:26.216Z",
    "id": "548efd2a436c850000353f4f"
  },
  {
    "dubs": [],
    "name": "The Beatles",
    "createdAt": "2014-12-15T20:30:33.922Z",
    "updatedAt": "2014-12-15T20:30:33.922Z",
    "id": "548f44e90630d50000e2d61d"
  },
  {...}
]

So the result I seeking for would be something like this :

[{
 _id: "548ef6215755950000a9a0de",
 name:"The Doors",
 total: 3
},{
 _id: "548ef6215715300000a9a1f9",
 name:"The Beatles",
 total: 0
}]

I tried to :

Artist.native(function(err, collection) {

  collection.aggregate([ {
    $group: {
      _id: {
        name: "$name"
      },
      total: {
        $size: "$dubs"
      }
    }
  }, {
    $size: {
      total: -1
    }
  }], function(e, r) {
    if (e) res.serverError(e);
    console.log(r);
  });
});

Which gives me

[]

And :

Artist.native(function(err, collection) {
  if (err) return res.serverError(err);

  collection.aggregate({
    $group: {
      _id: "$name",
      total: {
        $sum: 1
      }
    }
  }, {
    $sort: {
      total: -1
    }
  }, function(e, r) {
    console.log(r);
    if (e) return res.serverError(e);
  });
});

Which gives me

[ { _id: 'The Beatles', total: 1 },
{ _id: 'The Doors', total: 1 } ]

Thanks

I'm totally new to MongoDB and e from an SQL background.

I'm trying to do this :

Get the top Artists, based on the number of Dubs.

Data Structure :

Artists = [
  {
    "dubs": [{...},{...},{...}],
    "name": "The Doors",
    "createdAt": "2014-12-15T15:24:26.216Z",
    "updatedAt": "2014-12-15T15:24:26.216Z",
    "id": "548efd2a436c850000353f4f"
  },
  {
    "dubs": [],
    "name": "The Beatles",
    "createdAt": "2014-12-15T20:30:33.922Z",
    "updatedAt": "2014-12-15T20:30:33.922Z",
    "id": "548f44e90630d50000e2d61d"
  },
  {...}
]

So the result I seeking for would be something like this :

[{
 _id: "548ef6215755950000a9a0de",
 name:"The Doors",
 total: 3
},{
 _id: "548ef6215715300000a9a1f9",
 name:"The Beatles",
 total: 0
}]

I tried to :

Artist.native(function(err, collection) {

  collection.aggregate([ {
    $group: {
      _id: {
        name: "$name"
      },
      total: {
        $size: "$dubs"
      }
    }
  }, {
    $size: {
      total: -1
    }
  }], function(e, r) {
    if (e) res.serverError(e);
    console.log(r);
  });
});

Which gives me

[]

And :

Artist.native(function(err, collection) {
  if (err) return res.serverError(err);

  collection.aggregate({
    $group: {
      _id: "$name",
      total: {
        $sum: 1
      }
    }
  }, {
    $sort: {
      total: -1
    }
  }, function(e, r) {
    console.log(r);
    if (e) return res.serverError(e);
  });
});

Which gives me

[ { _id: 'The Beatles', total: 1 },
{ _id: 'The Doors', total: 1 } ]

Thanks

• javascript
• mongodb
• mongodb-query
• sails.js
• aggregation-framework

1 Answer 1

Your first query was on the right track back you were using the the wrong pipeline operator.

Artist.native(function(err,collection) {

    collection.aggregate(
        [
            { "$project": {
                "_id": 1,
                "name": 1,
                "total": { "$size": "$dubs" }
            }}
        ],
        function(err,result) {
          if (err) return res.serverError(err);
          console.log(result);
        }
})

Of course the $size operator there requires that you need a MongoDB 2.6 or greater version, which you probably should do by now, but you can still to the same thing without the operator for measuring the array length:

Artist.native(function(err,collection) {

    collection.aggregate(
        [
            { "$project": {
                "_id": 1,
                "name": 1,
                "dubs": {
                    "$cond": [
                       { "$eq": [ "$dubs", [] ] },
                       [0],
                       "$dubs"
                    ]
                }
            }},
            { "$unwind": "$dubs" },
            { "$group": {
                "_id": "$_id",
                "name": { "$first": "$name" },
                "total": { 
                    "$sum": {
                        "$cond": [
                            { "$eq": [ "$dubs", 0 ] },
                            0,
                            1
                        ]
                    }
                }
            }}
        ],
        function(err,result) {
          if (err) return res.serverError(err);
          console.log(result);
        }
})

That does the same thing by counting the members of the array, but instead you would need to $unwind the array elements in order to count them. So it can still be done but is not as efficient.

Additionally you need to handle the cases where the array is truly blank but present because of how $unwind treats an empty array []. If there was no content then the document that contained such an element would be removed from the results. In a similar way you would need to use $ifNull to set an array where the document did not even contain an element for $unwind to not result in an error.

Really if you intend to do this kind of query on a regular basis, then you should maintain a "total" field in the document rather than seek to calculate it first. Use the $inc operator along with operations such as $push and $pull to keep a tally of the current array length.

That does move away from the general Waterline philosophy a bit, but you have already introduced native aggregation operations and it's not that much more of a stretch to realize you are getting better performance from using native operations in other areas as well.

So with documents like these:

{
  "dubs": [{},{},{}],
  "name": "The Doors",
  "createdAt": "2014-12-15T15:24:26.216Z",
  "updatedAt": "2014-12-15T15:24:26.216Z",
  "id": "548efd2a436c850000353f4f"
},
{
  "dubs": [],
  "name": "The Beatles",
  "createdAt": "2014-12-15T20:30:33.922Z",
  "updatedAt": "2014-12-15T20:30:33.922Z",
  "id": "548f44e90630d50000e2d61d"
}

You get exactly the results you want in each case:

{
    "_id" : ObjectId("5494b79d7e22da84d53c8760"),
    "name" : "The Doors",
    "total" : 3
},
{
    "_id" : ObjectId("5494b79d7e22da84d53c8761"),
    "name" : "The Beatles",
    "total" : 0
}