CODEWITHSUNDEEP
mongodbaggregation-framework
bilak
bilak

Reputation: 4922

mongo nested aggregation with join

I've following tenant collection:

{id: 1, name: "T1", type: "DEFAULT", state: "ACTIVE"},
{id: 2, name: "T2", type: "DEFAULT", state: "DISABLED"},
{id: 3, name: "T3", type: "STANDARD", state: "ACTIVE"},
{id: 4, name: "T4", type: "TRIAL", state: "DELETED"},
{id: 5, name: "T5", type: "DEFAULT", state: "DISABLED"}

and then second collection with options:

{id:1, tenantId: 1, opt: "OPERATING"},
{id:2, tenantId: 2, opt: "OPERATING"},
{id:3, tenantId: 3, opt: "POSTPONED"},
{id:4, tenantId: 4, opt: "DELETED"},
{id:5, tenantId: 5, opt: "POSTPONED"}

Id' like to aggregate this collections to get umber of tenant types grouped with number of operations, but I'd like to remove all DELETED tenants and all DELETED options from search. Something like this:

{type: "DEFAULT", count: 3, opts: {operating: 2, postponed: 1}}
{type: "STANDARD", count: 1, opts: {postponed: 1}}

Grouping the tenants is fine, but I don't know what should I use for that next grouping of options.

db.tenant.aggregate([
  {$match: { state: {$ne: "DELETED"}}},
  {$lookup: {
    from: "option",
    localField: "_id",
    foreignField: "tenantId",
    as: "options"
  }},
  {$group {
    _id: "$type",
    count: {$sum: 1}
  }}
])

Upvotes: 1

Views: 31

Answers (1)

turivishal
turivishal

Reputation: 36104

  • $group by type and get group of ids
  • $lookup with pipeline match $in condition for tenantId
  • $group by opt and get count of option
  • $project to show fields in k and v format
  • $project to show required fields, $size to count total tenant and $arrayToObject convert opts array to object
db.tenant.aggregate([
  { $match: { state: { $ne: "DELETED" } } },
  {
    $group: {
      _id: "$type",
      ids: { $push: "$id" }
    }
  },
  {
    $lookup: {
      from: "options",
      let: { ids: "$ids" },
      pipeline: [
        { $match: { opt: { $ne: "DELETED" }, $expr: { $in: ["$tenantId", "$$ids"] } } },
        {
          $group: {
            _id: "$opt",
            count: { $sum: 1 }
          }
        },
        {
          $project: {
            _id: 0,
            k: "$_id",
            v: "$count"
          }
        }
      ],
      as: "opts"
    }
  },
  {
    $project: {
      _id: 0,
      type: "$_id",
      count: { $size: "$ids" },
      opts: { $arrayToObject: "$opts" }
    }
  }
])

Playground

Upvotes: 1

Related Questions