2020-03-05 how mongodb uses aggregation between different types of keys

Time:2020-3-18

Problem: mongodb join on “ID field from string to objectid
There are now two sets of data:

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

2.Role

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

I want to join these collection based on the userId (in role collection) – _id ( in user collection).
I want to splice the two sets based on userid and “Id”.

I tried the below query:
I tried the following query

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

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
When userid is of type string, there is no result.

foreignField: '_id'.valueOf()

and

foreignField: '_id'.toString()

But no luck to match/join based on a ObjectId-string fields.
It also failed.
Any help will be appreciated. (old fellow can help me?)

The previous tickets were fixed in MongoDB 4.0. You can now achieve this with the folowing query:
(it’s necessary, brother. You can do this!)

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

Result: (result)


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

try it online: mongoplayground.net/p/JoLPVIb1OLS
There is more information here.

Or you can try the following two queries:
You can use $toObjectId aggregation from mongodb 4.0 which converts String id to ObjectId

db.role.aggregate([
  { "$lookup": {
    "from": "user",
    "let": { "userId": "$_id" },
    "pipeline": [
      { "$addFields": { "userId": { "$toObjectId": "$userId" }}},
      { "$match": { "$expr": { "$eq": [ "$userId", "$$userId" ] } } }
    ],
    "as": "output"
  }}
])

Or you can use $toString aggregation from mongodb 4.0 which converts ObjectId to String

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

View the original discussion
It may be blocked for some reasons, you know!

Recommended Today

[Mongo] mongodb replica set

………………………………………………………………………………….Environment: centos7Ip: Master node: 192.168.225.128Slave node 192.168.225.129Slave node & arbitration node: 192.168.225.130Mongo version: 3.4…………………………………………………………………………………. 1、 Principle Mongodb replica set is a master-slave cluster with automatic fault recovery function, which is composed of one primary node and one or more secondary nodes. Mmm architecture similar to MySQL. Data synchronization process in replica set: the primary node writes […]