mongodb笔记

1.查询数组只返回数组中存在的该条件的数组. db.tablename.find({"arryname.fieldname":value},{"arryname.$":1}) 如果有多条匹配，只返回第一条

2.根据mongodb自带的objectId(_id)来查询

    db.wf_definitions.find({"_id":new ObjectId("53747052e4b080e26b0c0f22")})

    java代码：

    BasicDBObject bdb=new BasicDBObject("_id",new ObjectId("53747052e4b080e26b0c0f22"));

    dbCollection.find(bdb);

3.删除数组对象内嵌文档

db.*****.update(
    {
        "_id" : new ObjectId("537ee7087c1f6fef727d9889"),
        "stepdefinitions.stepnumber":"end"
    },
     {
        "$pull":{"stepdefinitions":{"stepnumber":"end"}}
    }
);

4.mongodb使用了剥离后需要统计记录的条数

db.****.aggregate({"$unwind":"$*"},{"$match":{"****":"admin"}}, {"$group": { "_id": null, "count": { "$sum": 1 } }})

 

5.mongodb的group by  类似  mapreduce使用。

  map的必须要使用emit函数，reduce 的vals 是emit第二个参数的集合

   finalize  会重新解析 返回数据

 

db.runCommand({ mapreduce: "wf_definitions", 
 map : function () {
var desc=this.description;

 if(this.status=='disable'){
  desc=desc+'(已停用)';
 }
emit(this.defname,{"description":desc,"status":this.status});
},
 reduce : function(key, vals) {  
 var desc=vals[0];
 var i=0;
 for(;i< vals.length;i++){
  if(vals[i].status=='enable'){
  desc=vals[i];
   break;
  }
 }
    return desc;  
} ,
 finalize : function (key, reduced) {
 return {"defname":key,"description":reduced.description,"status":reduced.status};
},
 query : { "$or" : [{ "defname" : { "$in" : ["工作转交子流程", "慰问子流程_产假", "员工", "新建测试使用工作流"] } }, { "status" : "enable" }] },
 keeptemp : false
 });

 

 

 mongodb和sql 的转换对应:

MongoDB  MySQL

aggregate: "DenormAggTable"	FROM DenormAggTable
{ $match : { Filter1 : {$in : ['A','B']}, Filter2 : 'C', Filter3 : {$gt : 123} } }	WHERE (Filter1 IN (’A’,’B’)) AND (Filter2 = ‘C’) AND (Filter3 > 123)
{ $project : { Dim1 : 1, Dim2 : 1, Measure1 : 1, Measure2 : 1, lessThanAHundred : { $cond: [ {$lt: ["$Measure2", 100] }, "$Measure2", // if 0] // else } } }	CASE WHEN Measure2 < 100 THEN Measure2 END
{ $group : { _id : {Dim1 : 1, Dim2 : 1}, MSum : {$sum : "$Measure1"}, RecordCount : {$sum : 1}, MAvg : {$avg : "$Measure2"}, MMin : {$min : "$Measure1"}, MMax : {$max : "$lessThanAHundred"} } }	SELECT Dim1, Dim2, SUM(Measure1) AS MSum, COUNT(*) AS RecordCount, AVG(Measure2) AS MAvg, MIN(Measure1) AS MMin MAX(CASE WHEN Measure2 < 100 THEN Measure2 END) AS MMax   GROUP BY Dim1, Dim2
{ $match : {MMin : {$gt : 0}} }	HAVING (MMin > 0)
{ $sort : {RecordCount : -1} }	ORDER BY RecordCount DESC
{ $limit : 8 }, { $skip : 4 }	LIMIT 4, 8