NodeJS MongoDB Mongoose将嵌套的子文档和数组导出到XLSX列

Question

我将MongoDB的查询结果作为包含嵌套子文档和子文档数组的文档数组。

[
  {
    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'
        }
      }
    ],
  }
]

问题

我需要按列顺序将结果导出到XLSX。XLSX库仅用于导出顶级属性（如RecordID和RecordType）。我还需要导出嵌套对象和对象数组。给定属性名称列表，例如，RecordID、RecordType、Location.LocationName、Items.Title、Items.Format.FormatName属性必须按指定顺序导出到XLSX列。

期望结果

下面是所需的“扁平”结构（或类似结构），我认为应该能够转换为XLSX列。

[
  {
    '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',
  }
]

我正在使用XLSX库将查询结果转换为XLSX，这只适用于顶级属性。

  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());

可能的选择

我猜我需要在查询中使用聚合或在返回查询时执行后处理来“展平”结构。

选项1：在MongoDB查询中构建逻辑以展平结果。

$replace eRoot可能有效，因为它能够“将现有的嵌入式文档提升到顶层”。虽然我不确定这是否会确切地解决问题，但我不想修改到位的文档，我只需要将结果展平以供导出。

下面是我用来生成结果的MongoDB查询：

records.find({ '$and': [ { RecordID: { '$gt': 9000 } } ]},
  { skip: 0, limit: 10, projection: { RecordID: 1, RecordType: 1, 'Items.Title': 1, 'Items.Location': 1 }});

选项2：迭代和展平节点服务器上的结果

这可能不是性能最好的选项，但如果在MongoDB查询中找不到这样做的方法，这可能是最简单的。

更新：

我可以使用MongoDB aggregate$project来“展平”结果。例如，此聚合查询通过“重命名”属性有效地“展平”结果。我只需要弄清楚如何在聚合操作中实现查询条件。

db.records.aggregate({
  $project: {
    RecordID: 1,
    RecordType: 1,
    Title: '$Items.Title',
    Format: '$Items.Format'
  }
})

更新2：

我放弃了$project解决方案，因为我需要更改整个API以支持聚合。此外，我还需要为populate找到一个解决方案，因为aggregate不支持它，相反，它使用$lookup，这是可能的，但很耗时，因为我需要动态编写查询。我将回顾如何通过创建一个递归迭代对象数组的函数来展平对象。

Answer 1

我编写了一个函数来迭代结果数组中的所有对象，并递归地创建新的展平对象。这里显示的对象函数与前面的答案类似，我从这个相关的答案中获得了额外的灵感。

由于objectid仍然作为bson类型返回，即使我设置了lean（）选项，因此“\u id”属性也不会被添加到展平对象中。

我仍然需要弄清楚如何对对象进行排序，使它们按照给定的顺序排列，例如，RecordID、RecordType、Items.Title。我相信通过创建一个单独的函数来迭代展平的结果可能是最容易实现的，尽管不一定是性能最好的。如果有人对如何按给定顺序实现对象排序有任何建议或对解决方案有任何改进，请告诉我。

const apiCtrl = {};

/**
 * Async array iterator
 */
apiCtrl.asyncForEach = async (array, callback) => {
  for (let index = 0; index < array.length; index++) {
    await callback(array[index], index, array)
  }
}

// Check if a value is an object
const isObject = (val) => {
  return typeof val == 'object' && val instanceof Object && !(val instanceof Array);
}

// Check if a value is a date object
const isDateObject = (val) => {
  return Object.prototype.toString.call(val) === '[object Date]';
}

/**
 * Iterate object properties recursively and flatten all values to top level properties
 * @param {object} obj Object to flatten
 * @param {string} prefix A string to hold the property name
 * @param {string} res A temp object to store the current iteration
 * Return a new object with all properties on the top level only
 *
 */
const flattenObject = (obj, prefix = '', res = {}) =>

  Object.entries(obj).reduce((acc, [key, val]) => {
    const k = `${prefix}${key}`

    // Skip _ids since they are returned as bson values
    if (k.indexOf('_id') === -1) {
      // Check if value is an object
      if (isObject(val) && !isDateObject(val)) {
        flattenObject(val, `${k}.`, acc)
      // Check if value is an array
      } else if (Array.isArray(val)) {
        // Iterate each array value and call function recursively
        val.map(element => {
          flattenObject(element, `${k}.`, acc);
        });
      // If value is not an object or an array
      } else if (val !== null & val !== 'undefined') {
        // Check if property has a value already
        if (res[k]) {
          // Check for duplicate values
          if (typeof res[k] === 'string' && res[k].indexOf(val) === -1) {
            // Append value with a separator character at the beginning
            res[k] += '; ' + val;
          }
        } else {
          // Set value
          res[k] = val;
        }
      }
    }

    return acc;

  }, res);

/**
 * Convert DB query results to an array of flattened objects
 * Required to build a format that is exportable to csv, xlsx, etc.
 * @param {array} results Results of DB query
 * Return a new array of objects with all properties on the top level only
 */
apiCtrl.buildExportColumns = async (results) => {

  const data = results.data;
  let exportColumns = [];

  if (data && data.length > 0) {
    try {
      // Iterate all records in results data array
      await apiCtrl.asyncForEach(data, async (record) => {

        // Convert the multi-level object to a flattened object
        const flattenedObject = flattenObject(record);
        // Push flattened object to array
        exportColumns.push(flattenedObject);

      });
    } catch (e) {
      console.error(e);
    }
  }

  return exportColumns;

}

Answer 2

下面是通过函数flattobject转换服务器上Mongo数据的解决方案，该函数递归展平嵌套对象并为嵌套路径返回“点类型”键。

请注意，下面的代码段包含一个函数，用于呈现和编辑要预览的表，但是，当您运行代码段并单击“下载”按钮时，应该触发您想要的重要部分（下载文件）。

const flattenObject = (obj, prefix = '') =>
  Object.keys(obj).reduce((acc, k) => {
    const pre = prefix.length ? prefix + '.' : '';
    if (typeof obj[k] === 'object') Object.assign(acc, flattenObject(obj[k], pre + k));
    else acc[pre + k] = obj[k];
    return acc;
  }, {});

var data = [{
    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"
      }
    }]
  }
];

const EXCEL_MIME_TYPE = `application/vnd.ms-excel`;
const flattened = data.map(e => flattenObject(e));
const ws_default_header = XLSX.utils.json_to_sheet(flattened);
const ws_custom_header = XLSX.utils.json_to_sheet(flattened, {
  header: ['Items.Title', 'RecordID', 'RecordType', 'Location.LocationName', 'Items.Format.FormatName']
});
const def_workbook = XLSX.WorkBook = {
  Sheets: {
    'data': ws_default_header
  },
  SheetNames: ['data']
}

const custom_workbook = XLSX.WorkBook = {
  Sheets: {
    'data': ws_custom_header
  },
  SheetNames: ['data']
}

const def_excelBuffer = XLSX.write(def_workbook, {
  bookType: 'xlsx',
  type: 'array'
});

const custom_excelBuffer = XLSX.write(custom_workbook, {
  bookType: 'xlsx',
  type: 'array'
});

const def_blob = new Blob([def_excelBuffer], {
  type: EXCEL_MIME_TYPE
});

const custom_blob = new Blob([custom_excelBuffer], {
  type: EXCEL_MIME_TYPE
});

const def_button = document.getElementById('dl-def')
/* trigger browser to download file */
def_button.onclick = e => {
  e.preventDefault()
  saveAs(def_blob, `${new Date().getTime()}.xlsx`);
}

const custom_button = document.getElementById('dl-cus')
/* trigger browser to download file */
custom_button.onclick = e => {
  e.preventDefault()
  saveAs(custom_blob, `${new Date().getTime()}.xlsx`);
}

/*
  render editable table to preview (for SO convenience)
*/
const html_string_default = XLSX.utils.sheet_to_html(ws_default_header, {
  id: "data-table",
  editable: true
});

const html_string_custom = XLSX.utils.sheet_to_html(ws_custom_header, {
  id: "data-table",
  editable: true
});
document.getElementById("container").innerHTML = html_string_default;
document.getElementById("container-2").innerHTML = html_string_custom;

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>

<head>
  <title>Excel file generation from JSON</title>
  <meta charset="utf-8" />
  <style>
    .xport,
    .btn {
      display: inline;
      text-align: center;
    }
    
    a {
      text-decoration: none
    }
    
    #data-table,
    #data-table th,
    #data-table td {
      border: 1px solid black
    }
  </style>
</head>
<script>
  function render(type, fn, dl) {
    var elt = document.getElementById('data-table');
    var wb = XLSX.utils.table_to_book(elt, {
      sheet: "Sheet JS"
    });
    return dl ?
      XLSX.write(wb, {
        bookType: type,
        bookSST: true,
        type: 'array'
      }) :
      XLSX.writeFile(wb, fn || ('SheetJSTableExport.' + (type || 'xlsx')));
  }
</script>
<div>Default Header</div>
<div id="container"></div>
<br/>
<div>Custom Header</div>
<div id="container-2"></div>
<br/>
<table id="xport"></table>
<button type="button" id="dl-def">Download Default Header Config</button>
<button type="button" id="dl-cus">Download Custom Header Config</button>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>