SailsJS& MongoDB聚合框架遇到自定义查询的麻烦 [英] SailsJS & MongoDB Aggregation framework troubles with custom queries

问题描述

我对MongoDB完全陌生，来自SQL背景.

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

我正在尝试这样做:

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

数据结构:

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
}]

我试图:

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);
  });
});

哪个给我

[]

然后:

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);
  });
});

哪个给我

[ { _id: 'The Beatles', total: 1 },
{ _id: 'The Doors', total: 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);
        }
})

当然 $size 运算符要求您需要一个MongoDB 2.6或更高版本，您可能现在应该这样做，但是如果没有运算符来测量数组长度，您仍然可以做到这一点:

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);
        }
})

这通过计数数组的成员来完成相同的操作，但是您需要 $unwind 数组元素以对其进行计数.因此它仍然可以完成，但效率不高.

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.

此外，由于 $ifNull > 设置一个数组，其中文档甚至不包含 $unwind 不会导致错误.

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.

真的，如果您打算定期进行这种查询，则应在文档中保留一个总计"字段，而不要首先进行计算.使用 $inc 运算符使用 $push 之类的操作和 $pull 来保持当前数组长度的计数.

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.

这确实偏离了一般的Waterline理念，但是您已经引入了本机聚合操作，要了解通过在其他领域中使用本机操作也可以获得更好的性能，这并不是一件容易的事.

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.

对于这样的文档:

{
  "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
}

这篇关于SailsJS& MongoDB聚合框架遇到自定义查询的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！