how to do two groupBy query in single aggregation based on the lookup

user_14892756 0
Viewed 45

I have two collections and I need to group documents from both using a single query using $lookup and $group. Here is my db data and the expected output:

currentBills table

const currentBills = [
  {
    memberId: 1,
    name: 'Mark',
    status: 'paid'
  },
  {
    memberId: 2,
    name: 'Steve',
    status: 'unpaid'
  },
  {
    memberId: 3,
    name: 'William',
    status: 'paid'
  }
];

pastBills table

const pastBills = [
  {
    memberId: 5,
    name: 'Joe',
    status: 'paid'
  },
  {
    memberId: 8,
    name: 'Carlos',
    status: 'paid'
  },
  {
    memberId: 25,
    name: 'ben',
    status: 'unpaid'
  }
];

expected output


const result = {
  CurrentBill: {
    paid: [
      {
        memberId: 1,
        name: 'Mark',
        status: 'paid'
      },

      {
        memberId: 3,
        name: 'William',
        status: 'paid'
      }
    ],
    unpaid: [
      {
        memberId: 2,
        name: 'Steve',
        status: 'unpaid'
      }
    ]
  },
  pastBill: {
    paid: [
      {
        memberId: 5,
        name: 'Joe',
        status: 'paid'
      },
      {
        memberId: 8,
        name: 'Carlos',
        status: 'paid'
      }
    ],
    unpaid: [
      {
        memberId: 25,
        name: 'ben',
        status: 'unpaid'
      }
    ]
  }
};

my query

db.getCollection('currentBills').aggregate([
{
   $lookup:
     {
       from: "pastBills",
       localField: "memberId",
       foreignField: "memberId",
       as: "bill"
     }
},{
    $unwind : "$bill"
    },
    {
        $group : {
            _id : "$status"
            }
        }
        ])

How to group each collections by the status and return a single result? Is this possible using a single aggregation query?

Thanks in advance!

aggregation-framework mongodb-query node.js mongodb
2 Answers

Demo - https://mongoplayground.net/p/qyA1QsjfAsT

Note - This is overkill if you don't have limited set of records

db.currentBills.aggregate([
  {
    $group: { // group by status
      _id: "$status",
      currentBills: { $push: "$$ROOT" }
    }
  },
  {
    $lookup: { // add pastBills to for the status paid in paid doc and unpaid in unpaid doc
      from: "pastBills",
      let: { status: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$status", "$status" ] } } }
      ],
      as: "pastBills"
    }
  },
  {
    $set: { // reshape docs with paid and unpaid keys
      currentBills: {
        $cond: [
          { $eq: [ "$_id", "paid" ] }, // condition
          { paid: "$currentBills" }, // true
          { unpaid: "$currentBills" } // false
        ]
      },
      pastBills: {
        $cond: [
          { $eq: [ "$_id", "paid" ] },
          { paid: "$currentBills" },
          { unpaid: "$currentBills" }
        ]
      }
    }
  },
  {
    $group: {
      _id: null, // group all documents
      currentBills: { $push: "$currentBills" },
      pastBills: { $push: "$pastBills" }
    }
  },
  { $project: { _id: 0 } }
])
user_22242650

Is this possible using a single aggregation query?

Its possible in single query but really bad idea, it may cause the performance issues, it would be better if you do 2 separate queries first for currentBills and second for pastBills and merge both the result in your programming language, because both the collections are different. see playground solution for single collection.

For single query you can try,

  • $addFields to add coll field to identify this is currentBills document
  • $unionWith to union pastBills documents and also $addFields to add coll field to identify pastBills document
  • $group by coll and status and construct the root members array
  • $group by only coll and construct the array of coll in key-value format
  • $group by null and construct the root document array in key-value format after converting coll from array to object
  • $replaceRoot to replace documents in root after converting it from array to object using $arrayToObject
db.currentBills.aggregate([
  { $addFields: { coll: "currentBills" } },
  {
    $unionWith: {
      coll: "pastBills",
      pipeline: [{ $addFields: { coll: "pastBills" } }]
    }
  },
  {
    $group: {
      _id: { coll: "$coll", status: "$status" },
      members: { $push: "$$ROOT" }
    }
  },
  {
    $group: {
      _id: "$_id.coll",
      coll: { $push: { k: "$_id.status", v: "$members" } }
    }
  },
  {
    $group: {
      _id: null,
      root: {
        $push: {
          k: "$_id",
          v: { $arrayToObject: "$coll" }
        }
      }
    }
  },
  { $replaceRoot: { newRoot: { $arrayToObject: "$root" } } }
])

Playground

READ THIS BEFORE USE THIS QUERY:

user_89871280
Related