需要为mongodb加入2个集合，根据集合字段查找聚合数据 [英] Need to join 2 collection for mongodb to find the aggregated data based on collection field

问题描述

我有商店列表作为商店集合，其中只有商店基本信息

商店收藏:[{"id": "5dc25092d972e71c3b3e6e87","name": "乍得商店",},{"id": "5dc2899bfd1ea02f0fceb9ab","name": "坏店",}]

我有信号记录集合，其中包含每个商店的多个信号记录.信号记录就像没有.信号条，信号强度等 很少有商店可能没有信号记录.

 信号记录:[{"id": "5dc250e0d972e71c3b3e6e88",信号强度":-180，"信号条": "3","员工": "5db59227f0204855654075ee",商店":5dc25092d972e71c3b3e6e87"，"carrierName": "LT&T"},{"id": "5dc251f723760a24de167f8e",信号强度":-180，"信号条": "3","员工": "5db59227f0204855654075ee",商店":5dc25092d972e71c3b3e6e87"，"carrierName": "Sprint"},{"id": "5dc289affd1ea02f0fceb9ac",信号强度":-80，"信号条": "3","员工": "5db59227f0204855654075ee",商店":5dc2899bfd1ea02f0fceb9ab"，"carrierName": "LT&T"}]

速度测试:[{"id": "5dc2c170e26c4a484051f4af","carrierName": "Sprint"平":10，下载速度":7.66，上传速度":4.22，"员工": "5dc2b9467a56f3446dcaf8f6",商店":5dc25092d972e71c3b3e6e87"},{"id": "5dc3a0a92588214e1a938a34","carrierName": "LT&T",平":10，下载速度":7.66，上传速度":4.22，"员工": "5dc2b9467a56f3446dcaf8f6",商店":5dc25092d972e71c3b3e6e87"}]

我需要根据运营商名称列出商店及其信号强度.

预期输出:

预期输出:[{商店":5dc25092d972e71c3b3e6e87"，"storeName": "乍得商店","carrierName": "LT&T",平均信号强度":-180，平均上传速度":7.66，平均下载速度":4.22，"totalSpeedTests": 2,totalSpeedtestTesters":1},{商店":5dc25092d972e71c3b3e6e87"，"storeName": "乍得商店","carrierName": "Sprint",平均信号强度":-180，平均上传速度":7.66，平均下载速度":4.22，"totalSpeedTests": 2,totalSpeedtestTesters":1},{商店":5dc2899bfd1ea02f0fceb9ab"，"storeName": "坏店","carrierName": "LT&T",平均信号强度":-80，平均上传速度":0，平均下载速度":0，"totalSpeedTests": 0,totalSpeedtestTesters":0}]

任何帮助都会很棒:)

解决方案

我使用了以下聚合.

$lookup 加入信号和速度测试.

$unwind 解构 storeSignals 数组字段.

$replaceRoot 和 $mergeRoot 来塑造你想要的输出.

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

$setDifference 计算不同的值.

您可以使用以下聚合:

db.store.aggregate([{$查找:{来自:信号"，本地字段:id"，外国字段:商店"，如:商店信号"}},{$查找:{来自:速度测试"，本地字段:id"，外国字段:商店"，如:速度测试"}},{$展开:{路径:$storeSignals"，preserveNullAndEmptyArrays: 真}},{$替换根:{新根:{$合并对象:["$storeSignals",{商店":$_id"，"storeName": "$name","carrierName": "$carrierName","speedTests": "$speedTests","信号强度": "$信号强度",},]}}},{$项目:{_id: 0,商店:$商店"，storeName: "$storeName",承运人名称: "$carrierName",平均信号强度:{$ifNull: [{$avg: "$信号强度"},0]},平均上传速度:{$ifNull: [{$avg: "$speedTests.uploadSpeed"},0]},平均下载速度:{$ifNull: [{$avg: "$speedTests.downloadSpeed"},0]},总速度测试:{$size: "$speedTests"},totalSpeedtestTesters:{$ 大小":{$setDifference":["$speedTests.employee",[]]}}}},])

输出如下:

<预><代码>[{平均下载速度":7.66，平均信号强度":-180，平均上传速度":4.22，"carrierName": "LT\u0026T",商店":ObjectId(5a934e000102030405000005")，"storeName": "乍得商店","totalSpeedTests": 2,totalSpeedtestTesters":1},{平均下载速度":7.66，平均信号强度":-180，平均上传速度":4.22，"carrierName": "Sprint",商店":ObjectId(5a934e000102030405000005")，"storeName": "乍得商店","totalSpeedTests": 2,totalSpeedtestTesters":1},{平均下载速度":0，平均信号强度":-80，平均上传速度":0，"carrierName": "LT\u0026T",商店":ObjectId(5a934e000102030405000006")，"storeName": "坏店","totalSpeedTests": 0,totalSpeedtestTesters":0}]

Playground 测试它是否工作:

https://mongoplayground.net/p/WTDPKEKGS1F

I have store list as store collection which has only store basic details

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

I have signal-records collection which has the multiple signals records for each store. signal records are like no. of signal bars, signal strength etc Few stores may not have signal records.

 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"
  }
]

I need to list stores and its signal strength based on its carrier names.

Expected output:

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
  }
]

Any help would be great :)

解决方案

I used the following aggregations.

$lookup to join signals and speedTests.

$unwind to deconstruct storeSignals array field.

$replaceRoot and $mergeRoot to shape the output you wanted.

$ifNull to handle null averages as 0.

$setDifference to count distinct values.

You can use the following aggregation:

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

])

Output will be like this:

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

Playground to test if it is working:

https://mongoplayground.net/p/WTDPKEKGS1F

这篇关于需要为mongodb加入2个集合，根据集合字段查找聚合数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！