CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-query
mchl0208
mchl0208

Reputation: 69

Find an document and it's referenced documents

I'm getting throw trying to populate related fields using aggregation pipeline in mongodb, plain relations works as well (I mean oid reference to oid in other collection) but what happens when you have an object array that one of its properties reference a sub document. If I wasn't clear, here a little representation.

Suppose I have the following schema:

Profile {
    _id: {
        type: mongoose.Schema.Types.ObjectId
    },
    Gender: {
        type: mongoose.Schema.Types.ObjectId,
        ref: "Gender"
    },
    PhoneNo: [
        Value: {
            type: String
        },
        PhoneType: {
            type: mongoose.Schema.Types.ObjectId,
            ref: "PhoneType"
        }
    ]
}

PhoneType {
    _id: {
        type: mongoose.Schema.Types.ObjectId
    },
    Name: {
        type: String
    }
}

Gender {
    _id: {
        type: mongoose.Schema.Types.ObjectId
    },
    Name: {
        type: String
    }
}

So, I would like to get results like:

    {
        _id: $oid,
        Gender: {Value:"Masculine"},
        PhoneNo: {
            Value: "000000",
            PhoneType: {
                _id: $oid
                Name: "Cell"
            }
        }
    },
    {
        _id: $oid,
        Gender: {Value:"Feminine"},
        PhoneNo: {
            Value: "999999",
            PhoneType: {
                _id: $oid
                Name: "Work"
            }
        }
    }

Lookup in Gender works good, but when I try to lookup PhoneNo then I lost the value property.

What I'm getting is:

Pay attention to the field/property Value, is lost.

    {
       _id: $oid,
       Gender: {Value:"Masculine"},
       PhoneNo: [{
         PhoneType: {
            _id: $oid
            Name: "Cell"
         }
       }]
    },

    {
       _id: $oid,
       Gender: {Value:"Feminine"},
       PhoneNo: [{
         PhoneType: {
            _id: $oid
            Name: "Work"
         }
       }]
    }

Here is the code that I used: 

{
  from: 'PhoneType',
  'let': {"ids":"$PhoneNo.PhoneType"},
  "pipeline": [
    { "$match": { "$expr": { "$in": ["$_id", "$$ids"] } } },
  ],
  as: "PhoneNo"
}

How can I do that? :S

Upvotes: 0

Views: 76

Answers (1)

sushant mehta
sushant mehta

Reputation: 1274

So this is a walkthrough for your problem, we also include Values in lookup let declaration it'll be an array because it's stored in an array and in project stage we'll fetch indexOf Values array where $$ROOT 's id and id match

https://mongoplayground.net/p/UUXus3N3ncw

Input collections:

"user": [
    {
      _id: {
        type: "1"
      },
      Gender: 12,
      PhoneNo: [
        {
          Value: "name",
          PhoneType: 21
        },
        {
          Value: "name2",
          PhoneType: 212
        }
      ]
    }
  ],
  "gender": [
    {
      "_id": 12,
      "Name": "Male"
    }
  ],
  "PhoneType": [
    {
      "_id": 21,
      name: "Sus",
      val: "750"
    },
    {
      "_id": 212,
      name: "Suss",
      val: "7500"
    }
  ]

Aggregate Pipeline:

user.aggregate([
  {
    $lookup: {
      from: "PhoneType",
      "let": {
        "ids": "$PhoneNo.PhoneType",
        "val": "$PhoneNo.Value",

      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$ids"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            Value: {
              $arrayElemAt: [
                "$$val",
                {
                  $indexOfArray: [
                    "$$ids",
                    "$$ROOT._id"
                  ]
                }
              ]
            },
            PhoneType: "$$ROOT"
          }
        }
      ],
      as: "PhoneNo"
    }
  }
])

Output Result:

[
  {
    "Gender": 12,
    "PhoneNo": [
      {
        "PhoneType": {
          "_id": 21,
          "name": "Sus",
          "val": "750"
        },
        "Value": "name"
      },
      {
        "PhoneType": {
          "_id": 212,
          "name": "Suss",
          "val": "7500"
        },
        "Value": "name2"
      }
    ],
    "_id": {
      "type": "1"
    }
  }
]

Upvotes: 1

Related Questions