mongodb aggregation - nested group

Question

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

bvrakvs · Accepted Answer · 2019-08-02 03:19:29Z

2

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

edited Aug 2, 2019 at 3:19

answered Aug 2, 2019 at 3:11

bvrakvs

14.7k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mongodb aggregation - nested group

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related