I am quite new to MongoDB, using it first time on a large scale app. We have a complicated nested structure representing an object with multiple documents associated with it, and multiple people associated with each of those documents. For GUI building purposes, I need to extract and concat some information from the document hiearchy and "promote" it to a top level, creating a simple flat structure. If there are multiple results for each nested "subquery", I am interested only in the first one.

I have tried to achieve this with the aggregation builder, but each time I ended up with over a hundred lines of endless unwind, addfield, project and it became too long and complicated (and also probably not very fast) to be viable. There has to be a simpler solution. Let me provide an example structure (I have omitted most fields, leaving just the essential ones for brevity):

{
    "_id": ObjectId(),
    "number": "ABC-123456",
    "status": "new",
    "items": [
        {
            "_id": ObjectId(),
            "name": "invoice",
            "people": [
                {
                    "first_name": "John",
                    "last_name": "Doe",
                    "active": false
                },
                {
                    "first_name": "Jane",
                    "last_name": "Smith",
                    "active": true
                },
                {
                    "first_name": "Fred",
                    "last_name": "Bloggs",
                    "active": true
                }
            ]
        },
        {
            "_id": ObjectId(),
            "name": "unimportant_document",
            "people": [
                {
                    "first_name": "John",
                    "last_name": "Doe",
                    "active": true
                }
            ]
        },
        {
            "_id": ObjectId(),
            "name": "order",
            "people": [
                {
                    "first_name": "Fred",
                    "last_name": "Bloggs",
                    "active": true
                }
            ]
        }
    ]
}

Now, I would like to get a result similar to this:

{
    "_id": "XXX",
    "number": "ABC-123456",
    "status": "new",
    "invoice_person_full_name": "Jane Smith",
    "order_person_full_name": "Fred Bloggs"
}

Basically, I need to concat "first_name" and "last_name" from the first person with "active": true in an array of people in a document of certain name ("invoice" for "invoice_person_full_name" and "order" for "order_person_full_name").

I don't care about duplicates,so if there are multiple documents with name "invoice" for example, I want to retrieve only the first invoice and first active person for this invoice.

As I said, I have tried doing this with and aggregation builder, using project with filter to filter the items array to only "invoice" names, then unwinding, projecting again filtering people, projecting again to get the first one, unwinding, adding fields, writing JS function to concat the name (in reality there are degrees, middle names etc. that need to be separated with spaces and might be null, so $concat is not enough) and finaly returning. This takes 40 lines of code and works only for a single item name, so I need to combine this multiple times to get result for all names I need. Which probably means I'm not approaching the problem in the right way.

I hope I made the question clear enough, any help would be greatly appreciated!

CodePudding user response：

1. $set

   1.1. invoice_persons - Create field by getting the first document from items array which its name is "invoice".

   1.2. order_persons - Create field by getting the first document from items array which its name is "order".

2. $set

   2.1. invoice_person_full_name - First filter the document from invoice_persons.people which its active is true. Next with $map to perform string concatenation for full name. And lastly, get the first document.

   2.2. order_person_full_name - First filter the document from order_persons.people which its active is true. Next with $map to perform string concatenation for full name. And lastly, get the first document.

3. $unset - Remove fields.

db.collection.aggregate([
  {
    $set: {
      "invoice_persons": {
        $first: {
          $filter: {
            input: "$items",
            cond: {
              $eq: [
                "$$this.name",
                "invoice"
              ]
            }
          }
        }
      },
      "order_persons": {
        $first: {
          $filter: {
            input: "$items",
            cond: {
              $eq: [
                "$$this.name",
                "order"
              ]
            }
          }
        }
      }
    }
  },
  {
    $set: {
      "invoice_person_full_name": {
        $first: {
          $map: {
            input: {
              $filter: {
                input: "$invoice_persons.people",
                cond: {
                  $eq: [
                    "$$this.active",
                    true
                  ]
                }
              }
            },
            in: {
              "$concat": [
                "$$this.first_name",
                " ",
                "$$this.last_name"
              ]
            }
          }
        }
      },
      "order_person_full_name": {
        $first: {
          $map: {
            input: {
              $filter: {
                input: "$order_persons.people",
                cond: {
                  $eq: [
                    "$$this.active",
                    true
                  ]
                }
              }
            },
            in: {
              "$concat": [
                "$$this.first_name",
                " ",
                "$$this.last_name"
              ]
            }
          }
        }
      }
    }
  },
  {
    "$unset": [
      "invoice_persons",
      "order_persons",
      "items"
    ]
  }
])

Sample Mongo Playground