前端之家

  1. 首页
  2. 问答

需要加入2个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","carrierName": "LT&T"
  },{
    "id": "5dc251f723760a24de167f8e","carrierName": "Sprint"
  },{
    "id": "5dc289affd1ea02f0fceb9ac","signalStrength": -80,"store": "5dc2899bfd1ea02f0fceb9ab","carrierName": "LT&T"
  }
]

Speed Tests:

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

我需要根据存储公司的名称列出存储及其信号强度。

预期输出：

Expected output:

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

任何帮助都会很棒：）

xiaonv5835335 回答：需要加入2个mongodb集合才能根据集合字段查找聚合数据

我使用了以下汇总。

$ lookup加入信号和speedTests。

$ unwind解构storeSignals数组字段。

$ replaceRoot和$ mergeRoot来调整所需的输出。

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

$ setDifference计数不同的值。

您可以使用以下聚合：

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

输出将如下所示：

[
  {
    "averageDownloadSpeed": 7.66,"averageSignalStrength": -180,"averageUploadSpeed": 4.22,"carrierName": "LT\u0026T","store": ObjectId("5a934e000102030405000005"),"storeName": "Chad store","totalSpeedTests": 2,"totalSpeedtestTesters": 1
  },{
    "averageDownloadSpeed": 7.66,"carrierName": "Sprint",{
    "averageDownloadSpeed": 0,"averageSignalStrength": -80,"averageUploadSpeed": 0,"store": ObjectId("5a934e000102030405000006"),"storeName": "Bad store","totalSpeedTests": 0,"totalSpeedtestTesters": 0
  }
]

测试游戏是否正常的操场：

https://mongoplayground.net/p/WTDPKEKGS1F

mongodb
本文链接：https://www.f2er.com/3145981.html

大家都在问