CODEWITHSUNDEEP
pythonmongodbmongodb-querypymongo-3.x
MOHAMED AZARUDEEN
MOHAMED AZARUDEEN

Reputation: 171

Mongo Query to Join two collection and get Sum of the particular field - MongoDB

I am having two collection post, comment.

post collection data,

[
  {
    _id: "2218457a-4b5d-4f77-a27f-9546099202df",
    author: "[email protected]",
    description: "Some description",
    total_votes: 10
  },
  {
    _id: "2218457a-565-4f77-a27f",
    author: "[email protected]",
    description: "Second description",
    total_votes: 20
  },
  {
    _id: "2218457a-4b5d-4f77-23das",
    author: "[email protected]",
    description: "Some description",
    total_votes: 5
  },
 {
    _id: "2218457a-232dd-4f77-23das",
    author: "[email protected]",
    description: "Some description",
    total_votes: 10
  },
]

comment collection data,

[
  {
    _id: "2218457a-12312",
    author: "[email protected]",
    description: "Some description",
    total_votes: 5
  },
  {
    _id: "2218457a-56sd5",
    author: "[email protected]",
    description: "Second Comment",
    total_votes: 5
  },
  {
    _id: "2218457a-4b5d-4f77-23das",
    author: "[email protected]",
    description: "Third Comment",
    total_votes: 5
  }
]

I want to get the sum of total_votes based on the author.

I need the output as below,

[
 {
   author: "[email protected]",
   total_votes: 35
 },
 {
   author: "[email protected]",
   total_votes: 15
 },
 {
   author: "[email protected]",
   total_votes: 10
 }
]

Kindly provide the mongo query for my expected result.

Thanks in advance.

Upvotes: 0

Views: 369

Answers (1)

ray
ray

Reputation: 15256

I presume your main struggle is how to combine the sum from 2 collections. You may use $unionWith for this. For a specific author's records, you may consider a more performant way of matching them in early stage.

db.post.aggregate([
  {
    $match: {
      "author": "[email protected]"
    }
  },
  {
    "$unionWith": {
      "coll": "comment",
      pipeline: [
        {
          $match: {
            "author": "[email protected]"
          }
        }
      ]
    }
  },
  {
    $group: {
      _id: "$author",
      total_votes: {
        $sum: "$total_votes"
      }
    }
  }
])

Here is the Mongo playground for your reference.

Upvotes: 2

Related Questions