需要连接mongodb的3集合以查找聚合数据

Question

我对mongodb知之甚少。我有存储列表作为商店收藏，它只存储基本的细节。

Store collection:
[
  {
    "id": "5dc25092d972e71c3b3e6e87",
    "name": "Chad store",
  },
  {
    "id": "5dc2899bfd1ea02f0fceb9ab",
    "name": "Bad store",
  }
]

我有信号记录收集，其中有多个信号记录为每个商店。

信号记录是否定的。对于信号条、信号强度等，很少有存储可能没有信号记录。

Signal records:

[
  {
    "id": "5dc250e0d972e71c3b3e6e88",
    "signalStrength": -180,
    "signalbars": "3",
    "employee": "5db59227f0204855654075ee",
    "store": "5dc25092d972e71c3b3e6e87"
  },
  {
    "id": "5dc251f723760a24de167f8e",
    "signalStrength": -180,
    "signalbars": "3",
    "employee": "5db59227f0204855654075ee",
    "store": "5dc25092d972e71c3b3e6e87"
  },
  {
    "id": "5dc289affd1ea02f0fceb9ac",
    "signalStrength": -80,
    "signalbars": "3",
    "employee": "5db59227f0204855654075ee",
    "store": "5dc2899bfd1ea02f0fceb9ab"
  }
]

然后还有另一个集合，称为速度测试，每个存储类似于信号-记录的字段，如上传/下载速度。

Speed Tests:

[
  {
    "id": "5dc2c170e26c4a484051f4af",
    "ping": 10,
    "downloadSpeed": 7.66,
    "uploadSpeed": 4.22,
    "employee": "5dc2b9467a56f3446dcaf8f6",
    "store": "5dc25092d972e71c3b3e6e87"
  },
  {
    "id": "5dc3a0a92588214e1a938a34",
    "ping": 10,
    "downloadSpeed": 7.66,
    "uploadSpeed": 4.22,
    "employee": "5dc2b9467a56f3446dcaf8f6",
    "store": "5dc25092d972e71c3b3e6e87"
  }
]

现在我确实需要找到存储列表和信号记录的数量(如果没有记录，那么它应该是0)和这些信号强度的平均值，信号条的平均值，平均上传/下载速度等等。

Expected output:

[
  {
    "store": "5dc25092d972e71c3b3e6e87",
    "storeName": "Chad store",
    "averageSignalStrength": -180,
    "averageUploadSpeed": 7.66
    "averageDownloadSpeed": 4.22
    "totalSpeedTests": 2
    "totalSpeedtestTesters": 1
  },
  {
    "store": "5dc2899bfd1ea02f0fceb9ab",
    "storeName": "Bad store",
    "averageSignalStrength": -80,
    "averageUploadSpeed": 0
    "averageDownloadSpeed": 0
    "totalSpeedTests": 0
    "totalSpeedtestTesters": 0
  }
]

我使用了这个内部聚合器，将存储作为父级，并且最低限度地进行测试。我在这里遇到的问题，如果没有速度测试为商店，他们，这些商店没有考虑到，当你做分组。

任何帮助都是很好的

Answer 1

我使用了以下聚合。

$lookup连接信号和speedTests。

$project用于构造计算字段。

$ifNull将空平均值处理为0。

$setDifference用于计数不同的值。

下面是您需要的聚合：

db.store.aggregate([
  {
    $lookup: {
      from: "signals",
      localField: "id",
      foreignField: "store",
      as: "storeSignals"
    }
  },
  {
    $lookup: {
      from: "speedTests",
      localField: "id",
      foreignField: "store",
      as: "speedTests"
    }
  },
  {
    $project: {
      _id: 0,
      store: "$id",
      storeName: "$name",
      averageSignalStrength: {
        $ifNull: [
          {
            $avg: "$storeSignals.signalStrength"
          },
          0
        ]
      },
      averageUploadSpeed: {
        $ifNull: [
          {
            $avg: "$speedTests.uploadSpeed"
          },
          0
        ]
      },
      averageDownloadSpeed: {
        $ifNull: [
          {
            $avg: "$speedTests.downloadSpeed"
          },
          0
        ]
      },
      totalSpeedTests: {
        $size: "$speedTests"
      },
      totalSpeedtestTesters: {
        "$size": {
          "$setDifference": [
            "$speedTests.employee",
            []
          ]
        }
      }
    }
  },

])

输出：

[
  {
    "averageDownloadSpeed": 7.66,
    "averageSignalStrength": -180,
    "averageUploadSpeed": 4.22,
    "store": "5dc25092d972e71c3b3e6e87",
    "storeName": "Chad store",
    "totalSpeedTests": 2,
    "totalSpeedtestTesters": 1
  },
  {
    "averageDownloadSpeed": 0,
    "averageSignalStrength": -80,
    "averageUploadSpeed": 0,
    "store": "5dc2899bfd1ea02f0fceb9ab",
    "storeName": "Bad store",
    "totalSpeedTests": 0,
    "totalSpeedtestTesters": 0
  }
]

游乐场：

https://mongoplayground.net/p/MwJ26hAx996

Answer 2

您可以使用$lookup。在MongoDB中获取MongoDB值有点困难。我想你需要独特的价值。我建议在客户端制作。

db.getCollection('Store').aggregate([
    {$lookup:
           {
             from: "Signal",
             localField: "id",
             foreignField: "store" ,
             as: "signals"
           }
    }
    ,{$lookup:
           {
             from: "Speed",
             localField: "id",
             foreignField: "store" ,
             as: "speeds"
           }
    }
    ,{$project :{ 
        store: "$id", 
        storeName: "$name",
        averageSignalStrength:  {
          $avg: "$signals.signalStrength"
        }
        ,averageUploadSpeed: {
          $avg: "$speeds.uploadSpeed"
        }
        ,averageDownloadSpeed:{
          $avg: "$speeds.downloadSpeed"
        }
        ,totalSpeedTests : {
          $size: "$speeds"
        }
        ,totalSpeedtestTesters:"$speeds.employee"
        }
     }
])