需要连接mongodb的2集合，以便根据集合字段查找聚合数据

Question

我有存储列表作为商店收藏，它只存储基本的细节。

​

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",
    "signalStrength": -180,
    "signalbars": "3",
    "employee": "5db59227f0204855654075ee",
    "store": "5dc25092d972e71c3b3e6e87",
    "carrierName": "Sprint"
  },
  {
    "id": "5dc289affd1ea02f0fceb9ac",
    "signalStrength": -80,
    "signalbars": "3",
    "employee": "5db59227f0204855654075ee",
    "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,
    "downloadSpeed": 7.66,
    "uploadSpeed": 4.22,
    "employee": "5dc2b9467a56f3446dcaf8f6",
    "store": "5dc25092d972e71c3b3e6e87"
  }
]

​

我需要列出商店和它的信号强度根据它的载体名称。

预期产出：

​

Expected output:

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

​

任何帮助都是很好的:)

Answer 1

我使用了以下聚合。

$lookup连接信号和speedTests。

$unwind解构storeSignals数组字段。

$replaceRoot和$mergeRoot来塑造您想要的输出。

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

$setDifference用于计数不同的值。

您可以使用以下聚合：

db.store.aggregate([
  {
    $lookup: {
      from: "signals",
      localField: "id",
      foreignField: "store",
      as: "storeSignals"
    }
  },
  {
    $lookup: {
      from: "speedTests",
      localField: "id",
      foreignField: "store",
      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"
          },
          0
        ]
      },
      averageDownloadSpeed: {
        $ifNull: [
          {
            $avg: "$speedTests.downloadSpeed"
          },
          0
        ]
      },
      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,
    "averageSignalStrength": -180,
    "averageUploadSpeed": 4.22,
    "carrierName": "Sprint",
    "store": ObjectId("5a934e000102030405000005"),
    "storeName": "Chad store",
    "totalSpeedTests": 2,
    "totalSpeedtestTesters": 1
  },
  {
    "averageDownloadSpeed": 0,
    "averageSignalStrength": -80,
    "averageUploadSpeed": 0,
    "carrierName": "LT\u0026T",
    "store": ObjectId("5a934e000102030405000006"),
    "storeName": "Bad store",
    "totalSpeedTests": 0,
    "totalSpeedtestTesters": 0
  }
]

操场测试它是否工作：

https://mongoplayground.net/p/WTDPKEKGS1F