db={"users":[{"_id":8,"name":"Full Name","email":"help@example.com","password":"password hash"},{"_id":9,"name":"Sample Name","email":"email@example.com","password":"password hash"}],"pages":[{"_id":42,"name":"Product Name","description":"Product Description","user_id":8,"rating_categories":[{"_id":114,"name":"Build Quality"},{"_id":115,"name":"Price"},{"_id":116,"name":"Feature"},{"_id":117,"name":"Comfort"},{"_id":118,"name":"Switch"}]}],"reviews":[{"_id":"9","page_id":42,"user_id":8,"review":"The review of the product","ratings":[{"_id":114,"rating":5},{"_id":115,"rating":4},{"_id":116,"rating":5},{"_id":117,"rating":3},{"_id":118,"rating":4}],"created":"1582825968963","votes":{"downvotes":[9],"upvotes":[8]}},{"_id":"10","page_id":42,"user_id":8,"review":"The review of the product","ratings":[{"_id":114,"rating":5},{"_id":115,"rating":4},{"_id":116,"rating":5},{"_id":117,"rating":3},{"_id":118,"rating":4}],"created":"1582825968963","votes":{"downvotes":[9],"upvotes":[8]}},{"_id":"11","page_id":42,"user_id":9,"review":"The review of the product","ratings":[{"_id":114,"rating":5},{"_id":115,"rating":4},{"_id":116,"rating":5},{"_id":117,"rating":3},{"_id":118,"rating":4}],"created":"1582825968964","votes":{"downvotes":[8],"upvotes":[9,8]}}]}

db.reviews.aggregate([{$match:{page_id:42}},{$addFields:{request_user_id:9}},{$group:{_id:{user_id:"$user_id",page_id:"$page_id",request_user_id:"$request_user_id"},data:{$push:"$$ROOT"}}},{$lookup:{"from":"users","let":{root_user_id:"$_id.user_id"},"pipeline":[{$match:{$expr:{$eq:["$$root_user_id","$_id"]}}},{$lookup:{"from":"reviews","let":{root_user_id:"$$root_user_id"},"pipeline":[{$match:{$expr:{$eq:["$$root_user_id","$user_id"]}}},{$project:{user_id:1,downvote_count:{$size:"$votes.downvotes"},upvote_count:{$size:"$votes.upvotes"}}},{$group:{_id:null,review_count:{$sum:{$cond:[{$eq:["$$root_user_id","$user_id"]},1,0]}},upvote_count:{$sum:"$upvote_count"},downvote_count:{$sum:"$downvote_count"}}},{$unset:"_id"}],"as":"stats"}},{$project:{tmp:{$mergeObjects:[{_id:"$_id",name:"$name"},{$arrayElemAt:["$stats",0]}]}}},{$replaceWith:"$tmp"}],"as":"user"}},{$addFields:{first:{$mergeObjects:["$$ROOT",{$arrayElemAt:["$data",0]},{user:{$arrayElemAt:["$user",0]},created:{$toDate:{$toLong:{$arrayElemAt:["$data.created",0]}}},downvote_count:{$reduce:{input:"$data.votes.downvotes",initialValue:0,in:{$add:["$$value",{$size:"$$this"}]}}},upvote_count:{$reduce:{input:"$data.votes.upvotes",initialValue:0,in:{$add:["$$value",{$size:"$$this"}]}}},vote_state:{$cond:[{$gt:[{$size:{$filter:{input:"$data.votes.upvotes",cond:{$in:["$_id.request_user_id","$$this"]}}}},0]},1,{$cond:[{$gt:[{$size:{$filter:{input:"$data.votes.downvotes",cond:{$in:["$_id.request_user_id","$$this"]}}}},0]},-1,0]}]}}]}}},{$unset:["first.data","first.votes","first.user_id","first.request_user_id"]},{$replaceWith:"$first"},{"$sort":{"created":-1}}])