mongodb aggregate （聚合查询）联表 node nest.js monoose .js实现
联表使用的场景
mongodb的curd（增删改查）操作比较简单，但是开发者在开发过程中肯定是不够用的，如果遇到很复杂的查询操作，只查询单个表（mongodb中的集合，本人习惯称为表，以下不在赘述）是不能满足业务需求的，所以可能会连接外部表，或者查询本表之后经过分组，转化之后的临时表。或者连接外部表格链接后产生的临时表。以上这些case（时候）将会使用到本教程。
废话不多说，上代码
1. 要查询的结果是这样式的
代码语言：ts复制//mongodb 连表后查询文档结构
{
"_id": {
"$oid": "65d2d0c0434057a3419404be"
},
"token": "6f9d245c-8b78-49fe-81f2-3ec71a6dc121",
"queueId": "65d2cefd8947df31245a4599",
"queue": {
"id": "65d2cefd8947df31245a4599",
"branchCode": "12345",
"sessionId": "65d2cefd8947df31245a4569",
"session": {
"id": "65d2cefd8947df31245a4569",
"branchCode": "12345",
"nameEn": "Breakfast",
"nameTc": "早市",
"nameSc": "早市",
"onlineStart": "0800",
"onlineEnd": "1200",
"start": "0800",
"end": "1200",
"branchId": "65d2cefd8947df31245a455a",
"branch": {
"id": "65d2cefd8947df31245a455a",
"branchCode": "12345",
"branchIp": null,
"status": 1,
"onlineStatus": 0,
"createdAt": {
"$date": "2024-02-19T03:46:05.006Z"
},
"updatedAt": {
"$date": "2024-02-19T03:46:05.006Z"
},
"kioskStatus": 0,
"kioskPrinterStatus": 0,
"kioskScannerStatus": 0,
"adminStatus": 0,
"adminPrinterStatus": 0,
"adminScannerStatus": 0,
"callDisplayStatus": 0,
"offlineStatus": 0,
"branchServerStatus": 0,
"brandId": "65d2cefc8947df31245a4554",
}
},
"pplFrom": 1,
"pplTo": 4,
"prefix": "A",
"current": 0,
"last": 0,
"vipLast": 0,
"syncToCloud": 0,
},
"referenceSource": "12c3dacb",
"referenceId": "121345caaa",
"ticketNumber": "A1",
"phone": "27259654",
"type": 0,
"status": 0,
"ivrsStatus": 0,
"ivrsRetry": 0,
"blastStatus": 0,
"blastRetry": 0,
"apiVersion": 2,
"lang": "TC",
"platform": 1,
"cancelRetry": 0,
"called": 0,
"createdAt": {
"$date": "2024-02-19T03:53:36.661Z"
},
"updatedAt": {
"$date": "2024-02-19T03:53:36.661Z"
},
"syncToCloud": 0,
"tableSize": 4,
"expiryDatetime": {
"$date": "2024-02-20T03:53:36.661Z"
},
"id": "65d2d0c0434057a3419404be",
"__v": 0
}2.需要什么样的表结构呢？
表一
代码语言：ts复制//mongodb ticket 表 文档结构
{
"_id": {
"$oid": "65d2d0c0434057a3419404be"
},
"token": "6f9d245c-8b78-49fe-81f2-3ec71a6dc121",
"queueId": "65d2cefd8947df31245a4599",
"queue": {
"id": "65d2cefd8947df31245a4599",
"branchCode": "12345",
"sessionId": "65d2cefd8947df31245a4569",
"session": {
"id": "65d2cefd8947df31245a4569",
"branchCode": "12345",
"nameEn": "Breakfast",
"nameTc": "早市",
"nameSc": "早市",
"onlineStart": "0800",
"onlineEnd": "1200",
"start": "0800",
"end": "1200",
"branchId": "65d2cefd8947df31245a455a",
},
"pplFrom": 1,
"pplTo": 4,
"prefix": "A",
"current": 0,
"last": 0,
"vipLast": 0,
"syncToCloud": 0,
},
"referenceSource": "12c3dacb",
"referenceId": "121345caaa",
"ticketNumber": "A1",
"phone": "27259654",
"type": 0,
"status": 0,
"ivrsStatus": 0,
"ivrsRetry": 0,
"blastStatus": 0,
"blastRetry": 0,
"apiVersion": 2,
"lang": "TC",
"platform": 1,
"cancelRetry": 0,
"called": 0,
"createdAt": {
"$date": "2024-02-19T03:53:36.661Z"
},
"updatedAt": {
"$date": "2024-02-19T03:53:36.661Z"
},
"syncToCloud": 0,
"tableSize": 4,
"expiryDatetime": {
"$date": "2024-02-20T03:53:36.661Z"
},
"id": "65d2d0c0434057a3419404be",
"__v": 0
}表二
代码语言：ts复制//mongodb branch 表 文档结构
{
"_id": {
"$oid": "65d2cefd8947df31245a455a"
},
"id": "65d2cefd8947df31245a455a",
"branchCode": "12345",
"branchIp": null,
"status": 1,
"onlineStatus": 0,
"createdAt": {
"$date": "2024-02-19T03:46:05.006Z"
},
"updatedAt": {
"$date": "2024-02-19T03:46:05.006Z"
},
"kioskStatus": 0,
"kioskPrinterStatus": 0,
"kioskScannerStatus": 0,
"adminStatus": 0,
"adminPrinterStatus": 0,
"adminScannerStatus": 0,
"callDisplayStatus": 0,
"offlineStatus": 0,
"branchServerStatus": 0,
"
}2. 那我们该如何实现呢？
案例一 localField-foreignField
代码语言：javascript复制let pipeline=[
{
$lookup:{
from:'branch',//外部表、临时表的名称
localField:"queue.session.branchCode",//xxxxModel映射表的代码的字段
foreignField:"branchCode",//branc表的字段
as:'branch'//将连接的表输出为某字段
}
},
{
$project:{
"queue.session.branch":"$branch"
}
}
]
this.xxxxModel.aggregate(pipeline)//xxxModel 是mongose model案例二 pipeline 方式
代码语言：ts复制//mongodb 的aggregate 的聚合实现方式方式
// 采用的pipeline 方式，可以实现更加复杂的逻辑，比如增加分组、多次match 等方式。
let pipeline=[
{
$lookup:{
from:'branch',//外部表、临时表的名称
let: { branchCode: "$session.branchCode"},
pipeline: [ {
$match: {
$expr: {
$and: [
{ $eq: [ "$$branchCode", "$branchCode" ] },
]
}
}
} ],
as:'branch'//将连接的表输出为某字段
}
}]
this.ticketModel.aggregate(pipeline)//ticketModel 是mongose model案例二
代码语言：ts复制/mongodb 的aggregate 的聚合实现方式方式
// 注释的内容是采用的pineline 方式，可以实现更加复杂的逻辑，比如增加分组、多次match 等方式。
let pipeline=[
{
$lookup:{
from:'branch',//外部表、临时表的名称
let: { branchCode: "$session.branchCode"},
pipeline: [ {
$match: {
$expr: {
$and: [
{ $eq: [ "$$branchCode", "$branchCode" ] },
]
}
}
} ],
as:'branch'//将连接的表输出为某字段
}
},
{
$project:{
"queue.session.branch":"$branch"
}
}
]
this.ticketModel.aggregate(pipeline)//ticketModel 是mongose model案例三、连接自身
代码语言：ts复制//mongodb 连表后查询文档结构
{
"dateTime":"20-02-2024",
"onlineTickets":5,
"offlineTickets":1,
"checkedInTickets":0
}查询实现
代码语言：ts复制let =pipeline=[
{
'$lookup': {
'from': 'ticket',
'let': {
'date': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
},
'pipeline': [
{
'$match': {
'$and': [
{
'status': {
'$ne': 5
}
}
]
}
}, {
'$addFields': {
'fliteDate': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
}
}, {
'$group': {
'_id': '$fliteDate',
'totalSum': {
'$sum': 1
}
}
}, {
'$match': {
'$expr': {
'$eq': [
'$_id', '$$date'
]
}
}
}
],
'as': 'onlineTicket'
}
}, {
'$lookup': {
'from': 'ticket',
'let': {
'date': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
},
'pipeline': [
{
'$match': {
'$and': [
{
'status': {
'$ne': 5
}
}
]
}
}, {
'$addFields': {
'fliteDate': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
}
}, {
'$group': {
'_id': '$fliteDate',
'totalSum': {
'$sum': 1
}
}
}, {
'$match': {
'$expr': {
'$eq': [
'$_id', '$$date'
]
}
}
}
],
'as': 'offlineTicket'
}
}, {
'$lookup': {
'from': 'ticket',
'let': {
'date': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
},
'pipeline': [
{
'$match': {
'$and': [
{
'status': 3
}
]
}
}, {
'$addFields': {
'fliteDate': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
}
}
}, {
'$group': {
'_id': '$fliteDate',
'totalSum': {
'$sum': 1
}
}
}, {
'$match': {
'$expr': {
'$eq': [
'$_id', '$$date'
]
}
}
}
],
'as': 'checkedInTicket'
}
}, {
'$addFields': {
'onlineTickets': {
'$ifNull': [
{
'$arrayElemAt': [
'$onlineTicket.totalSum', 0
]
}, 0
]
},
'offlineTickets': {
'$ifNull': [
{
'$arrayElemAt': [
'$offlineTicket.totalSum', 0
]
}, 0
]
},
'checkedInTickets': {
'$ifNull': [
{
'$arrayElemAt': [
'$checkedInTicket.totalSum', 0
]
}, 0
]
}
}
}, {
'$unset': [
'onlineTicket', 'offlineTicket', 'checkedInTicket'
]
}, {
'$group': {
'_id': {
'dateTime': {
'$dateToString': {
'format': '%d-%m-%Y',
'date': '$createdAt'
}
},
'onlineTickets': '$onlineTickets',
'offlineTickets': '$offlineTickets',
'checkedInTickets': '$checkedInTickets',
}
}
}, {
'$project': {
'_id': 0,
'dateTime': '$_id.dateTime',
'onlineTickets': '$_id.onlineTickets',
'offlineTickets': '$_id.offlineTickets',
'checkedInTickets': '$_id.checkedInTickets',
}
}, {
'$sort': {
'dateTime': -1
}
}
]
this.ticketModel.aggregate(pipeline)//ticketModel 是mongose model包括但不限于这些场景，如有其他应用场景，和疑问欢迎评论区留言
我正在参与2024腾讯技术创作特训营第五期有奖征文，快来和我瓜分大奖！
