mongodb - Mongodb查询：在结果查询中生成“表格式”

Question

我收集了几天内每个 IP 和 MAC 的网络流量。该集合看起来像：

{
    "_id" : {
        "ip" : "190.112.100.0",
        "date" : "05/10/2020",
        "mac" : "bcd1657bca6e"
    },
    "total_in_bytes" : 237203,
},
{
    "_id" : {
        "ip" : "191.110.90.0",
        "date" : "05/10/2020",
        "mac" : "bcd165bc8ae5"
    },
    "total_in_bytes" : 197102,
},
{
    "_id" : {
        "ip" : "190.112.100.0",
        "date" : "06/10/2020",
        "mac" : "bcd1657bca6e"
    },
    "total_in_bytes" : 205210,
},
{
    "_id" : {
        "ip" : "190.112.100.0",
        "date" : "07/10/2020",
        "mac" : "bcd1657bca6e"
    },
    "total_in_bytes" : 223245,
},
{
    "_id" : {
        "ip" : "191.110.90.0",
        "date" : "07/10/2020",
        "mac" : "bcd165bc8ae5"
    },
    "total_in_bytes" : 208200,
}

现在我需要以表格格式或等效的 CSV 格式获取每天的“total_in_bytes”数量

ip              |   mac         |   05/10/2020      |   06/10/2020      |   07/10/2020
----------------------------------------------------------------------------------------
190.112.100.0       bcd1657bca6e    237203              205210              223245
191.110.90.0        bcd165bc8ae5    197102              -----               208200

标签： mongodbmongodb-query

Answer 1

你必须做一些JavaScript。像这样的东西（未经测试）：

var header = ["ip", "mac"];
var dates = db.collection.aggregate([
    {$group: {_id: null, date_set: {$addToSet: "$_id.date"}}}
]).toArray().shift().date_set;
header.push(dates);

print(header.join("|"));

db.collection.aggregate([
  {
    $group: {
      _id: { ip: "$_id.ip", mac: "$_id.mac" },
      data: {
        $push: {
          total_in_bytes: "$total_in_bytes",
          date: "$_id.date"
        }
      }
    }
  }
]).forEach(function(doc) {
   var row = [doc._id.ip, doc._id.mac];
   dates.forEach(function(d) {
      doc.data.forEach(function(val) {
        if (val.date == d) 
          row.push(val.total_in_bytes);
      });
   });
   print(row .join("|"));
})