Mongoose 聚合查询，用于从 4 个按 Id 和按操作分组的集合中获取数据 [英] Mongoose Aggregate Query For Getting Data From 4 Collection By Id and Group By Action
本文介绍了Mongoose 聚合查询，用于从 4 个按 Id 和按操作分组的集合中获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有 3 个收藏
const userSchema = new mongoose.Schema ({用户名 : {类型:字符串，修剪:真实},名称 : {类型:字符串，修剪:真实},头像:{类型:字符串}最后一次露面: {类型:日期，默认值:Date.now},地位: {类型:布尔值，默认值:真}})const hsVideoSchema = new mongoose.Schema ({名称 : {类型:字符串，修剪:真实，要求:真},网址:{类型:字符串，修剪:真实，要求:真},上传者:{类型:mongoose.Schema.Types.ObjectId，参考:'用户'},地位: {类型:布尔值，默认值:真}})const fsVideoSchema = new mongoose.Schema ({名称 : {类型:字符串，修剪:真实，要求:真},网址:{类型:字符串，修剪:真实，要求:真},上传者:{类型:mongoose.Schema.Types.ObjectId，参考:'用户'},地位: {类型:布尔值，默认值:真}})现在，为了保留用户的操作历史，我创建了如下的历史模型:
const historySchema = new mongoose.Schema ({用户身份 : {类型:mongoose.Schema.Types.ObjectId，参考:'用户'},hs_videoId :{类型:mongoose.Schema.Types.ObjectId，ref: 'HsVideo',默认值:空},fs_videoId :{类型:mongoose.Schema.Types.ObjectId，ref: 'FsVideo',默认值:空},行动 : {类型:字符串，修剪:真实，枚举:['下载'，'查看'，'喜欢'，'报告']}})因此，当用户执行任何操作时，我将在历史记录集合中添加新记录.一次，在 2 个字段 (hs_videId & fs_videoId) 中，一个字段将为 null，另一个字段将具有 ref 文档的 id.在历史集合中，可以有相同的 hs_videId/fs_videId 具有不同的动作('downloaded'、'viewed'、'liked'、'reported').
我正在寻找通过传递 user_id 来获取用户历史记录的查询，并使用 2 个子数组获取所有视频历史记录数组:HsVideos 和 FsVideos.两个子数组都应该有动作的子数组，它将包含视频的完整详细信息(名称，url，uploadedBy(UserArray)，状态).
我应该写什么查询来得到想要的结果?
我已经尝试过的查询:
User.aggregate([{ $match: {_id : ObjectId('5f3a90110132e115db700201')} },{$查找:{来自:历史"，如:历史"，管道:[{$匹配:{user_id : ObjectId('5f3a90110132e115db700201')}}]},},]).exec(函数(错误，结果){if(err) console.log(err);返回 res.status(200).json(results);})请帮忙！任何帮助将不胜感激.谢谢.
1
我期待以下结果:
<预><代码>[{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),姓名":约翰"，状态":真，用户名":jony"FsVideos":[{看过:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],已下载:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],喜欢:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],报道:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],}],HsVideos":[{看过:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],已下载:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],喜欢:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},},{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],报道:[{_id":ObjectId(5a934e000102030405000001")，网址":http://example.com/video.mp4"，上传者":{_id":ObjectId(5f3a90110132e115db700201")，头像":default.png"，last_seen":ISODate(1970-01-01T00:00:00Z"),名称":A"，状态":真，用户名":A"},}],}]}] 解决方案
这完全是一个糟糕的schema结构，你真的需要更新它，因为这会导致内存使用和查询的执行时间，
您需要将查找与管道和嵌套查找结合使用，
$matchuser 你的条件$lookupwithhistories集合和结果将在HsVideos$match获取user_id的$lookup用于hsVideo集合和结果将在hs_videoId$match获取hsVideo详情$lookup带有updatedBy的用户集合，结果将在updatedBy$unwind解构结果updatedBy将是对象$unwind解构hs_videoId因为它的数组和我们需要的对象$group通过action并将必填字段推送到v数组$project显示和隐藏必填字段
historyUser.aggregate([{ $match: { _id: ObjectId("5f3a90110132e115db700201") } },{$查找:{来自:历史"，让:{ user_id:$_id"；},如:HsVideos"，管道:[{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },{$查找:{来自:hsVideo"，让:{ hs_videoId:$hs_videoId"；},如:hs_videoId"，管道:[{ $match: { $expr: { $eq: ["$$hs_videoId", "$_id"] } } },{$查找:{来自:用户"，本地字段:uploadedBy"，外国字段:_id"，如:上传者"}},{ $unwind: "$uploadedBy";}]}},{ $unwind: "$hs_videoId";},{$组:{_id: "$action",v:{$推:{_id: "$_id",网址:$hs_videoId.url"，名称:$hs_videoId.name"，上传者:$hs_videoId.uploadedBy"；}}}},{ $project: { _id: 0, k: "$_id", v: 1 } }]}},- 我在重复上面几行的解释，重复
FsVideos中
HsVideos的相同流程<代码> {$查找:{来自:历史"，让:{ user_id:$_id"；},如:FsVideos"，管道:[{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },{$查找:{来自:fsVideo"，让:{ fs_videoId:$fs_videoId"；},如:fs_videoId"，管道:[{ $match: { $expr: { $eq: ["$$fs_videoId", "$_id"] } } },{$查找:{来自:用户"，本地字段:uploadedBy"，外国字段:_id"，如:上传者"}},{ $unwind: "$uploadedBy";}]}},{ $unwind: "$fs_videoId";},{$组:{_id: "$action",v:{$推:{_id: "$_id",网址:$fs_videoId.url"，名称:$fs_videoId.name"，上传者:$fs_videoId.uploadedBy"；}}}},{ $project: { _id: 0, k: "$_id", v: 1 } }]}},- $addFields 将
FsVideos和HsVideos数组转换为对象，action作为 key 和 vlaue
<代码> {$addFields:{FsVideos: { $arrayToObject: "$FsVideos";},HsVideos: { $arrayToObject: "$HsVideos";}}}]).exec(函数(错误，结果){if(err) console.log(err);返回 res.status(200).json(results);})I have 3 collections
const userSchema = new mongoose.Schema ({
username : {
type : String,
trim: true
},
name : {
type : String,
trim: true
},
avatar : {
type : String
}
last_seen: {
type: Date,
default: Date.now
},
status: {
type : Boolean,
default: true
}
})
const hsVideoSchema = new mongoose.Schema ({
name : {
type : String,
trim: true,
required : true
},
url : {
type : String,
trim: true,
required : true
},
uploadedBy: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
status: {
type : Boolean,
default: true
}
})
const fsVideoSchema = new mongoose.Schema ({
name : {
type : String,
trim: true,
required : true
},
url : {
type : String,
trim: true,
required : true
},
uploadedBy: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
status: {
type : Boolean,
default: true
}
})
Now, to keep user's action history, i created History Model as below :
const historySchema = new mongoose.Schema ({
user_id : {
type: mongoose.Schema.Types.ObjectId,
ref: 'User'
},
hs_videoId : {
type: mongoose.Schema.Types.ObjectId,
ref: 'HsVideo',
default: null
},
fs_videoId : {
type: mongoose.Schema.Types.ObjectId,
ref: 'FsVideo',
default: null
},
action : {
type : String,
trim: true,
enum:['downloaded','viewed','liked','reported']
}
})
So, i will add new record in history collection when user will perform any action. At a time, out of 2 fields (hs_videId & fs_videoId) one field will be null and another will have id of ref document. In history collection, there can be same hs_videId/fs_videId with different action ('downloaded','viewed','liked','reported').
I am looking for query to get user's history by passing user_id and get all video history array with 2 sub arrays : HsVideos and FsVideos. Both sub array should have action's sub-array, which will have complete details of video (name, url,uploadedBy(UserArray),status).
What query i should write to get desire result ?
Query i tried already :
User.aggregate([
{ $match: {_id : ObjectId('5f3a90110132e115db700201')} },
{
$lookup: {
from: "histories",
as: "history",
pipeline: [
{
$match: {
user_id : ObjectId('5f3a90110132e115db700201')
}
}
]
},
},
]).exec(function(err, results){
if(err) console.log(err);
return res.status(200).json(results);
})
Please help ! Any help will be appreciated. Thanks.
EDIT : 1
I am expecting below result :
[
{
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "John",
"status": true,
"username": "jony"
"FsVideos": [
{
Viewed :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Downloaded :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Liked :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Reported :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
}
],
"HsVideos": [
{
Viewed :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Downloaded :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Liked :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
},
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
Reported :[
{
"_id": ObjectId("5a934e000102030405000001"),
"url": "http://example.com/video.mp4",
"uploadedBy": {
"_id": ObjectId("5f3a90110132e115db700201"),
"avatar": "default.png",
"last_seen": ISODate("1970-01-01T00:00:00Z"),
"name": "A",
"status": true,
"username": "A"
},
}
],
}
]
}
]
解决方案
This is totally a bad structure of schema, you really need to update it, because this will cause memory usage and execution time of the query,
You need to use lookup with pipeline and nested lookup,
$matchyour conditions foruser$lookupwithhistoriescollection and result will be inHsVideos$matchgethistoriesofuser_id$lookupforhsVideocollection and result will be inhs_videoId$matchgethsVideodetails$lookupwith user collection forupdatedByand result will be inupdatedBy$unwinddeconstruct resultupdatedByand it will be object$unwinddeconstrucths_videoIdbecause its array and we need object$groupbyactionand push required fields invarray$projectto show and hide required fields
User.aggregate([
{ $match: { _id: ObjectId("5f3a90110132e115db700201") } },
{
$lookup: {
from: "histories",
let: { user_id: "$_id" },
as: "HsVideos",
pipeline: [
{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },
{
$lookup: {
from: "hsVideo",
let: { hs_videoId: "$hs_videoId" },
as: "hs_videoId",
pipeline: [
{ $match: { $expr: { $eq: ["$$hs_videoId", "$_id"] } } },
{
$lookup: {
from: "user",
localField: "uploadedBy",
foreignField: "_id",
as: "uploadedBy"
}
},
{ $unwind: "$uploadedBy" }
]
}
},
{ $unwind: "$hs_videoId" },
{
$group: {
_id: "$action",
v: {
$push: {
_id: "$_id",
url: "$hs_videoId.url",
name: "$hs_videoId.name",
uploadedBy: "$hs_videoId.uploadedBy"
}
}
}
},
{ $project: { _id: 0, k: "$_id", v: 1 } }
]
}
},
- i am repeating the above lines of explanation, repeat the same flow of
HsVideosinFsVideos
{
$lookup: {
from: "histories",
let: { user_id: "$_id" },
as: "FsVideos",
pipeline: [
{ $match: { $expr: { $eq: ["$user_id", "$$user_id"] } } },
{
$lookup: {
from: "fsVideo",
let: { fs_videoId: "$fs_videoId" },
as: "fs_videoId",
pipeline: [
{ $match: { $expr: { $eq: ["$$fs_videoId", "$_id"] } } },
{
$lookup: {
from: "user",
localField: "uploadedBy",
foreignField: "_id",
as: "uploadedBy"
}
},
{ $unwind: "$uploadedBy" }
]
}
},
{ $unwind: "$fs_videoId" },
{
$group: {
_id: "$action",
v: {
$push: {
_id: "$_id",
url: "$fs_videoId.url",
name: "$fs_videoId.name",
uploadedBy: "$fs_videoId.uploadedBy"
}
}
}
},
{ $project: { _id: 0, k: "$_id", v: 1 } }
]
}
},
- $addFields to convert
FsVideosandHsVideosarray to object,actionas key and vlaue
{
$addFields: {
FsVideos: { $arrayToObject: "$FsVideos" },
HsVideos: { $arrayToObject: "$HsVideos" }
}
}
])
.exec(function(err, results){
if(err) console.log(err);
return res.status(200).json(results);
})
这篇关于Mongoose 聚合查询，用于从 4 个按 Id 和按操作分组的集合中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
