CODEWITHSUNDEEP
mongodbmongooseaggregation-frameworkaggregatelookup
guy
guy

Reputation: 111

mongoose recursive nesting

in my project a user can create products. each user have a reference to all of its products and each product have a reference to its user.

both the user and the product have a 'name' field.

i need to get all of the users products array, and in that array i want to have the product name and the user name that created it (and only those fields and no others).

for example:

Users: 

{ _id: 1, name: 'josh', productIds: [1,3]}
{ _id: 2, name: 'sheldon', productIds: [2]}

Products:

{ _id: 1, name: 'table', price: 45, userId: 1}
{ _id: 2, name: 'television', price: 25 userId: 2}
{ _id: 3, name: 'chair', price: 14 userId: 1}

i want to get the following result:

{ _id: 1, name: 'josh', 
    products: {
        { _id: 1, name: 'table', user: { _id: 1, name: 'josh' },
        { _id: 3, name: 'chair', user: { _id: 1, name: 'josh' },
    }
}
{ _id: 2, name: 'sheldon', 
    products: {
        { _id: 2, name: 'television', userId: { _id: 2, name: 'sheldon' }
    }
}

i tried the following query that didn't fill the inner userId and left it with only the id (no name):

User.aggregate([
  { 
    $lookup: 
      { 
        from: 'products',
        localField: 'productIds',
        foreignField: '_id',
        as: 'products' 
      }  
  }

i also tried the following, which did the same as the first query except it only retried the first product for each user:

User.aggregate([
  { 
    $lookup: 
      { 
        from: 'products',
        localField: 'productIds',
        foreignField: '_id',
        as: 'products' 
      }  
  },
  {
    $unwind: {
      path: "$products",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $lookup: {
      from: "user",
      localField: "products.userId",
      foreignField: "_id",
      as: "prodUsr",
    }
  },
  {
    $group: {
      _id : "$_id",
      products: { $push: "$products" },
      "doc": { "$first": "$$ROOT" }
    }
  },
  {
    "$replaceRoot": {
    "newRoot": "$doc"
    }
}

Product: 

const schema = new Schema(
  {
    name: {
      type: String,
      required: true
    },
    price: {
      type: Number,
      required: true
    },
    userId: {
      type: Schema.Types.ObjectId, 
      ref: 'User',
      required: true
    },
  }
);

module.exports = mongoose.model('Product', schema);

User:

const schema = new Schema(
  {
    name: {
      type: String,
      required: true,
      unique: true
    },
    productIds: [{
      type: Schema.Types.ObjectId, 
      ref: 'Product',
      require: false
    }],
  { timestamps: true }
);

module.exports = mongoose.model('User', schema);

any help will be highly appreciated

Upvotes: 1

Views: 554

Answers (1)

mickl
mickl

Reputation: 49945

It looks like a perfect scenario for $lookup with custom pipeline and another nested $lookup. The inner one allows you to handle product-> user relationship while the outer one handles user -> product one:

db.Users.aggregate([
    {
        $project: {
            productIds: 0
        }
    },
    {
        $lookup: {
            from: "Products",
            let: { user_id: "$_id" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: [ "$userId", "$$user_id" ]
                        }
                    }
                },
                {
                    $lookup: {
                        from: "Users",
                        localField: "userId",
                        foreignField: "_id",
                        as: "user"
                    }
                },
                {
                    $unwind: "$user"
                },
                {
                    $project: {
                        "user.productIds": 0,
                        "price": 0,
                        "userId": 0
                    }
                }
            ],
            as: "products"
        }
    }
])

Mongo Playground

Upvotes: 2

Related Questions