title: NodeJS MongoDB Mongoose export nested subdocuments and arrays to XLSX columns
body: <p>I have query results from MongoDB as an array of documents with nested subdocuments and arrays of subdocuments.</p>
  
  <pre><code>[
    {
      RecordID: 9000,
      RecordType: 'Item',
      Location: {
        _id: 5d0699326e310a6fde926a08,
        LocationName: 'Example Location A'
      }
      Items: [
        {
          Title: 'Example Title A',
          Format: {
            _id: 5d0699326e310a6fde926a01,
            FormatName: 'Example Format A'
          }
        },
        {
          Title: 'Example Title B',
          Format: {
            _id: 5d0699326e310a6fde926a01,
            FormatName: 'Example Format B'
          }
        }
      ],
    },
    {
      RecordID: 9001,
      RecordType: 'Item',
      Location: {
        _id: 5d0699326e310a6fde926a08,
        LocationName: 'Example Location C'
      },
      Items: [
        {
          Title: 'Example Title C',
          Format: {
            _id: 5d0699326e310a6fde926a01,
            FormatName: 'Example Format C'
          }
        }
      ],
    }
  ]
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>I need to export the results to XLSX in column order. The XLSX library is working to export the <em>top-level</em> properties (such as RecordID and RecordType) only. I also need to export the nested objects and arrays of objects. Given a list of property names e.g. <code>RecordID, RecordType, Location.LocationName, Items.Title, Items.Format.FormatName</code> the properties must be exported to XLSX columns in the specified order.</p>
  
  <p><strong>Desired result</strong></p>
  
  <p>Here is the desired 'flattened' structure (or something similar) that 
  I think <em>should</em> be able to convert to XLSX columns.</p>
  
  <pre><code>[
    {
      'RecordID': 9000,
      'RecordType': 'Item',
      'Location.LocationName': 'Example Location A',
      'Items.Title': 'Example Title A, Example Title B',
      'Items.Format.FormatName': 'Example Format A, Example Format B',
    },
    {
      'RecordID': 9001,
      'RecordType': 'Item',
      'Location.LocationName': 'Example Location C',
      'Items.Title': 'Example Title C',
      'Items.Format.FormatName': 'Example Format C',
    }
  ]
  </code></pre>
  
  <p>I am using the XLSX library to convert the query results to XLSX which works for top-level properties only.</p>
  
  <pre><code>  const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(results.data);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
  
    const data: Blob = new Blob([excelBuffer], { type: EXCEL_TYPE });
    FileSaver.saveAs(data, new Date().getTime());
  </code></pre>
  
  <p><strong>POSSIBLE OPTIONS</strong></p>
  
  <p>I am guessing I need to 'flatten' the structure either using aggregation in the query or by performing post-processing when the query is returned.</p>
  
  <p><strong>Option 1: Build the logic in the MongoDB query to flatten the results.</strong></p>
  
  <p><a href="https://docs.mongodb.com/manual/reference/operator/aggregation/replaceRoot/" rel="nofollow noreferrer">$replaceRoot</a> might work since it is able to "promote an existing embedded document to the top level". Although I am not sure if this will solve the problem exactly, I do not want to modify the documents in place, I just need to flatten the results for exporting.</p>
  
  <p>Here is the MongoDB query I am using to produce the results:</p>
  
  <pre><code>records.find({ '$and': [ { RecordID: { '$gt': 9000 } } ]},
    { skip: 0, limit: 10, projection: { RecordID: 1, RecordType: 1, 'Items.Title': 1, 'Items.Location': 1 }});
  </code></pre>
  
  <p><strong>Option 2: Iterate and flatten the results on the Node server</strong></p>
  
  <p>This is likely not the most performant option, but might be the easiest if I can't find a way to do so within the MongoDB query.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I may be able to use MongoDB aggregate <code>$project</code> to 'flatten' the results. For example, this aggregate query effectively 'flattens' the results by 'renaming' the properties. I just need to figure out how to implement the query conditions within the aggregate operation.</p>
  
  <pre><code>db.records.aggregate({
    $project: {
      RecordID: 1,
      RecordType: 1,
      Title: '$Items.Title',
      Format: '$Items.Format'
    }
  })
  </code></pre>
  
  <p><strong>UPDATE 2:</strong></p>
  
  <p>I have abandoned the $project solution because I would need to change the entire API to support aggregation. Also, I would need to find a solution for populate because aggregate does not support it, rather, it uses $lookup which is possible but time consuming because I would need to write the queries dynamically. I am going back to look into how to flatten the object by creating a function to iterate the array of objects recursively.</p>