CODEWITHSUNDEEP
node.jsmongodbmongoose
Umesh
Umesh

Reputation: 1242

Mongodb - Find count of distinct items after applying aggregate and match

Trying to figure out something from Mongo using mongoose in optimal way.

I have following documents

Regions

{
    "_id" : ObjectId("5cf21263ff605c49cd6d8016"),
    "name" : "Asia"
}

Countries can be part of multiple regions

{
    "_id" : ObjectId("5d10a4ad80a93a1d7cd56cc6"),
    "regions" : [ 
        ObjectId("5d10a50080a93a1d7cd56cc7"), 
        ObjectId("5cf2126bff605c49cd6d8017")
    ],
    "name" : "India"
}

Places belongs to one country

{
    "_id" : ObjectId("5d11bb8180a93a1d7cd56d26"),
    "name" : "Delhi",
    "country" : ObjectId("5d136e7a4e480863a51c4056"),
}

Programs each in dayshows array represents one day. On a day show can cover multiple places. 

{
    "_id" : ObjectId("5d11cc9480a93a1d7cd56d31"),
    "dayshows" : [ 
        { 
            "_id" : ObjectId("5d11cc9480a93a1d7cd56d41"),
            "places" : [ 
                ObjectId("5d11bb8180a93a1d7cd56d26")
            ],
        },
         {
            "_id" : ObjectId("5d11cc9480a93a1d7cd56d3c"),
            "places" : [ 
                ObjectId("5d11bb8180a93a1d7cd56d26"),
                ObjectId("5d11bc7c80a93a1d7cd56d2e")
            ]
         }
     ]
}

What am I trying to figure out?

For a given region, for each country in region which all places are covered and count of programs for each place. Using nodejs and mongoose.

Example 

Input - Asia

Output
India
 - Delhi (3)
 - Mumbai (5)

Thailand
 - Pattaya (2)
 - Bangkok (5)

New to mongo.

Upvotes: 0

Views: 132

Answers (1)

Valijon
Valijon

Reputation: 13113

You need to use $lookup to cross different collections.

Pipeline:
Stages 1-6 serves to get all related data.
(Optional) Stages 7-10 serves to transform aggregated data into key:pair object.

ASSUMPTION

Programs to visit 2 places counted as is (Place1: +1, Place2: +1)
You know how to execute MongoDB aggregation in node.js

db.Regions.aggregate([
  {
    $match: {
      name: "Asia"
    }
  },
  {
    $lookup: {
      from: "Countries",
      let: {
        region: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$$region",
                "$regions"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "Places",
            localField: "_id",
            foreignField: "country",
            as: "Places"
          }
        }
      ],
      as: "Countries"
    }
  },
  {
    $unwind: "$Countries"
  },
  {
    $unwind: "$Countries.Places"
  },
  {
    $lookup: {
      from: "Programs",
      localField: "Countries.Places._id",
      foreignField: "dayshows.places",
      as: "Countries.Places.Programs"
    }
  },
  {
    $project: {
      "name": 1,
      "Countries.name": 1,
      "Countries.Places.name": 1,
      "Countries.Places.Programs": {
        $size: "$Countries.Places.Programs"
      }
    }
  },
  {
    $group: {
      _id: {
        name: "$name",
        Countries: "$Countries.name"
      },
      Places: {
        $push: {
          k: "$Countries.Places.name",
          v: "$Countries.Places.Programs"
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      Places: {
        $arrayToObject: "$Places"
      }
    }
  },
  {
    $group: {
      _id: "$_id.name",
      Countries: {
        $push: {
          k: "$_id.Countries",
          v: "$Places"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      name: "$_id",
      Countries: {
        $arrayToObject: "$Countries"
      }
    }
  }
])

MongoPlayground

Upvotes: 1

Related Questions