MongoDB的聚合示例命令和Mongoose＃Aggregate（）在node.js中的用法
▪️今天的困惑
-
- MongoDBで、groupbyとかdistinctとかどうするの？
- それをnode.jsで実装する場合はどうするの？
解决问题
-
- MongoDBのaggregateを使う。
- node.jsでMongoDBを使う時は、monngooseを使う。
首先是一个参考链接集。
阅读并实际筛选数据后，你会逐渐掌握窍门。
-
- Aggregation — MongoDB Manual 3.4
-
- Aggregation Pipeline Operators — MongoDB Manual 3.4
-
- Aggregation with the Zip Code Data Set — MongoDB Manual 3.4
-
- MongoDB Aggregation
MySQLに馴染んでいる人はこちらもどうでしょうか。MySQLのクエリをMongoのクエリに直してくれるというものです。
Query Mongo: MySQL to MongoDB Query Translator
用MongoDB进行简单的聚合操作的示例。
准备进行聚合数据的准备工作
- ちなみに、MongoDBでの日付の挿入は以下の通り
db.example.insert({"date":new Date(Date.now())});
- monngooseは、collectionを複数形で作成する必要があります。
稍后会提到，如果按照以下方式创建Topic模型，则MongoDB的目标应该是“topic”，但实际上是“topics”，所以请小心避免陷阱。
var TopicModel = mongoose.model('Topic', topicSchema);
- サンプルデータをinsertします
db.topics.insert(
[
{
posted_at: new Date(Date.now()),
posted_by: 'tanaka',
data: 'hogehoge',
room:'room_1',
topic_text:'Todays Weather',
},
{
posted_at: new Date(Date.now()),
posted_by: 'okamoto',
data: 'hogehoge',
room:'room_2',
topic_text:'Todays Weather',
},
{
posted_at: new Date(Date.now()),
posted_by: 'tanaka',
data: 'hogehoge',
room:'room_2',
topic_text:'Todays Weather',
},
{
posted_at: new Date(Date.now()),
posted_by: 'yamamoto',
data: 'hogehoge',
room:'room_1',
topic_text:'New Year',
},
{
posted_at: new Date(Date.now()),
posted_by: 'tanaka',
data: 'hogehoge',
room:'room_1',
topic_text:'Count Down',
},
]
)
-
- データをselectします
prettyをつけてあげると、分かりやすいJSON形式で出力してくれます。
db.topics.find().pretty()
执行Aggregation
聚合功能类似于Unix的管道“|”操作符。
-
- count
posted_by毎の数
db.topics.aggregate([
{$group : {_id : "$posted_by", num : {$sum : 1}}}
])
{ "_id" : "yamamoto", "num" : 1 }
{ "_id" : "okamoto", "num" : 1 }
{ "_id" : "tanaka", "num" : 3 }
-
- group
room毎にgroupby
db.topics.aggregate( [
{ $group : { _id : { room: "$room" }, profile: { $push: "$$ROOT" }} }
] ).pretty()
{
"_id" : {
"room" : "room_2"
},
"profile" : [
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16c"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "okamoto",
"data" : "hogehoge",
"room" : "room_2",
"topic_text" : "Todays Weather"
},
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16d"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "tanaka",
"data" : "hogehoge",
"room" : "room_2",
"topic_text" : "Todays Weather"
}
]
}
{
"_id" : {
"room" : "room_1"
},
"profile" : [
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16b"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "tanaka",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "Todays Weather"
},
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16e"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "yamamoto",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "New Year"
},
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16f"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "tanaka",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "Count Down"
}
]
}
-
- match+ group + sort
room = room_1
posted_atのソート
topic_text毎
db.topics.aggregate( [
{ $match: { room : "room_1"}},
{ $sort: {posted_at:1}},
{ $group : { _id : { topic_text: "$topic_text" }, profile: { $push: "$$ROOT" }} }
] ).pretty()
{
"_id" : {
"topic_text" : "Count Down"
},
"profile" : [
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16f"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "tanaka",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "Count Down"
}
]
}
{
"_id" : {
"topic_text" : "New Year"
},
"profile" : [
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16e"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "yamamoto",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "New Year"
}
]
}
{
"_id" : {
"topic_text" : "Todays Weather"
},
"profile" : [
{
"_id" : ObjectId("5861f90669e5e0b2eb21f16b"),
"posted_at" : ISODate("2016-12-27T05:15:50.603Z"),
"posted_by" : "tanaka",
"data" : "hogehoge",
"room" : "room_1",
"topic_text" : "Todays Weather"
}
]
}
使用mongoose和node.js进行实现
好，我們將用Node.js編寫上述使用aggregate方法執行的內容。我們將通過運行npm install來安裝並使用mongoose。
- Mongoose API v4.7.5
var mongoose = require('mongoose');
var Schema = mongoose.Schema;
//Database connection
var uristring = 'mongodb://localhost/jp';
var mongoOptions = { };
mongoose.connect(uristring, mongoOptions, function (err, res) {
if (err) {
console.log('Error when connecting to: ' + uristring + '. ' + err);
}
else {
console.log('Successfully connected to: ' + uristring);
}
});
//Schemas:
var topicSchema = new Schema({
posted_by: {
type: String,
default: ''
},
posted_at: {
type: Date,
default: Date.now
},
data: {
type: String,
default: JSON.stringify({})
},
room: {
type: String,
default: ''
},
topic_text: {
type: String,
default: ''
}
});
//Define Models
var TopicModel = mongoose.model('Topic', topicSchema);
getTopicProfile("room_1", function(err, data){
if(!err){
console.log(JSON.stringify(data));
}
});
//Aggregation function
function getTopicProfile(room_name, callback) {
TopicModel.aggregate([
{ $match: {
room: room_name
}},
{ $sort: {posted_at:1}},
{ $group: {
_id : { topic_text: "$topic_text" },
profile: { $push: "$$ROOT" }
}
}
], function (err, result) {
if (err) {
console.log(err);
return;
}
console.log(JSON.stringify(result));
});
}
-
- コードはこちらを参考にさせていただきました。
Aggregate data from MongoDB with Node.js and mongoose
