Menu
I want to get results like simple inner join from SQL, for get all accounts of the same id_customer. I almost do this, but I get something like left join, and I don't now how to filer the result of query.
query:
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"
}
},
])
actual result:
empty
expected result:
{
"_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"
}
}
}
List of all account with the same id_customer, but
$match: {
"customer.ACCOUNT.ID_CUSTOMER": "$ID"
}
not doing what I want to do.
970
The way we do this is by $project ing our documents and using the $concat string aggregation operator to return the concatenated string. You then iterate the cursor and use the $set update operator to add the new field to your documents using bulk operations for maximum efficiency.
You can use $getField to retrieve the value of fields with names that contain periods ( . ) or start with dollar signs ( $ ). Use $setField to add or update fields with names that contain dollar signs ( $ ) or periods ( . ).
Basically, MongoDB provides the different match operators such as $match and $count, etc, to the user and we can utilize them as per our requirement. We can also use a match operator for the aggregation pipeline. In the above syntax, we use the $match Mongodb operator as shown.
I found the solution: use $expr operator
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"] } } },
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With