mongo常用语句

原创

江南野鹤 2022-10-21 09:10:36 ©著作权

文章标签 json 3d 字段 文章分类 运维

©著作权归作者所有：来自51CTO博客作者江南野鹤的原创作品，请联系作者获取转载授权，否则将追究法律责任

只列出指定字段
db.getCollection('PUBLICACCOUNTS').find({},{NickName:1,UserName:1,FID:1,_id:0})
获取微信公众号列表
db.getCollection('RELATIONALINFO').find({oidOrUid:{$ne:""},"folderInfo.available":1,sourceId:{$ne:""},accountType:1,ownerId:{$in:[0,1]}})
删除全部微信文章
db.getCollection('FollowMessage').remove({source:"weixin"})
修改微信公众号的lastPublicTime,lastGrabbeTime
db.getCollection('RELATIONALINFO').update({accountType:1},{$set:{lastPublicTime: ISODate("1999-12-31T16:00:00.000Z"),lastGrabbeTime: ISODate("1999-12-31T16:00:00.000Z")}},{multi:true})
获取绑定了3个folder的账号
db.getCollection('RELATIONALINFO').find({folderInfo:{$size:3}})
列出指定字段排序
db.getCollection('FollowMessage').find({source:"weixin"}, {originalId: 1,"data.title": 1, _id:0,publishedAt:1 }).sort({publishedAt:-1})
获取广华的微博
db.getCollection('FollowMessage').find({oidOrUid:"2244734372"}).sort({createdAt:-1})
查找某些台的歌曲 in
db.getCollection('FollowMessage').find({stationId:{$in:["97105541-2563-E411-A33D-AC853D9F52F0","6a396fc0-5465-e411-a33d-ac853d9f52f0","bd6104a9-30c7-43c8-b907-d59911be5081","d862ce91-661c-4e50-b055-a4f901308274","e92132ce-952b-e511-b870-a35aa304d87f","6b4a168f-ba09-e511-b870-a35aa304d87f","4e7e41a0-653a-e511-b870-a35aa304d87f","232d2efb-1363-e411-a33d-ac853d9f52f0","3b2d490a-89a0-4f2d-9815-a4fa0129728e"]}})
根据来源进行分类统计 group
db.getCollection('FollowMessage').group({
key:{'source':1},reduce:function(doc,aggr){aggr.count++},initial:{'count':0}
})
不等于
price: { $ne: 1.99 }

统计某段时间内按照公众号消息量排名

db.getCollection('FROMWECHATDATA').aggregate([
{$match:{createdAt:{$gt:ISODate('2017-06-01 01:08:47.483Z')},createdAt:{$lt:ISODate('2017-06-30 01:08:47.483Z')}}},
{$group:{_id:"$TOUSERNAME", num_tutorial : {$sum : 1}}},
{$sort:{'num_tutorial':-1}}
])

更新
update命令格式：
db.collection.update(criteria,objNew,upsert,multi)
参数说明：
criteria：查询条件
objNew：update对象和一些更新操作符
upsert：如果不存在update的记录，是否插入objNew这个新的文档，true为插入，默认为false，不插入。
multi：默认是false，只更新找到的第一条记录。如果为true，把按条件查询出来的记录全部更新。
更新库中
db.getCollection('RELATIONALINFO').update({accountType:1,oidOrUid:''},{$set:{isAvailable:0}},false,true)
没绑定房间的且绑定了folder的设置为不可用
db.getCollection('RELATIONALINFO').update({folderInfo:{$size:1},roomInfo:{$size:0},"folderInfo.FolderId":{$ne:null}},{$set:{isAvailable:0}},false,true)
distinct
db.channelcloudspace.distinct('originalOrgainizationGuid')

db.getCollection('channelcloudspace').find({organizationGuid:null})

搜索关键字查看公众号授权情况
var p = db.getCollection('PUBLICACCOUNTS').findOne({NickName:/拇指/})
db.getCollection('WeiXinNotify').find({AuthorizerAppid:p.AppId}).forEach(function(o){
printjson(p.NickName+'--'+o.InfoType+'--'+o.AddTime);
printjson(o.AddTime);
});

var p = db.getCollection('PUBLICACCOUNTS').findOne({NickName:/七星/})
printjson(p.UserName);
db.getCollection('FROMWECHATDATA').find({TOUSERNAME:p.UserName}).sort({createdAt:-1}).limit(1)

db.getCollection('WeiXinNotify').find({AuthorizerAppid:p.AppId}).forEach(function(o){
printjson(p.NickName+'--'+o.InfoType+'--'+o.AddTime);
printjson(o.AddTime);
});
----mongo查找某关键字公众号最新的消息及授权情况----
var p = db.getCollection('PUBLICACCOUNTS').findOne({NickName:/七星/})
var lastAddTime=db.getCollection('FROMWECHATDATA').find({TOUSERNAME:p.UserName}).sort({createdAt:-1}).limit(1)[0].createdAt
printjson(p.NickName+'--'+p.UserName+'--'+ p.AddTime.getFullYear()+'年'+(p.AddTime.getMonth()+1)+'月'+p.AddTime.getDate()+'日 '+p.AddTime.getHours()+':'+p.AddTime.getMinutes()+':'+p.AddTime.getSeconds()+' 最新一条消息添加时间 '+lastAddTime.getFullYear()+'年'+(lastAddTime.getMonth()+1)+'月'+lastAddTime.getDate()+'日 '+lastAddTime.getHours()+':'+lastAddTime.getMinutes()+':'+lastAddTime.getSeconds());
db.getCollection('WeiXinNotify').find({AuthorizerAppid:p.AppId}).forEach(function(o){
printjson(o.AddTime.getFullYear()+'年'+(o.AddTime.getMonth()+1)+'月'+o.AddTime.getDate()+'日 '+o.AddTime.getHours()+':'+o.AddTime.getMinutes()+':'+o.AddTime.getSeconds()+'--'+o.InfoType );
});

DECLARE @accountName VARCHAR(200)=''
SELECT @accountName= [OriginalID] FROM [wechat].[dbo].[PublicAccounts] WHERE Name LIKE '%七星%'
IF @accountName=''
PRINT '没找到公众号'
ELSE
BEGIN
DECLARE @sqlStr NVARCHAR(max)='SELECT TOP 10 * FROM [wechat].[dbo].['+@accountName+'_Msg] ORDER BY ID DESC'
exec sp_executesql @sqlStr;
END

添加字段，设置值
db.getCollection('adlist').update({}, {$set: {"startdate":ISODate("2017-08-01T02:11:18.894Z"),"deadline":ISODate("2017-08-30T02:11:18.894Z")}}, false, true)

批量插入
db.getCollection('adlist').insert([])

db.getCollection('adlist').update({stationId:'BF8BF5B5-BE63-E411-A33D-AC853D9F52F0'},{$set:{guname: 'shuazhibo_room208',roomId: '208'}},false,true)
not in
db.getCollection('channelcloudspace').find({"$and": [{stationType:2},{channelName:{'$nin':['音乐之路']}}]})

批量更新
db.getCollection('channelcloudspace').find({$and:[{stationType:0},{roomid:{$gt:0}}]}).forEach(function(item){
db.getCollection('channelcloudspace').update({"_id":item._id},{"$set":{orgainizationGUName:'shuazhibo_room'+item.roomid}},false,true)
});

mongodb中查询某个字段不存在的文档
db.getCollection('transcriptionstask').find({system:/beijingtai/,content:{$exists:false}})
查询某个字段存在的文档
db.getCollection("privatesoundprogramfolder").find({channelid:{$exists:true}})
获取某台的频道列表只包含名称和guname
db.getCollection('channelcloudspace').find({organizationGuid:'23AFF488-7B48-4873-9A09-3186C35498DE'},{channelName:1,orgainizationGUName:1,_id:0})

db.getCollection('privatesoundprogramfolder').find({playOnSegmentName:/社区/})
db.getCollection('privatesoundprogramfolder').find({"playOnChannelGUName" : "江苏文艺广播"})

db.getCollection('privatesoundsingleprogram').find({folderGuid:"758377CB-FDB9-4D20-857A-101306625AEA"}).sort({playFirstDate:1})

db.getCollection('privatesoundsingleprogram').distinct('programOfBroadCasts.playOnChannelGUName')
db.getCollection('privatesoundsingleprogram').find({'programOfBroadCasts.startTime':'2018-01-25 16:00:00'},{title:1,_id:0}).sort({createDateTime:1,indexInFolder:1,partNumber:1})

//删除重复数据
db.privatesoundsingleprogram.aggregate([
{ $match : { aSoundFileName:/\/ICT\// } },
{
$group: { _id: {playDateTime: '$playDateTime',title: '$title',folderGuid:'$folderGuid'},count: {$sum: 1},dups: {$addToSet: '$_id'}}
},
{
$match: {count: {$gt: 1}}
}
]).forEach(function(doc){
printjson(doc.dups);//.shift();
//db.privatesoundsingleprogram.remove({_id: {$in: doc.dups}});
//db.getCollection('privatesoundsingleprogram').find({_id: {$in: doc.dups}})
})
//
db.privatesoundsingleprogram.aggregate([
{$match: {folderGuid:/^0-/}},
{
$group: { _id: {folderGuid: '$folderGuid',programGuid: '$programGuid'},count: {$sum: 1},dups: {$addToSet: '$_id'}}
},
{
$match: {count: {$gt: 1}}
}
]).forEach(function(doc){
doc.dups.shift();
db.privatesoundsingleprogram.remove({_id: {$in: doc.dups}});
}).sort({updatedAt:-1}).limit(100)

获取所有表 show collections

两个字段内容不等
{"$where": "this.aSoundFileName != this.bSoundFileName","bSoundFileName":{$exists:true}}

或
{$or: [{"streamNames.streamName" : "room428_pc"},{"streamName" : "room428_pc"}]}
批量更新
db.getCollection("mts_wrapped_playbacks").find({}).forEach(function(item){
item.isAvailable = NumberInt(1);
db.mts_wrapped_playbacks.save(item);

})

db.getCollection('privatesoundsingleprogram').update({"_id" : { $in : [ObjectId("618e4862fc17db007bd1355b")]}},{"$set":{isAvailable:NumberInt(0)}},false,true)
var i=0;
db.getCollection('privatesoundsingleprogram').find({ $and : [{"folderGuid" : "600ECB35-319C-4623-8A4F-3BDC91B57087"}, {"isAvailable" : 1}] }).sort({columnIndex: 1}).forEach(function(item){
i++;
//printjson(item.columnIndex+'--'+i+'-'+item.title);
db.getCollection('privatesoundsingleprogram').update({"_id":item._id},{"$set":{columnIndex:NumberInt(i)}},false,true)
});
db.getCollection('privatesoundprogramfolder').update({"folderGuid" : "600ECB35-319C-4623-8A4F-3BDC91B57087"},{"$set":{totalCout:NumberInt(i)}},false,false)