mongodb - MongoDB - 基于另一个字段值的 $match
问题描述
我想从 SQL 中获得简单的内部连接等结果,以获取相同 id_customer 的所有帐户。我几乎这样做了,但我得到了类似左连接的东西,我现在不知道如何归档查询结果。
询问:
db.customer.aggregate([
{
$match: {
"LOAN.AMOUNT": { $gte: 41000 }
}
},
{
$lookup: {
from: "department",
localField: "ID",
foreignField: "ACCOUNT.ID_CUSTOMER",
as: "customer"
}
},
{ $match: { "myArray": { $ne: [] } } },
{
$unwind: {
path: "$customer",
preserveNullAndEmptyArrays: false
}
}
,
{
$unwind: "$customer.ACCOUNT"
},
{
$match: {
"customer.ACCOUNT.ID_CUSTOMER": "$ID"
}
},
])
实际结果:
empty
预期结果:
{
"_id" : ObjectId("5dfccc28d29876c2988c7c05"),
"ID" : 4.0,
"SECOND_NAME" : "abc",
"FIRST_NAME" : "abc",
"ID_DISCOUNT" : {
"ID" : 3.0,
"TITLE" : "abc",
"AMOUNT" : 5.0
},
"LOAN" : {
"ID" : 4.0,
"AMOUNT" : 42750.0
},
"customer" : {
"_id" : ObjectId("5dfb7f8f5861584bbaedf718"),
"ID" : 1.0,
"TITLE" : "abc",
"ADDRESS" : "abc",
"CITY" : "abc",
"ACCOUNT" : {
"ID" : 10.0,
"ID_CUSTOMER" : 4.0,
"DATE_OPEN" : "2019-09-24 21:03:44"
}
}
}
{
"_id" : ObjectId("5dfccc28d29876c2988c7c05"),
"ID" : 4.0,
"SECOND_NAME" : "abc",
"FIRST_NAME" : "abc ",
"ID_DISCOUNT" : {
"ID" : 3.0,
"TITLE" : "abc",
"AMOUNT" : 5.0
},
"LOAN" : {
"ID" : 4.0,
"AMOUNT" : 42750.0
},
"customer" : {
"_id" : ObjectId("5dfb7f8f5861584bbaedf718"),
"ID" : 1.0,
"TITLE" : "abc",
"ADDRESS" : "abc",
"CITY" : "abc",
"ACCOUNT" : {
"ID" : 11.0,
"ID_CUSTOMER" : 4.0,
"DATE_OPEN" : "2019-08-23 21:03:44"
}
}
}
具有相同 id_customer 的所有帐户的列表,但
$match: {
"customer.ACCOUNT.ID_CUSTOMER": "$ID"
}
不做我想做的事。
解决方案
我找到了解决方案:使用 $expr 运算符
db.customer.aggregate([
{
$match: {
"LOAN.AMOUNT": { $gte: 41000 }
}
},
{
$lookup: {
from: "department",
localField: "ID",
foreignField: "ACCOUNT.ID_CUSTOMER",
as: "customer"
}
},
{ $match: { "myArray": { $ne: [] } } },
{
$unwind: {
path: "$customer",
preserveNullAndEmptyArrays: false
}
}
,
{
$unwind: "$customer.ACCOUNT"
},
{ $match: { $expr: { $eq: ["$customer.ACCOUNT.ID_CUSTOMER", "$ID"] } } },
])
推荐阅读
- oracle - Toad 结果集:直观地突出显示行组
- flutter - 当两个滚动始终可见时嵌套滚动问题(颤振稳定通道)
- node.js - 将 Twilio 呼叫转变为会议
- c - 分段故障设置信号量功能
- php - 雄辩的多对多返回一个空值
- sql - 索引大表最适合性能 行或列索引
- oop - Tkinter 从 Mikhails 先生的 ScrolledWindow 的 Bryan Oakley 先生的孩子制作 PageOne
- thingsboard - MQTT - 如何更改 CE (SenML) 上 JSON 的默认解析器的行为?
- cytoscape.js - dagre网络 - 重复的边缘
- javascript - Vue.js 通过客户端渲染预取数据