CODEWITHSUNDEEP
mongodbmongodb-query
doptimusprime
doptimusprime

Reputation: 9395

Mongodb combine aggregate queries

I have following collections in MongoDB

Profile Collection

> db.Profile.find()
{ "_id" : ObjectId("5ec62ccb8897af3841a46d46"), "u" : "Test User", "is_del": false }

Store Collection

> db.Store.find()
{ "_id" : ObjectId("5eaa939aa709c30ff4703ffd"), "id" : "5ec62ccb8897af3841a46d46",  "a" : { "ci": "Test City", "st": "Test State" }, "ip" : false }, "op" : [ ], "b" : [ "normal" ], "is_del": false}

Item Collection

> db.Item.find()
{ "_id" : ObjectId("5ea98a25f1246b53a46b9e10"), "sid" : "5eaa939aa709c30ff4703ffd", "n" : "sample", "is_del": false}

Relation among these collections are defined as follows:

  1. Profile -> Store: It is 1:n relation. id field in Store relates with _id field in Profile.
  2. Store -> Item: It is also 1:n relation. sid field in Item relates with _id field in Store.

Now, I need to write a query to find the all the store of profiles alongwith their count of Item for each store. Document with is_del as true must be excluded.

I am trying it following way:

  1. Query 1 to find the count of item for each store.
  2. Query 2 to find the store for each profile.

Then in the application logic use both the result to produce the combined output.

I have query 1 as follows:

db.Item.aggregate({$group: {_id: "$sid", count:{$sum:1}}})

Query 2 is as follows:

db.Profile.aggregate([{ "$addFields": { "pid": { "$toString": "$_id" }}},   { "$lookup": {"from": "Store","localField": "pid","foreignField": "id",  "as": "stores"}}])

In the query, is_del is also missing. Is there any simpler way to perform all these in a single query? If so, what will be scalability impact?

Upvotes: 1

Views: 62

Answers (1)

thammada.ts
thammada.ts

Reputation: 5245

You can use uncorrelated sub-queries, available from MongoDB v3.6

db.Profile.aggregate([
  {
    $match: { is_del: false }
  },
  {
    $lookup: {
      from: "Store",
      as: "stores",
      let: {
        pid: { $toString: "$_id" }
      },
      pipeline: [
        {
          $match: {
            is_del: false,
            $expr: { $eq: ["$$pid", "$id"] }
          }
        },
        {
          $lookup: {
            from: "Item",
            as: "items",
            let: {
              sid: { $toString: "$_id" }
            },
            pipeline: [
              {
                $match: {
                  is_del: false,
                  $expr: { $eq: ["$$sid", "$sid"] }
                }
              },
              {
                $count: "count"
              }
            ]
          }
        },
        {
          $unwind: "$items"
        }
      ]
    }
  }
])

Mongo Playground

To improve performance, I suggest you store the reference ids as ObjectId so you don't have to convert them in each step.

Upvotes: 2

Related Questions