Count number of elements to get from lookup query for nested referenced array in mongodb

Question

I have the following categories collection

{
"_id" : ObjectId("5e1d8c3ed06661d2b06a7c46"),
    "status" : "active",
    "noOfLevels" : 3,
    "levels" : [ 
        ObjectId("5e1db2237a265cfe50dfdb18"), 
        ObjectId("5e1db22b7a265cfe50dfdb19"), 
        ObjectId("5e1db23b7a265cfe50dfdb1a")
    ],
    "name" : "Fuel",
},
{
 "_id" : ObjectId("5e1d8c3ed06661d2b06a7c46"),
    "status" : "active",
    "noOfLevels" : 3,
    "levels" : [ 
        ObjectId("5e1db2237a265cfe50dfdb18"), 
        ObjectId("5e1db22b7a265cfe50dfdb19"), 
        ObjectId("5e1db23b7a265cfe50dfdb1a")
    ],
    "name" : "Feeds",
}

In the above collection levels _id has been stored as a reference to populate later.

Level collection has this items:-

{
 levelName: { type: String, required: true },
  levelDesc: { type: String },
  levelNo: { type: Number, required: true },
  categoryId: { type: Mongoose.Schema.Types.ObjectId, ref: 'trainingCategories', required: true },
  videoId: [{ type: Mongoose.Schema.Types.ObjectId, ref: 'trainingVideoMaster' }],
},
{
 levelName: { type: String, required: true },
  levelDesc: { type: String },
  levelNo: { type: Number, required: true },
  categoryId: { type: Mongoose.Schema.Types.ObjectId, ref: 'trainingCategories', required: true },
  videoId: [{ type: Mongoose.Schema.Types.ObjectId, ref: 'trainingVideoMaster' }],
}

I am using aggregation and lookup to find items in categories collection, in my final result I need counts for videoIds which is present on each level of the particular categories.

Need final result like this:-

 {
                        name: 'How to save fuel',
                        catNo: 1,
                        noOfLevels: 3,
                        totalVideos: 4
    },
    {
                        name: 'Feeds',
                        catNo: 2,
                        noOfLevels: 3,
                        totalVideos: 3
     }

@Ashh something like this Cat.aggregate([ { $match: {status: 'active' }}, { $lookup: {from: 'traininglevels', localField: 'levels', foreignField: '_id', as: 'trainingLevels'} }, { "$project": { noOfLevels: 1, name: 1, catNo: 1, createdAt: 1, trainingLevels: {$sum:'$trainingLevels.videoId'} }} ]) But I know it doesn't compliance with the aggregation scenario. — Piyush Jain
– Piyush Jain, Commented Jan 17, 2020 at 13:14

Ashh · Accepted Answer · 2020-01-17 13:54:02Z

1

You need to $unwind the trainingLevels array to make it an object property and then you can use .dot notation to get the videoId length.

Cat.aggregate([
  { "$match": { "status": "active" } },
  { "$lookup": {
    "from": "traininglevels",
    "let": { "levels": "$levels" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$_id", "$$levels"] }}},
      { "$group": {
        "_id": null,
        "totalVideos": { "$sum": { "$size": "$videoId" }}
      }}
    ]
    "as": "trainingLevels"
  }},
  { "$unwind": "$trainingLevels" },
  { "$project": {
    "noOfLevels": 1,
    "name": 1,
    "catNo": 1,
    "createdAt": 1,
    "trainingLevels": "$trainingLevels.totalVideos"
  }}
])

edited Jan 17, 2020 at 13:54

answered Jan 17, 2020 at 13:18

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Piyush Jain Over a year ago

I get repeated categories in this case:- "categories": [ { "_id": "5e1d8c3ed06661d2b06a7c46", "noOfLevels": 3, "name": "HOW TO SAVE FUEL", "catNo": 1, "totalVideos": 0 }, { "_id": "5e1d8c3ed06661d2b06a7c46", "noOfLevels": 3, "name": "HOW TO SAVE FUEL", "catNo": 1, "totalVideos": 5, },

Ashh Over a year ago

Is it possible to have same _id for the multiple documents? I can see "foreignField": "_id" which definitely means you are matching with _id of the traininglevels collection and that should not be the same for the multiple documents.

Piyush Jain Over a year ago

In category collection levels are referenced as an array of ObjectId, also, levels has videoIds as an array of ObjectId. What happened here is that it returns data for objects which has videoId present in levels collection. And it didn't returns the data for levels where videoId is empty array[].

Piyush Jain Over a year ago

And grouping is also not achieved with this.

Piyush Jain Over a year ago

Your answer has worked for the data in categories collection which does not have the levels as empty array, categories which has levels empty [] does not return from the query. Adding this solve the problem:-

{ $unwind: { path: "$trainingLevels", preserveNullAndEmptyArrays: true } }, { "$project": { "noOfLevels": 1,  "name": 1, "catNo": 1,  "createdAt": 1, "totalVideos":  { "$cond":{  if: {  $gte: ['$trainingLevels.totalVideos', 0]  }, then: "$trainingLevels.totalVideos", else: 0   }} }} ])

Collectives™ on Stack Overflow

Count number of elements to get from lookup query for nested referenced array in mongodb

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related