CODEWITHSUNDEEP
mongodb
apelidoko
apelidoko

Reputation: 790

How to get count of joined collections in MongoDB?

I have here my sample query to join multiple collections by field 'company_name', It is my day 1 in mongodb,

I am not sure how to get the count of the joined elements.

I have 3 collections with same column "company_name"

Here is my query so far,

db.ds_companyscrapdata.aggregate(
[
  {
    $match: {
        "company_name": "Group, LLC"
    }
  },
  { 
     $lookup: {
        from: "ds_company_news",
        localField: "company_name",
        foreignField: "company_name",
        as: "newsData"
     }
  },
  {  
      $lookup: {
        from: "ds_company_ma",
        localField: "company_name",
        foreignField: "company_name",
        as: "MA"
    }
  }
])

Here is the returned result

{
    "_id" : ObjectId("5f7c114266938e100708f258"),
    "company_name" : "Group, LLC",
    "date" : "2020-10-06",
    "created_at" : "2020-10-06 06:40:02",
    "status" : 1,
    "is_delta" : 0,
    "newsData" : [ 
        {
            "_id" : ObjectId("5f7c113766938e100708f253"),
            "news_tite" : "412802876",
            "news_link" : "Jeddie",
            "company_name" : "Group, LLC",
        }, 
        {
            "_id" : ObjectId("5f7c113766938e100708f223"),
            "news_tite" : "title2",
            "news_link" : "test news link",
            "company_name" : "Group, LLC",
        }
    ],
    "MA" : [ 
        {
            "_id" : ObjectId("5f7c113766938e100708f253"),
            "merge_acquisition" : "Sample Acquisition",
            "url_link" : "sample ma link",
            "company_name" : "Group, LLC",
        }
    ]
}

How to generate something like this,

{
        "_id" : ObjectId("5f7c114266938e100708f258"),
        "company_name" : "Group, LLC",
        "date" : "2020-10-06",
        "created_at" : "2020-10-06 06:40:02",
        "status" : 1,
        "is_delta" : 0,
        "newsDataCount" : 2,
        "MACount" : 1
    }

Thank You

Upvotes: 3

Views: 839

Answers (1)

user12582392
user12582392

Reputation:

You need to

  • measure the $size of the joined field
  • $unset the previous fields

(Advice: if you need to learn this quickly use Atlas or Compass, not CLI).

db.ds_companyscrapdata.aggregate(
[
  {
    $match: {
        "company_name": "Group, LLC"
    }
  },
  { 
     $lookup: {
        from: "ds_company_news",
        localField: "company_name",
        foreignField: "company_name",
        as: "newsData"
     }
  },
  {  
      $lookup: {
        from: "ds_company_ma",
        localField: "company_name",
        foreignField: "company_name",
        as: "MA"
    }
  },
 {
  $addFields:{
    newsDataCount:{$size:"$newsData"}, 
    MACount:{"$size":"$MA"} 
    } 
  },
{$unset:["MA", "newsData"]}
])

Upvotes: 3

Related Questions