CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
sachin.pandey
sachin.pandey

Reputation: 39

mongodb aggregate to find,count and project unique documnets

Below are the sample collection.

col1:

 "_id" : ObjectId("5ec293782bc00b43b463b67c")
    "status" : ["running"],
    "name" : "name1 ",
    "dcode" : "dc001",
    "address" : "address1",
    "city" : "city1"

col2:

     "_id" : ObjectId("5ec296182bc00b43b463b68f"),
     "scode" : ObjectId("5ec2933df6079743c0a2a1f8"),
    "ycode" : ObjectId("5ec293782bc00b43b463b67c"),
    "city" : "city1",
    "lockedDate" : ISODate("2020-05-20T00:00:00Z"),

    "_id" : ObjectId("5ec296182bc00b43b463688b"),
     "scode" : ObjectId("5ec2933df6079743c0a2a1ff"),
    "ycode" : ObjectId("5ec293782bc00b43b463b67c"),
    "city" : "city1",
    "lockedDate" : ISODate("2020-05-20T00:00:00Z"),

    "_id" : ObjectId("5ec296182bc00b43b44fc6cb"),
     "scode" :null,
    "ycode" : ObjectId("5ec293782bc00b43b463b67c"),
    "city" : "city1",
    "lockedDate" : ISODate("2020-05-20T00:00:00Z"),

problemStatement:

I want to display name from col1 & count of documents from col2 according to ycode where scode is != null

Tried attempt:

db.col1.aggregate([
        {'$match':{
   city:'city1'
    }
      },
   {
       $lookup:
       {
           from: "col2",
           let: {
               ycode: "$_id",city:'$city'
           },

           pipeline: [
               {
                   $match: {
                       scode:{'$ne':null},
                     lockedDate:ISODate("2020-05-20T00:00:00Z"),
                       $expr: {
                           $and: [
                               {
                                   $eq: [
                                       "$ycode",
                                       "$$ycode"
                                   ]
                               },
                               {
                                   $eq: [
                                       "$city",
                                       "$$city"
                                   ]
                               }
                           ]
                       }, 
                   },

               },
                   ], as: "col2"
               }
   }, 
   {'$unwind':'$col2'},
   {'$count':'ycode'},
   {
       $project: {
           name: 1,
           status: 1,

       }
   },
])

now problem with this query is it either displays the count or project the name & status i.e if i run this query in the current format it gives {} if I remove {'$count':'ycode'} then it project the values but doesn't give the count and if I remove $project then i do get the count {ycode:2} but then project doesn't work but I want to achieve both in the result. Any suggestions

ORM: mongoose v>5, mongodb v 4.0

Upvotes: 1

Views: 718

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

You can try below query :

db.col1.aggregate([
    { "$match": { city: "city1" } },
    {
      $lookup: {
        from: "col2",
        let: { id: "$_id", city: "$city" }, /** Create local variables from fields of `col1` but not from `col2` */
        pipeline: [
          {
            $match: { scode: { "$ne": null }, lockedDate: ISODate("2020-05-20T00:00:00Z"),
              $expr: { $and: [ { $eq: [ "$ycode", "$$id" ] }, { $eq: [ "$city", "$$city" ] } ] }
            }
          },
          { $project: { _id: 1 } } // Optional, But as we just need count but not the entire doc, holding just `_id` helps in reduce size of doc
        ],
        as: "col2" // will be an array either empty (If no match found) or array of objects
      }
    },
    {
      $project: { _id: 0, name: 1, countOfCol2: { $size: "$col2" } }
    }
  ])

Test : mongoplayground

Upvotes: 2

Related Questions