如何在mongodb中找到引用模式字段的avg

Question

我有这样设计的模式

const MerchantSchema = new mongoose.Schema({
mobile: {
type: Number,
required: true,
unique: true
},
user_type:{
type:String,
enum:['merchant'],
required: true
},
isVerified:{
type:Boolean,
default:()=>false
},
token:String,
location:{
type:{
type:String,
enum:['Point'],
default:()=>'Point'
},
coordinates:{
type:[Number],
required:true
}
},
images:[String],
reviews:[{
type:mongoose.Schema.Types.ObjectId,
ref:'reviews'
}],
});
MerchantSchema
.method("toJSON", function() {
const { __v, _id, ...object } = this.toObject();
object.id = _id;
return object;
});
MerchantSchema.index({location:'2dsphere'});
module.exports = mongoose.model('merchants',MerchantSchema);

审查模式

const mongoose = require('mongoose');
const ReviewSchema = new mongoose.Schema({
rating:{
type:Number,
required:true,
default:0
},
message: {
type: String,       
required: true
},
owner_id:{
type:mongoose.Schema.Types.ObjectId,
ref:'merchants'
},
posted_by:{
type:mongoose.Schema.Types.ObjectId,
ref:'users',},
});
ReviewSchema
.method("toJSON", function() {
const { __v, _id, ...object } = this.toObject();
object.id = _id;
return object;
}); 
module.exports = mongoose.model('reviews', ReviewSchema);

我想根据地理位置和一些条件得到结果并且我想要结果集中的avgRating的额外字段。我正在尝试以下查询

try {

const merchants = await Merchant.aggregate([
// match merchant lat lng in radius
{
$geoNear: {
near: {
type: "Point",
coordinates: [
parseFloat(req.query.long),
parseFloat(req.query.lat)
]
},
distanceField: "distance",
maxDistance: Number(req.query.distance),
spherical: true
},
},
{ $match: { isActive: { $eq: true } } },
// {
//     $unwind: {
//       path: "$reviews"
//     }
//   },
{
$addFields: {
reviewCount: {
$size: "$reviews"
},
avgRating: {
$avg: "$reviews.rating",
}
}
},
// uncoment this to get all data except the reviews
{
$project: {
reviews: 0,
}
},
// uncomment this to get all the reviews withth slected fields
// if selected fields are not required then comment let section 
// and pipeline section and uncomment local fiels and foreign fields
// {
//     $lookup: {
//         from: "reviews",
//          let: { owner_id: "$_id" },
//          pipeline: [ 
//              {$match: { $expr: { $eq: [ "$owner_id", "$$owner_id" ] } } },
//              { $project: { message: 1, posted_by: 1, _id: 0,rating:1 } } 
//          ],
//         as: "reviews",

//     }
// },

]);
return (merchants.length>0)?
apiResponse.successResponseWithData(res,"Success",merchants) :
apiResponse.ErrorResponse(res,{message:"No merchants found"});


} catch (error) {
return apiResponse.ErrorResponse(res, error.message || "Some error occurred while getting merchants.");
}

我得到的输出是

{
"status": 1,
"message": "Success",
"data": [
{
"_id": "6223400356a5f3404b57a273",
"mobile": 9999999999,
"user_type": "merchant",
"location": {
"type": "Point",
"coordinates": [
25.75679373274295,
86.02907103277855
]
},
"images": [],
"isVerified": true,
"__v": 0,
"reviewCount": 2,
"facilityCount": 2,
"avgRating": null
}
]
}

预期输出

{
"status": 1,
"message": "Success",
"data": [
{
"_id": "6223400356a5f3404b57a273",
"mobile": 9999999999,
"user_type": "merchant",
"location": {
"type": "Point",
"coordinates": [
25.75679373274295,
86.02907103277855
]
},
"images": [],
"isVerified": true,
"__v": 0,
"reviewCount": 2,
"facilityCount": 2,
"avgRating": 3.0
}
]
}

获得预期产出的可能性有多大。将高度赞赏任何建议

Answer 1

您的代码对我来说很好。我在自己的数据库中尝试过。以下是示例。

db.Merchant.aggregate([
{
$geoNear: {
near: {
type: "Point",
coordinates: [
25.75679373274295,
86.02907103277855
]
},
distanceField: "distance",
maxDistance: 10,
spherical: true
}
},
{
$match: {
isActive: {
$eq: true
}
}
},
{
$lookup: {
from: "reviews",
let: {
owner_id: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$owner_id",
"$$owner_id"
]
}
}
},
{
$project: {
message: 1,
posted_by: 1,
_id: 0,
rating: 1
}
}
],
as: "reviews"
}
},
{
$addFields: {
reviewCount: {
$size: "$reviews"
},
avgRating: {
$avg: "$reviews.rating"
}
}
},
{
$project: {
reviews: 0
}
}
])

mongoplayground