How to compare two objects in which one having array field using MongoDb Aggregation-CodePudding

I have two collections one is company and other is reports so below is 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. Here how can I get the desired output.

I tried checking by $ObjectToArray operator in mongodb pipeline but its not working its 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}}
      ]
    }},
  ])

Someone let me know how can I get the desired result while comparing array and string elements inside an object.

CodePudding user response：

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
        ]
      }
    }
  }
])

Playground