CODEWITHSUNDEEP
mongodbmongodb-querylookupmongodb-lookup
Coder
Coder

Reputation: 339

Get matched and unmatched count from another collection mongodb

I have two collection present in mongodb database. one status column is common. I need matched and unmatched count from both the collection based on status column. I have written some code but it fetching only matched count.

db.properties.aggregate([
    {
        $lookup: {
            from: "old_properties_data",
            localField: "identifier",
            foreignField: "identifier",
            as: "col2docs"
        }
    },
    
  
    {"$group" : {_id:"_id", count:{$sum:1}}}
])

Upvotes: 0

Views: 399

Answers (1)

ray
ray

Reputation: 15276

You can first $group by identifier to get the total match count. Then You can use uncorreleated subquery in $lookup to get the total size of old_properties_data. Finally do a $subtract to get the total unmatched count.

db.properties.aggregate([
  {
    $lookup: {
      from: "old_properties_data",
      localField: "identifier",
      foreignField: "identifier",
      as: "matched"
    }
  },
  {
    $project: {
      identifier: 1,
      match_count: {
        $size: "$matched"
      }
    }
  },
  {
    "$group": {
      "_id": "$identifier",
      "total_match_count": {
        "$sum": "$match_count"
      }
    }
  },
  {
    "$lookup": {
      "from": "old_properties_data",
      "pipeline": [
        {
          "$count": "total"
        }
      ],
      "as": "total_count"
    }
  },
  {
    "$unwind": "$total_count"
  },
  {
    "$project": {
      total_match_count: 1,
      total_unmatched_count: {
        "$subtract": [
          "$total_count.total",
          "$total_match_count"
        ]
      }
    }
  }
])

Here is the Mongo Playground for your reference.

Upvotes: 0

Related Questions