mongodb可以实现这样操作数据吗？

mongodb可以实现这样操作数据吗？请各位大佬帮忙说一说：

比如有这样一张表collection-1：

{"_id" : ObjectId("fajflfkajfa840faff"),"city":"北京","region":"东城"}
{"_id" : ObjectId("fajflfkajfa9r8q9ga"),"city":"北京","region":"西城"}
{"_id" : ObjectId("fajflfkaj948gfangi"),"city":"北京","region":"朝阳"}

{"_id" : ObjectId("fajflfkajffdajhfa7"),"city":"上海","region":"黄浦"}
{"_id" : ObjectId("fajflfkajfflja983f"),"city":"上海","region":"卢湾"}
{"_id" : ObjectId("fajflfkajfa8378fey"),"city":"上海","region":"徐汇"}

{"_id" : ObjectId("fajflfkajfa48ua0ja"),"city":"天津","region":"河东"}
{"_id" : ObjectId("fajflfkajff8a7yuhf"),"city":"天津","region":"河西"}
{"_id" : ObjectId("fajflfkajfalaofa04"),"city":"天津","region":"和平"}

说明：实际数据是乱序的，没有像上面那样按`city`排序。

问题：
1、能否给每个相同city的数据添加一个city_id编号，完成后像这样collection-2：

{"_id" : ObjectId("fajflfkajfa840faff"),"city":"北京","region":"东城","city_id":"1"}
{"_id" : ObjectId("fajflfkajfa9r8q9ga"),"city":"北京","region":"西城","city_id":"1"}
{"_id" : ObjectId("fajflfkaj948gfangi"),"city":"北京","region":"朝阳","city_id":"1"}

{"_id" : ObjectId("fajflfkajffdajhfa7"),"city":"上海","region":"黄浦","city_id":"2"}
{"_id" : ObjectId("fajflfkajfflja983f"),"city":"上海","region":"卢湾","city_id":"2"}
{"_id" : ObjectId("fajflfkajfa8378fey"),"city":"上海","region":"徐汇","city_id":"2"}

{"_id" : ObjectId("fajflfkajfa48ua0ja"),"city":"天津","region":"河东","city_id":"3"}
{"_id" : ObjectId("fajflfkajff8a7yuhf"),"city":"天津","region":"河西","city_id":"3"}
{"_id" : ObjectId("fajflfkajfalaofa04"),"city":"天津","region":"和平","city_id":"3"}

说明：城市不需要用指定的`city_id`，按城市排序后，`city_id`从1自然增长就可以。

2、能否把相同城市的第一条取出来，另存为一张表collection-3，像这样：

{"_id" : ObjectId("fajflfkajfa840faff"),"city":"北京","region":"东城","city_id":"1"}
{"_id" : ObjectId("fajflfkajffdajhfa7"),"city":"上海","region":"黄浦","city_id":"2"}
{"_id" : ObjectId("fajflfkajfa48ua0ja"),"city":"天津","region":"河东","city_id":"3"}

说明：实际可能不是上面这3条数据，取出`collection-2`表的相同城市的第1条就可以。

mongodb

阅读 3.1k

2 个回答

✓ 已被采纳

对于第一个需求我不是十分理解，照现在的说法，每个城市对应一个唯一的city_id，但是谁在前谁在后是按city的顺序排吗？这样的话没有现成的方法，你要自己找到有多少城市，排序好后自己循环分配city_id，和需求2放一起实现的话就可以有：

db['collection-1'].aggregate([
    {$group: {_id: "$city", doc: {$first: "$$ROOT"}}},    // 按city分组获取第一条
    {$replaceRoot: {newRoot: "$doc"}},    // 原来位于doc中的文档取代根文档
    {$out: "collection-2"}    // 输出到collection-2
]);

$first, $$ROOT的用法参考相关文档。这时候collection-2里面就有你需要的所有独立的city了，然后为它们分配唯一的city_id并更新到collection-1中:

let i = 1;
db['collection-2'].find().collation({locale: 'zh'}).sort({city: 1}).forEach(doc => {
    db['collection-1'].updateMany({city: doc.city}, {$set: {city_id: i++}});
});

需要注意的是这里指定了.collation({locale: 'zh'})，才能按拼音排序，如果不指定的话是按照每个汉字的编码的二进制顺序排列，对我们并没有什么意义（可以自己试一下看是什么顺序）。这是MongoDB 3.4才支持的新特性。
另外如果数据量大请自己创建合适的索引再进行操作。