MongoDB日常工作记录

一、模糊查询

文档如下：

{
    "_id": {
        "$oid": "6139dd9cff909b83c9633afc"
    },
    "ackid": "315c56d540c14acfb7f65fd8fd5faafa",
    "id": "1631182236919074660",
    "sid": "159190488252417_156192831766529",
    "sender": "159190488252417",
    "sendername": "Alan Chen",
    "mid": "156192831766529",
    "gid": "",
    "type": 0,
    "body": "{\"msg\":{\"isAddBlacklist\":false,\"isInvitation\":0,\"size\":4,\"totalCount\":0,\"type\":2,\"url\":\"https://www.jianshu.com/\"},\"timestamp\":1631182236843,\"type\":2}",
    "status": 0,
    "create_time": {
        "$numberLong": "1631182236920"
    },
    "tags": [
        "runoob"
    ]
}
1.1 文档字段模糊查询
db.posts.find({ sendername: { $regex: "Alan" } })

以上查询也可以写为

db.posts.find({ sendername: /Alan/ })
1.2 模糊查询不区分大小写
db.posts.find({ sendername: { $regex: "alan", $options: "$i" } })
1.3 子文档字段模糊查询

目标：查询body属性里type为2的文档

db.posts.find({ body: { $regex: '\"type\":2' } })

{ 'questions.answers': { $regex: 'qimiaobb' } }
1.4 模糊查询数组元素
db.posts.find({ tags: { $regex: "run" } })

二、判断字段是否存在

{ pretty_content_type: { "$exists": true } }

三、数组size判断

{ topics: { $size: 2 } } { topics: { $size: 2 } }

四、建索引

文档内容

{
    "address": {
        "city": "Los Angeles",
        "state": "California",
        "pincode": "123"
    },
    "tags": [
        "music",
        "cricket",
        "blogs"
    ],
    "name": "Tom Benzamin",
    "gender": "1"
}
4.1 单个字段建索引
db.users.createIndex({ name: 1 })

db.users.createIndex({ gender: 1 })
4.2 创建联合索引
db.users.createIndex({ gender: 1, name: 1 })
4.3 数组字段建索引
db.users.createIndex({ "tags": 1 })
4.4 子文档字段建索引
db.users.ensureIndex({ "address.city": 1, "address.state": 1, "address.pincode": 1 })

五、子文档查询

{ "calendar.calendar_type": 2 }

六、字符串替换

1、更新普通字段

db.dynamic.updateMany(
    { member_icon: { "$exists": true } },
    [{
        $set: {
            member_icon: {
                $replaceOne: { input: "$member_icon", find: "oss-cn-shenzhen.aliyuncs.com", replacement: "oss.alanchen.com" }
            }
        }
    }]
)

2、更新数组

db.dynamic.updateMany(
    { "attachments.url": { "$exists": true } },
    [{
        $set: {
            "attachments.url": {
                $map: {
                    input: "$attachments.url",
                    in: {
                        $replaceOne: {
                            input: "$$this",
                            find: "oss-cn-shenzhen.aliyuncs.com",
                            replacement: "oss.alanchen.com"
                        }
                    }
                }
            }
        }
    }]
)

七、数据修复

db.dynamic.find({ "calendar.last_like": { "$exists": true } })
    .sort({
        _id: -1
    }).forEach(function(item) {

        var last_like = [];
        item.calendar.last_like.forEach(function(e) {
            if (e != null) {
                last_like.push(e);
            }
        });

        if (last_like.length != item.calendar.last_like.length) {
            db.getCollection("dynamic").update({ _id: item._id }, { $set: { "calendar.last_like": last_like } });
        }
    })

八、批量修改

8.1
db.publisher.update(
    { member_id: { $ne: NumberLong("274") } },
    { $set: { member_id: NumberLong("274") } },
    { multi: true }
)
8.2
db.publisher.update(
    { member_id: 274 },
    { $set: { member_id: 167993292161025 } },
    { multi: true }
)
8.3
db.comment.updateMany({ deleted: false }, { $set: { inspectStatus: 1 } })

九、查询重复数据

db.getCollection('dynamic').aggregate([
    { $match: { type: "CALENDAR" } },
    { $match: { createTime: { $gt: ISODate("2022-10-20T00:10:40.000Z") } } },
    { $group: { _id: { 'calendar': '$calendar.title', 'pub_location': '$pub_location' }, count: { $sum: 1 } } },
    { $match: { count: { $gt: 1 } } }

]).forEach(function(item) {
    print(item);
})

十、查找并删除重复数据

10.1
db.getCollection('dynamic_draft').aggregate([
    { $group: { _id: { 'origin': '$origin', 'url': '$url', 'status': '$status', 'block_status': '$block_status' }, count: { $sum: 1 } } },
    { $match: { count: { $gt: 1 } } }

]).forEach(function(o) {
    db.dynamic_draft.find({ "origin": o._id.origin, "url": o._id.url, "status": o._id.status, "block_status": o._id.block_status }).skip(1).forEach(function(d) {
        db.dynamic_draft.deleteOne({ _id: d._id })
    })
})
10.2
db.calendar_limit_settings.remove({
    setting_type: 0,
    member_mer_type: {
        $in: ["PERSONAL_FAMOUS", "PERSONAL"]
    }
})

十一、分组统计记录数

db.getCollection('maoyan_fetch_ticket_way').aggregate([
    {
        "$group": {
            "_id": {
                "ticketUnitId": "$ticketUnitId"
            },
            "recordCount": { "$sum": 1 }
        }
    }
])

十二、将一个表的数据插入另外一个表

//同步想去到收藏
db.calendar_like_log.find({}).forEach(function(item) {
    db.goods_favorite_log.insert({
        _id: item._id,
        member_id: item.member_id,
        member: item.member,
        goods_type: "ACTIVITY",
        goods_id: item.dynamic_id,
        deleted: item.deleted,
        createTime: item.createTime
    });
})

十三、数据处理

13.1
db.activity.find({
    activity_id: {
        $exists: false
    }
}).forEach(function(item) {
    db.getCollection("activity").update({
        _id: item._id
    },
        { $set: { "activity_id": item.dynamic_id } });
})
13.2
db.activity.find({
    activity_id: {
        $exists: false
    }
}).forEach(function(item) {
    db.getCollection("activity").update({
        _id: item._id
    },
        { $set: { "activity_id": item.dynamic_id } });
})
13.3
db.activity.find({
    activity_id: {
        $exists: false
    }
}).forEach(function(item) {
    db.getCollection("activity").update({
        _id: item._id
    },
        { $set: { "activity_id": item.dynamic_id } });
})
13.4
//处理猫眼活动价格没有
db.goods.find({
    goods_type: "ACTIVITY",
    "activity_attribute.isMaoYan": true,
    "activity_attribute.performance.performanceId": {
        $exists: true
    }
}).forEach(function(item) {
    var performance = db.maoyan_performances.findOne({ performanceId: item.activity_attribute.performance.performanceId })
    db.getCollection("goods").update(
        { _id: item._id },
        { $set: { "sales_attribute.paymentPrice": performance.minPrice, "sales_attribute.stock": 200 } })
})
13.5
db.dynamic.find({
    "topics._id": "63b513a86af1ae0df6b07608",
    type: {
        $ne: "CALENDAR"
    },
    isQCYXWorks: {
        $exists: false
    }
})
    .sort({
        _id: -1
    }).forEach(function(item) {

        if (item.content.length >= 50 && ((item.type == 'IMAGE' && item.attachments.length >= 9) || item.type == 'VIDEO')) {
            db.getCollection("dynamic").update({
                _id: item._id
            },
                {
                    $set: {
                        "isQCYXWorks": true, "qcyxInteract.like_nums": (item.like_nums + item.share_nums), "contentSpecialTag": {
                            "code": "QINGCUN_YINGXIANG",
                            "name": "青春影像",
                            "color": "FF5F36"

                        }
                    }
                });

            print(item);
        }
    })
13.6
db.dynamic.find({
        type: "CALENDAR",
        deleted: false,
        shelf_status: "PUT"
})
    .sort({
        _id: -1
}).forEach(function(item){
        var lastLike=[];
        print(item._id.valueOf());
      var count =  db.calendar_like_log.find({dynamic_id: item._id.valueOf(), deleted: false
    }).count();

      print(item.like_nums == count)
      if(count != item.like_nums) {
          if(count>0 ) {
                var list =  db.calendar_like_log.find({dynamic_id: item._id.valueOf(), deleted: false
            }).sort({
                    _id: -1
            }).limit(6).forEach(function(l){
                    lastLike.push(l.member)
            });

          db.getCollection("dynamic").update({
            _id: item._id
            },
            {$set: {
                    "like_nums": count,
                    "calendar.last_like":lastLike
                }
            });
        }   else {
            db.getCollection("dynamic").update({
          _id: item._id
            },
            {$set: {
                    "like_nums": count
                }
            });
        }
    }
})
13.7
db.third_part_inspect.find({}).forEach(function(item) {
    db.inspect_log.update({ _id: ObjectId(item.inspect_log_id) }, { $push: { third_part_inspects: item } });
    print(item.inspect_log_id)

})
13.8
db.calendar_like_log.find({
    deleted: true
})
    .sort({
        _id: -1
    }).forEach(function(item) {
        var lastLike = [];
        var count = db.calendar_like_log.find({ dynamic_id: item.dynamic_id, deleted: false }).count();
        if (count > 0) {
            var list = db.calendar_like_log.find({ dynamic_id: item.dynamic_id, deleted: false }).sort({
                _id: -1
            }).limit(6).forEach(function(l) {
                lastLike.push(l.member)
            });
            print(lastLike);

            db.getCollection("dynamic").update({
                _id: ObjectId(item.dynamic_id)
            },
                { $set: { "like_nums": count, "calendar.last_like": lastLike } });
            print(item.dynamic_id)

        } else {
            db.getCollection("dynamic").update({
                _id: ObjectId(item.dynamic_id)
            },
                { $set: { "like_nums": count } });

        }

    })
13.9
db.topic.aggregate([
    {
        $group: { _id: { topic: '$topic' }, count: { $sum: 1 }, dups: { $addToSet: '$_id' } }
    },
    {
        $match: { count: { $gt: 1 } }
    }
]).forEach(function(doc) {
    doc.dups.shift();
    db.topic.remove({ _id: { $in: doc.dups } });
})
13.10
db.dynamic_like_log.aggregate([
    {
        $group: { _id: { dynamic_id: '$dynamic_id', member_id: '$member_id' }, count: { $sum: 1 }, dups: { $addToSet: '$_id' } }
    },
    {
        $match: { count: { $gt: 1 } }
    }
]).forEach(function(doc) {
    doc.dups.shift();
    db.dynamic_like_log.remove({ _id: { $in: doc.dups } });
})
13.11
db.dynamic.find({
    "calendar.calendar_multi_bu_type": {
        $exists: true,
        $ne: null
    }
})
    .sort({
        _id: -1
    }).forEach(function(item) {
        if (item.calendar.calendar_multi_bu_type[0] == item.calendar.calendar_multi_bu_type[1]) {
            db.getCollection("dynamic").update({ _id: item._id }, { $set: { "calendar.calendar_multi_bu_type": [item.calendar.calendar_multi_bu_type[0]] } });
        }
    })
13.12
db.dynamic.find({
    type: "CALENDAR",
    "calendar.calendar_bu_type": "艺术"
})
    .sort({
        _id: -1
    }).forEach(function(item) {
        db.getCollection("dynamic").update({ _id: item._id }, { $push: { "calendar.calendar_multi_bu_type": "艺术" } });
        db.getCollection("dynamic").update({ _id: item._id }, { $push: { "calendar.calendar_multi_bu_type": "展览" } });
    })

十四、查询条件 字符串长度

如果要查询符合特定字符串长度的数据，可以使用 expr 运算符和strLenCP 函数结合使用。例如，要查询 collection 中 ip 字段长度大于20的所有文档，可以使用以下查询条件：

{
  $expr: {
    $gt: [{ $strLenCP: "$ip" }, 20]
  }
}

$eq   等于
$gt   大于

IP地址长度大于20，省份不是云南省的数据

{
  $expr: {
    $gt: [{ $strLenCP: "$ip" }, 20]
  },provinceName: { $ne: '云南省' }
}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容