Mongodb combine aggregate queries

Question

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:

Profile -> Store: It is 1:n relation. id field in Store relates with _id field in Profile.
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:

Query 1 to find the count of item for each store.
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?

The structure of your documents in Store collection is a bit strange, is there supposed to be a closing curly bracket after "ip": false ? — thammada.ts
– thammada.ts, Commented Jul 27, 2020 at 4:55
@thammada.ts Thanks for pointing out. I think Ashh corrected this. — doptimusprime
– doptimusprime, Commented Jul 27, 2020 at 6:17
@varman: Sample data is there. If you need more info, let me know. — doptimusprime
– doptimusprime, Commented Jul 27, 2020 at 6:17

thammada.ts · Accepted Answer · 2020-07-27 05:44:05Z

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.

Collectives™ on Stack Overflow

Mongodb combine aggregate queries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related