javascript通过从另一个集合中查找来更新mongodb记录多个字段

Question

我在 mongoDB 中有几十万个文档要更新。以下是集合 Users 中现有文档的示例:

{ 
    "_id" : "549120bcf5115900124fb6e1", 
    "user" : "Tom", 
    "country" : "United Kingdom", 
    "province" : "North Yorkshire", 
    "city" : "York", 
    "organization" : "" 
},
{ 
    "_id" : "143184fbf5482260184ac6e2", 
    "user" : "Jack", 
    "country" : "Not Listed", 
    "province" : "", 
    "city" : "", 
    "organization" : "United Nations"
},
{ 
    "_id" : "1234567890123456748979", 
    "user" : "Sarah", 
    "country" : "Not Listed", 
    "province" : "", 
    "city" : "", 
    "organization" : "" 
},
{ 
    "_id" : "98765432411654987654", 
    "user" : "Mat"
}

每个文档都有可能在这些字段中有值:

• 一个国家，一个省，一个城市
• 或一个国家和州

这是来自另一个国家/地区集合的示例:

{ 
    "_id" : "123456789", 
    "key" : "Not Listed",
    "uuid" : "ca55b53a-ef5b-43ed-90ed-b857f45ddb6d",    
    "organization" : [
        {
            "key" : "United Nations", 
            "uuid" : "1c4ae4c6-00c5-405d-98fa-ca7cc9edc72a"
        }, 
        {
            "key" : "FIFA", 
            "uuid" : "11cfe606-821f-40fb-b1d0-bb7f9abb21dc"
        }
    ], 
    "province" : [], 
},
{ 
    "_id" : "1123465498742", 
    "key" : "United Kingdom", 
    "uuid" : "d756e167-25ec-4aa9-b231-4dbf6d4bfce4",
    "organization" : [], 
    "province" : [
        {
            "key" : "North Yorkshire", 
            "uuid" : "73d07c77-eba4-4dfa-9ada-e0ba8d8a2d55",            
            "city" : [
                {
                    "key" : "York", 
                    "uuid" : "80fd18a6-c4eb-4fb9-b591-6cca62319ba7"
                }, 
                {
                    "key" : "Middlesbrough", 
                    "uuid" : "26a277c4-8640-4959-a64a-00f3727975f4"
                }
            ], 
        },
        {
            "key" : "Oxfordshire", 
            "uuid" : "f7b5a570-df42-4520-ba3a-8bdcdd00e7d4",            
            "city" : [
                {
                    "key" : "Oxford", 
                    "uuid" : "b931865c-a363-4958-b7e7-5503fe674eb0"
                }, 
                {
                    "key" : "Banbury", 
                    "uuid" : "b8d4c63a-75a9-4c3c-a4cd-d315f06a92e0"
                }
            ], 
        }
    ]
}

想法是从Users集合中的文档中查找country/organization/province/city字段值，并根据的uuid值更新它们>国家集合。

所以结果看起来像这样:

{ 
    "_id" : "549120bcf5115900124fb6e1", 
    "user" : "Tom", 
    "country" : "d756e167-25ec-4aa9-b231-4dbf6d4bfce4", // uuid of United Kingdom
    "province" : "73d07c77-eba4-4dfa-9ada-e0ba8d8a2d55", // uuid of North Yorkshire
    "city" : "80fd18a6-c4eb-4fb9-b591-6cca62319ba7", // uuid of York
    "state" : "" 
},
{ 
    "_id" : "143184fbf5482260184ac6e2", 
    "user" : "Jack", 
    "country" : "ca55b53a-ef5b-43ed-90ed-b857f45ddb6d", // uuid of Not Listed
    "province" : "", 
    "city" : "", 
    "state" : "1c4ae4c6-00c5-405d-98fa-ca7cc9edc72a" // uuid of United Nations 
},
{ 
    "_id" : "1234567890123456748979", 
    "user" : "Sarah", 
    "country" : "ca55b53a-ef5b-43ed-90ed-b857f45ddb6d", // uuid of Not Listed
    "province" : "", 
    "city" : "", 
    "state" : "" 
},
{ 
    "_id" : "98765432411654987654", 
    "user" : "Mat" 
}

字段的依赖如下:

国家>省>市

或者:

国家>组织

可能存在父字段，但其子字段不存在或为空。

如何使用 mongo 脚本规则更新这些多维数组？

这是我的尝试，但是有很多 for 循环，并且不确定如何执行 mongodb 查找/更新/保存部分。有人可以帮助实现它吗？

var usrCountry, uuidcountry, usrProvince, uuidprovince, usrOrg, uuidorg, usrCity, uuidcity;

for (var i = 0; i < users.length; i++) {
   usrCountry = users[i].country;
   usrProvince = users[i].province;
   usrOrg = users[i].organization;
   usrCity = users[i].city;
   for (var j = 0; j < countries.length; j++) {
     if (countries[j].key === usrCountry) {
       uuidcountry = countries[j].uuid;
       console.log('uuidcountry: ', uuidcountry)
       if (countries[j].province.length){
         for (var k = 0; k < countries[j].province.length; k++) {
           if (countries[j].province[k].key === usrProvince){
            uuidprovince = countries[j].province[k].uuid;
            console.log('uuidprovince', uuidprovince)
            for (var l = 0; l < countries[j].province[k].city.length; l++) {
             if (countries[j].province[k].city[l].key === usrCity){
               uuidcity = countries[j].province[k].city[l].uuid
               console.log('uuidcity: ', uuidcity)
             }
            }  
           }

         }
       }
     }
   }
}

Answer 1

您可以尝试使用聚合管道执行此操作，并使用该信息进行更新

db.u.aggregate(
    [
        {
            $lookup: {
                from : "c",
                localField : "country",
                foreignField : "key",
                as : "countryInfo"
            }
        },
        {
            $project: {
                "_id" : 1,
                "user" : 1,
                "province" : 1,
                "country" : 1,
                "city" : 1, 
                "organization" : 1,
                "country_uuid" : {$arrayElemAt : ["$countryInfo.uuid",0]},
                "province_uuid" : { $arrayElemAt : [{ $map : { input : { $filter : {  input : {$arrayElemAt : ["$countryInfo.province" ,0 ]} , as : "pro", cond : { $eq : [ "$$pro.key", "$province" ]  } } } , as : "pr", in : "$$pr.uuid" } }, 0 ] },
                "city_uuid" : {$arrayElemAt : [{$map : { input : { $arrayElemAt : [ {$filter : { input : { $map : { input : { $arrayElemAt : ["$countryInfo.province.city" ,0 ] }, as : "ct", in : { $filter : { input : "$$ct" , as : "ctyy", cond : { $eq : ["$$ctyy.key", "$city"] } } } } }, as : "o", cond : {$ne : [ {$size : "$$o"} , 0  ] } } } , 0]}, as : "o", in :"$$o.uuid"}}, 0]}
            }
        }
    ]
)

结果

> db.u.aggregate( [ { $lookup: { from : "c", localField : "country", foreignField : "key", as : "countryInfo" } }, { $project: { "_id" : 1, "user" : 1, "province" : 1, "country" : 1, "city" : 1,  "organization" : 1, "country_uuid" : {$arrayElemAt : ["$countryInfo.uuid",0]}, "province_uuid" : { $arrayElemAt : [{ $map : { input : { $filter : {  input : {$arrayElemAt : ["$countryInfo.province" ,0 ]} , as : "pro", cond : { $eq : [ "$$pro.key", "$province" ]  } } } , as : "pr", in : "$$pr.uuid" } }, 0 ] }, "city_uuid" : {$arrayElemAt : [{$map : { input : { $arrayElemAt : [ {$filter : { input : { $map : { input : { $arrayElemAt : ["$countryInfo.province.city" ,0 ] }, as : "ct", in : { $filter : { input : "$$ct" , as : "ctyy", cond : { $eq : ["$$ctyy.key", "$city"] } } } } }, as : "o", cond : {$ne : [ {$size : "$$o"} , 0  ] } } } , 0]}, as : "o", in :"$$o.uuid"}}, 0]} } } ] ).pretty()
{
    "_id" : "549120bcf5115900124fb6e1",
    "user" : "Tom",
    "country" : "United Kingdom",
    "province" : "North Yorkshire",
    "city" : "York",
    "organization" : "",
    "country_uuid" : "d756e167-25ec-4aa9-b231-4dbf6d4bfce4",
    "province_uuid" : "73d07c77-eba4-4dfa-9ada-e0ba8d8a2d55",
    "city_uuid" : "80fd18a6-c4eb-4fb9-b591-6cca62319ba7"
}