CODEWITHSUNDEEP
mongodbaggregation-framework
Deano
Deano

Reputation: 12230

mongodb aggregation - nested group

I'm trying to perform nested group, I have an array of documents that has two keys (invoiceIndex, proceduresIndex) I need the documents to be arranged like so

invoices (parent) -> procedures (children)

invoices: [ // Array of invoices
   {
    ..... 
   "procedures": [{}, ...] // Array of procedures
  }
]

Here is a sample document 

{
    "charges": 226.09000000000003,
    "currentBalance": 226.09000000000003,
    "insPortion": "",
    "currentInsPortion": "",
    "claim": "notSent",
    "status": "unpaid",
    "procedures": {
      "providerId": "9vfpjSraHzQFNTtN7",
      "procedure": "21111",
      "description": "One surface",
      "category": "basicRestoration",
      "surface": [
        "m"
      ],
      "providerName": "B Dentist",
      "proceduresIndex": "0"
    },
    "patientId": "mE5vKveFArqFHhKmE",
    "patientName": "Silvia Waterman",
    "invoiceIndex": "0",
    "proceduresIndex": "0"
}

Here is what I have tried

https://mongoplayground.net/p/AEBGmA32n8P

Upvotes: 2

Views: 57

Answers (1)

buræquete
buræquete

Reputation: 14698

Can you try the following;

db.collection.aggregate([
  {
    $group: {
      _id: "$invoiceIndex",
      procedures: {
        $push: "$procedures"
      },
      invoice: {
        $first: "$$ROOT"
      }
    }
  },
  {
    $addFields: {
      "invoice.procedures": "$procedures"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$invoice"
    }
  }
])

I retain the invoice fields with invoice: { $first: "$$ROOT" }, also keep procedures's $push logic as a separate field. Then with $addFields I move that array of procedures into the new invoice object. Then replace root to that.

You shouldn't use the procedureIndex as a part of _id in $group, for you won't be able to get a set of procedures, per invoiceIndex then. With my $group logic it works pretty well as you see.

Link to mongoplayground

Upvotes: 2

Related Questions