How to compare two objects in which one having array field using MongoDB Aggregation

Question

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?

I not sure what you try to achieve, but from company collection: dimensions:{ region:['North america'],country:['USA']} and reports collection: dimensions:{ region:'North america',country:'USA'}, there is no match. — Yong Shun
– Yong Shun, Commented Jan 9, 2023 at 7:47
In companies collection for company_id:2 I missed earlier making region and country field to array that time it was showing correct results but after I updated my company collection its not showing desired result.It should include report_id:4 also in a result. — Digvijay Singh Thakur
– Digvijay Singh Thakur, Commented Jan 9, 2023 at 8:09
Suspect that compares the objects with $eq is not recommended as the order of keys matters. Possibly this demo is what you need. — Yong Shun
– Yong Shun, Commented Jan 9, 2023 at 8:31

Noel · Accepted Answer · 2023-01-09 08:37:56Z

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

Collectives™ on Stack Overflow

How to compare two objects in which one having array field using MongoDB Aggregation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related