mongodb聚合查询总结

mysql与mongodb聚合对比

mysql	mongodb
where	$match
group by	$group
having	$match
select	$project
order by	$sort
limit	$limit
sum()	$sum
count()	$sum
join	$lookup

常用例子

数据库文档内容

/* 1 */
{
“_id” : ObjectId(“512bc95fe835e68f199c8686”),
“author” : “dave”,
“score” : 80.0,
“views” : 100.0
}

/* 2 */
{
“_id” : ObjectId(“512bc962e835e68f199c8687”),
“author” : “dave”,
“score” : 85.0,
“views” : 521.0
}

/* 3 */
{
“_id” : ObjectId(“55f5a192d4bede9ac365b257”),
“author” : “ahn”,
“score” : 60.0,
“views” : 1000.0
}

/* 4 */
{
“_id” : ObjectId(“55f5a192d4bede9ac365b258”),
“author” : “li”,
“score” : 55.0,
“views” : 5000.0
}

/* 5 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b259”),
“author” : “annT”,
“score” : 60.0,
“views” : 50.0
}

/* 6 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b25a”),
“author” : “li”,
“score” : 94.0,
“views” : 999.0
}

/* 7 */
{
“_id” : ObjectId(“55f5a1d3d4bede9ac365b25b”),
“author” : “ty”,
“score” : 95.0,
“views” : 1000.0
}

1.一次性查出指定的多个作者名下的分数和查看数，每个作者的记录数不超过100条

js:

// An highlighted block
db.getCollection('articles').aggregate([
{$match:{author:{$in:["dave", "ahn", "li", "annT", "ty"]}}},
{$group:{"_id":"$author",authorInfo:{$push:{"score":"$score","views":"$views"}}}},
{$project:{"author_info":{$slice:["$authorInfo", 100]}}},
])

查询结果：
/* 1 */
{
“_id” : “ty”,
“author_info” : [
{
“score” : 95.0,
“views” : 1000.0
}
]
}

/* 2 */
{
“_id” : “dave”,
“author_info” : [
{
“score” : 80.0,
“views” : 100.0
},
{
“score” : 85.0,
“views” : 521.0
}
]
}

/* 3 */
{
“_id” : “ahn”,
“author_info” : [
{
“score” : 60.0,
“views” : 1000.0
}
]
}

/* 4 */
{
“_id” : “li”,
“author_info” : [
{
“score” : 55.0,
“views” : 5000.0
},
{
“score” : 94.0,
“views” : 999.0
}
]
}

/* 5 */
{
“_id” : “annT”,
“author_info” : [
{
“score” : 60.0,
“views” : 50.0
}
]
}

“$group”

$group使用时

语法

// An highlighted block
{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

参数以及用法说明
1. _id字段必须，值可以为null
2. accumulator1必须要有，分组了之后，必须要对分组的记录做些什么(不然只会查到_id这一个)，简单的统计个数、平均数’，复杂一点的用$push把想要的数据放到数组，如上所示。