CODEWITHSUNDEEP
mongodb
Prajwal
Prajwal

Reputation: 419

Need to join 2 collection for mongodb to find the aggregated data based on collection field

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 :)

Upvotes: 0

Views: 55

Answers (1)

SuleymanSah
SuleymanSah

Reputation: 17858

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

Upvotes: 2

Related Questions