javascript - 在Node.js中优化组合MongoDB查询

我在stations集合中存储了十个电台：Station A，Station B，Station C，Station D，Station E，Station F，Station G，Station H，Station I，。

现在，要创建所有可能的站点对之间的所有站点间行驶的计数列表，我在Node.js代码中执行以下操作（使用Mongoose）：

const stationCombinations = []

// get all stations from the stations collection
const stationIds = await Station.find({}, '_id name').lean().exec()

// list of all possible from & to combinations with their names
stationIds.forEach(fromStation => {
  stationIds.forEach(toStation => {
    stationCombinations.push({ fromStation, toStation })
  })
})

const results = []

// loop through all station combinations
for (const stationCombination of stationCombinations) {
  // create aggregation query promise
  const data = Ride.aggregate([
    {
      $match: {
        test: false,
        state: 'completed',
        duration: { $gt: 2 },
        fromStation: mongoose.Types.ObjectId(stationCombination.fromStation._id),
        toStation: mongoose.Types.ObjectId(stationCombination.toStation._id)
       }
    },
    {
      $group: {
        _id: null,
        count: { $sum: 1 }
      }
    },
    {
      $addFields: {
        fromStation: stationCombination.fromStation.name,
        toStation: stationCombination.toStation.name
      }
    }
  ])

  // push promise to array
  results.push(data)
}

// run all aggregation queries
const stationData = await Promise.all(results)

// flatten nested/empty arrays and return
return stationData.flat()

执行此功能可以得到以下格式的结果：

[
  {
    "fromStation": "Station A",
    "toStation": "Station A",
    "count": 1196
  },
  {
    "fromStation": "Station A",
    "toStation": "Station B",
    "count": 1
  },
  {
    "fromStation": "Station A",
    "toStation": "Station C",
    "count": 173
  },
]

And so on for all other combinations...

该查询当前需要花费很多时间来执行，并且由于这些查询，我不断从MongoDB Atlas收到有关数据库服务器上过多负载的警报。当然必须有一种优化的方法来做这样的事情吗？

最佳答案

您需要使用MongoDB本机操作。您需要$group通过fromStation和toStation并与$lookup联接两个集合。

注意：我假设您有MongoDB> = v3.6，并且Station._id是ObjectId

db.ride.aggregate([
  {
    $match: {
      test: false,
      state: "completed",
      duration: {
        $gt: 2
      }
    }
  },
  {
    $group: {
      _id: {
        fromStation: "$fromStation",
        toStation: "$toStation"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $lookup: {
      from: "station",
      let: {
        fromStation: "$_id.fromStation",
        toStation: "$_id.toStation"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                [
                  "$$fromStation",
                  "$$toStation"
                ]
              ]
            }
          }
        }
      ],
      as: "tmp"
    }
  },
  {
    $project: {
      _id: 0,
      fromStation: {
        $reduce: {
          input: "$tmp",
          initialValue: "",
          in: {
            $cond: [
              {
                $eq: [
                  "$_id.fromStation",
                  "$$this._id"
                ]
              },
              "$$this.name",
              "$$value"
            ]
          }
        }
      },
      toStation: {
        $reduce: {
          input: "$tmp",
          initialValue: "",
          in: {
            $cond: [
              {
                $eq: [
                  "$_id.toStation",
                  "$$this._id"
                ]
              },
              "$$this.name",
              "$$value"
            ]
          }
        }
      },
      count: 1
    }
  },
  {
    $sort: {
      fromStation: 1,
      toStation: 1
    }
  }
])

MongoPlayground

未经测试：

const data = Ride.aggregate([
  {
     $match: {
       test: false,
       state: 'completed',
       duration: { $gt: 2 }
     }
  },
  {
    $group: {
      _id: {
        fromStation: "$fromStation",
        toStation: "$toStation"
      },
      count: { $sum: 1 }
    }
  },
  {
    $lookup: {
      from: "station",
      let: {
        fromStation: "$_id.fromStation",
        toStation: "$_id.toStation"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                [
                  "$$fromStation",
                  "$$toStation"
                ]
              ]
            }
          }
        }
      ],
      as: "tmp"
    }
  },
  {
    $project: {
      _id: 0,
      fromStation: {
        $reduce: {
          input: "$tmp",
          initialValue: "",
          in: {
            $cond: [
              {
                $eq: [
                  "$_id.fromStation",
                  "$$this._id"
                ]
              },
              "$$this.name",
              "$$value"
            ]
          }
        }
      },
      toStation: {
        $reduce: {
          input: "$tmp",
          initialValue: "",
          in: {
            $cond: [
              {
                $eq: [
                  "$_id.toStation",
                  "$$this._id"
                ]
              },
              "$$this.name",
              "$$value"
            ]
          }
        }
      },
      count: 1
    }
  },
  {
    $sort: {
      fromStation: 1,
      toStation: 1
    }
  }
])

关于javascript - 在Node.js中优化组合MongoDB查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/60237301/