How to trim a value by a particular length and then apply lookUp in mongoDB

Question

The main issue i'm facing is doing multi document joins. For example, when I try and run a query on orders by customer I can't find an easy way to join orders to customers on a customer id because in the orders object the customer id has _user$ appended to the beginning of the user ID and I don't know how to truncate that in an aggregation pipeline.

eg; In the order object, the customer is defined as _p_customer:"_User$8qXjk40eOd" But in the user table the _id is just 8qXjk40eOd and therfore the default lookup function in charts cannot match the two.

Note: I'm using parse server and it stores pointers in the above mentioned way.

This is how the data is being stored in the mongoDB order collection.

customer field in the above image is the pointer to the _User collection

This is how the data is stored in the mongoDB _User collection.

My requirement is to write mongo query that find all users and their related orders count. I'm not able to do that because I don't how to remove _User$ from the order row and join it the _User via _id.

Answer 1

before lookup in aggregate substr the field you want to use in $lookup

db.collection.aggregate([
{
   $addFields :{
       nUser : { $substr: [ "$_p_customer", 6, -1 ] } // _User$ has 6 characters
   }
},
{
   $lookup :{
       from:"usersCollection",
       localField:"nUser",
       foreignField : "_id",
       as : "UserObject"
   }
}
])