英文:
How to compare two objects in which one having array field using MongoDB Aggregation
问题
我有两个集合，一个是company，另一个是reports。以下是我的集合。
company集合
[
{
company_id:1,
hubId:4,
dimensions:{ region:['North america'],country:['USA']},
name:'Amsol Inc.'
},
{
company_id:1,
hubId:4,
dimensions:{ region:['North america'],country:['Canada','Greenland']},
name:'Amsol Inc.'
},
{
company_id:2,
hubId:7,
dimensions:{ region:['North america'],country:['USA'],revenue:34555},
name:'Microsoft Inc.'
}
]
reports集合
[
{
report_id:1,
name:'example report',
hubId:4,
dimensions:{ region:'North america',country:'USA'},
name:'Amsol Inc.'
},
{
report_id:2,
name:'example report',
hubId:4,
dimensions:{ region:'North america',country:'Canada'},
name:'Amsol Inc.'
},
{
report_id:3,
name:'example report',
hubId:5,
dimensions:{ region:'North america',country:'USA',revenue:20000},
name:'Microsoft Inc.'
},
{
report_id:4,
name:'example report',
hubId:4,
dimensions:{region:'North america',country:'Greenland'},
name:'Amsol Inc.'
}
]
输出
[
{
report_id:1,
name:'example report',
hubId:4,
dimensions:{ region:'North america',country:'USA'},
name:'Amsol Inc.'
},
{
report_id:2,
name:'example report',
hubId:4,
dimensions:{region:'North america',country:'Canada'},
name:'Amsol Inc.'
},
{
report_id:4,
name:'example report',
hubId:4,
dimensions:{region:'North america',country:'Greenland'},
name:'Amsol Inc.'
}
]
我想获取与公司相同的HubId和dimensions的所有报告。但在公司集合中，维度字段的region和country是数组，在报告集合中它们都是字符串。如何获取所需的输出？
我尝试使用MongoDB管道中的$ObjectToArray运算符进行检查，但它不起作用。它只显示一个完全匹配的结果，而忽略了数组元素。
以下是我尝试的聚合管道：
db.reports.aggregate([
{
$lookup: {
from: "company",
let: { hubId: "$hubId", dimensions: "$dimensions" },
as: "companies",
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$hubId", "$$hubId"] },
{
$setEquals: [
{ $objectToArray: "$dimensions" },
{ $objectToArray: "$$dimensions" }
]
}
]
}
}
},
{ $project: { _id: 1 } }
]
}
},
])
如何在比较对象内的数组和字符串元素时获取所需的结果？
英文:
I have two collections one is company and other is reports. Below are my collections.
company collection
[
{
company_id:1,
hubId:4
dimensions:{ region:['North america'],country:['USA']},
name:'Amsol Inc.'
},
{
company_id:1,
hubId:4
dimensions:{ region:['North america'],country:['Canada','Greenland']},
name:'Amsol Inc.'
},
{
company_id:2,
hubId:7
dimensions:{ region:['North america'],country:['USA'],revenue:34555},
name:'Microsoft Inc.'
}
]
reports collection
[
{
report_id:1,
name:'example report',
hubId:4
dimensions:{ region:'North america',country:'USA'},
name:'Amsol Inc.'
},
{
report_id:2,
name:'example report',
hubId:4
dimensions:{ region:'North america',country:'Canada'},
name:'Amsol Inc.'
},
{
report_id:3,
name:'example report',
hubId:5
dimensions:{ region:'North america',country:'USA',revenue:20000},
name:'Microsoft Inc.'
}
{
report_id:4,
name:'example report',
hubId:4
dimensions:{region:'North america',country:'Greenland'},
name:'Amsol Inc.'
}
]
Output
[
{
report_id:1,
name:'example report',
hubId:4
dimensions:{ region:'North america',country:'USA'},
name:'Amsol Inc.'
},
{
report_id:2,
name:'example report',
hubId:4,
dimensions:{region:'North america',country:'Canada'},
name:'Amsol Inc.'
},
{
report_id:4,
name:'example report',
hubId:4
dimensions:{region:'North america',country:'Greenland'},
name:'Amsol Inc.'
}
]
I want to fetch all the report that has same HubId and dimensions as of companies. But in company collection in dimension field region and country fields are array and in reports collection they both are string. How can I get the desired output?
I tried checking by $ObjectToArray operator in MongoDB pipeline but it's not working. It's showing one result which is completely matching its ignoring array elements.
Below is my aggregation pipeline I tried:
db.reports.aggregate([
{$lookup: {
from: "company",
let: {hubId: "$hubId", dimensions: "$dimensions"},
as: "companies",
pipeline: [
{$match: {
$expr: {
$and: [
{$eq: ["$hubId", "$$hubId"]},
{$setEquals: [
{$objectToArray: "$dimensions"},
{$objectToArray: "$$dimensions"}
]}
]
}
}},
{$project: {_id: 1}}
]
}},
])
How can I get the desired result while comparing array and string elements inside an object?
答案1
得分: 1
以下是您要翻译的代码部分：
db.reports.aggregate([
{
$lookup: {
from: "company",
let: {
hubId: "$hubId",
dimensionsCountry: "$dimensions.country",
dimensionsRegion: "$dimensions.region"
},
as: "companies",
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: ["$hubId","$$hubId"]
},
{
$in: ["$$dimensionsRegion","$dimensions.region"]
},
{
$in: ["$$dimensionsCountry","$dimensions.country"]
}
]
}
}
},
{
$project: {"_id": 1}
}
]
}
},
{
$match: {
$expr: {
$gt: [
{$size: "$companies"}, 0
]
}
}
}
])
您可以在Playground中查看示例。
英文:
Another way...
db.reports.aggregate([
{
$lookup: {
from: "company",
let: {
hubId: "$hubId",
dimensionsCountry: "$dimensions.country",
dimensionsRegion: "$dimensions.region"
},
as: "companies",
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: ["$hubId","$$hubId"]
},
{
$in: ["$$dimensionsRegion","$dimensions.region"]
},
{
$in: ["$$dimensionsCountry","$dimensions.country"]
}
]
}
}
},
{
$project: {"_id": 1}
}
]
}
},
{
$match: {
$expr: {
$gt: [
{$size: "$companies"}, 0
]
}
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论