Mongodb在_id字段上从字符串连接到ObjectId [英] Mongodb Join on _id field from String to ObjectId

问题描述

我有两个收藏夹

1. 用户

1. User

         {
             "_id" : ObjectId("584aac38686860d502929b8b"),
             "name" : "John"
         }

角色

Role

 {
     "_id" : ObjectId("584aaca6686860d502929b8d"),
     "role" : "Admin",
     "userId" : "584aac38686860d502929b8b"  
 }

我要基于 userId (在角色集合中)- _id (在 user 收藏).

I want to join these collection based on the userId (in role collection) - _id ( in user collection).

我尝试了以下查询:

db.role.aggregate(
{
   $lookup:
   {
       from: 'user',
       localField: 'userId',
       foreignField: '_id',
       as: 'output'
   }
}
);

只要我将userId存储为ObjectId，这就会给我带来预期的结果.当我的userId是字符串时，没有结果. 附言:我尝试过

This gives me expected results as long as i store userId as a ObjectId. When my userId is a string there are no results. Ps: I tried

foreignField:'_id'.valueOf()

foreignField: '_id'.valueOf()

和

foreignField:'_id'.toString()

foreignField: '_id'.toString()

.但是根据ObjectId字符串字段进行匹配/联接没有运气.

. But no luck to match/join based on a ObjectId-string fields.

任何帮助将不胜感激.

推荐答案

从MongoDB 3.4开始，这是不可能的.已请求此功能，但尚未实现.这是相应的票证:

This is not possible as of MongoDB 3.4. This feature has already been requested, but hasn't been implemented yet. Here are the corresponding tickets:

• SERVER-22781:允许在ObjectId(_id.str)和 字符串
• SERVER-24947:需要布尔值的类型转换机制， ISODates，ObjectID

• SERVER-22781: Allow $lookup between ObjectId (_id.str) and string
• SERVER-24947: Need a type conversion mechanism for booleans, ISODates, ObjectID

现在，您必须将userId存储为ObjectId

For now you'll have to store userId as ObjectId

编辑

以前的票已在MongoDB 4.0中修复.现在，您可以使用以下查询来实现此目的:

The previous tickets were fixed in MongoDB 4.0. You can now achieve this with the folowing query:

db.user.aggregate([
  {
    "$project": {
      "_id": {
        "$toString": "$_id"
      }
    }
  },
  {
    "$lookup": {
      "from": "role",
      "localField": "_id",
      "foreignField": "userId",
      "as": "role"
    }
  }
])

结果:

[
  {
    "_id": "584aac38686860d502929b8b",
    "role": [
      {
        "_id": ObjectId("584aaca6686860d502929b8d"),
        "role": "Admin",
        "userId": "584aac38686860d502929b8b"
      }
    ]
  }
]

在线试用: mongoplayground.net/p/JoLPVIb1OLS

这篇关于Mongodb在_id字段上从字符串连接到ObjectId的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！