Optimizing MongoDB Aggregation Pipeline (Group, Lookup, Match)

user_12397250 0
Viewed 664

I'm new on NoSQL Database and i choose MongoDB as my first NoSQL Database. I made an aggregation pipeline to shows the data that i want, here's my document sample:

Document sample from Users Collection

{
    "_id": 9,
    "name": "Sample Name",
    "email": "email@example.com",
    "password": "password hash"
}

Document sample from Pages Collection (this one doesn't really matter)

{
    "_id": 42,
    "name": "Product Name",
    "description": "Product Description",
    "user_id": 8,
    "rating_categories": [{
        "_id": 114,
        "name": "Build Quality"
    }, {
        "_id": 115,
        "name": "Price"
    }, {
        "_id": 116,
        "name": "Feature"
    }, {
        "_id": 117,
        "name": "Comfort"
    }, {
        "_id": 118,
        "name": "Switch"
    }]
}

Document sample from Reviews Collection

{
    "_id": 10,
    "page_id": 42, #ID reference from pages collection
    "user_id": 8, #ID reference from users collection
    "review": "The review of the product",
    "ratings": [{
        "_id": 114, #ID Reference from pages collection of what rating category it is
        "rating": 5
    }, {
        "_id": 115,
        "rating":4
    }, {
        "_id": 116,
        "rating": 5
    }, {
        "_id": 117,
        "rating": 3
    }, {
        "_id": 118,
        "rating": 4
    }],
    "created": "1582825968963", #Date Object
    "votes": {
        "downvotes": [],
        "upvotes": [9] #IDs of users who upvote this review
    }
}

I want to get reviews by page_id which can be accessed from the API i made, here's the expected result from the aggregation:

[
  {
    "_id": 10, #Review of the ID
    "created": "Thu, 27 Feb 2020 17:52:48 GMT",
    "downvote_count": 0, #Length of votes.downvotes from reviews collection
    "page_id": 42, #Page ID
    "ratings": [ #Stores what rate at what rating category id
      {
        "_id": 114,
        "rating": 5
      },
      {
        "_id": 115,
        "rating": 4
      },
      {
        "_id": 116,
        "rating": 5
      },
      {
        "_id": 117,
        "rating": 3
      },
      {
        "_id": 118,
        "rating": 4
      }
    ],
    "review": "The Review",
    "upvote_count": 0, #Length of votes.upvotes from reviews collection
    "user": { #User who reviewed
      "_id": 8, #User ID
      "downvote_count": 0, #How many downvotes this user receive from all of the user's reviews
      "name": "Sample Name", #Username
      "review_count": 1, #How many reviews the user made
      "upvote_count": 1 #How many upvotes this user receive from all of the user's reviews
    },
    "vote_state": 0 #Determining vote state from the user (who requested to the API) for this review, 0 for no vote, -1 for downvote, 1 for upvote
  },
  ...
]

Here's the pipeline of the aggregation for reviews collection that i made for the result above:

user_id = 9
page_id = 42
pipeline = [
            {"$group": {
                    "_id": {"user_id":"$user_id", "page_id": "$page_id"},
                    "review_id": {"$last": "$_id"},
                    "page_id": {"$last": "$page_id"},
                    "user_id" : {"$last": "$user_id"},
                    "ratings": {"$last": "$ratings"},
                    "review": {"$last": "$review"},
                    "created": {"$last": "$created"},
                    "votes": {"$last": "$votes"},
                    "upvote_count": {"$sum": 
                        {"$cond": [ 
                            {"$ifNull": ["$votes.upvotes", False]}, 
                            {"$size": "$votes.upvotes"}, 
                            0
                        ]}
                    },
                    "downvote_count": {"$sum": 
                        {"$cond": [ 
                            {"$ifNull": ["$votes.downvotes", False]}, 
                            {"$size": "$votes.downvotes"}, 
                            0
                        ]}
                    }}},
            {"$lookup": {
                "from": "users",
                "localField": "user_id",
                "foreignField": "_id",
                "as": "user"
            }},
            {"$unwind": "$user"},
            {"$lookup": {
                "from": "reviews",
                "localField": "user._id",
                "foreignField": "user_id",
                "as": "user.reviews"
            }},
            {"$addFields":{
                "_id": "$review_id",
                "user.review_count": {"$size": "$user.reviews"},
                "user.upvote_count": {"$sum":{
                    "$map":{
                        "input":"$user.reviews",
                        "in":{"$cond": [ 
                            {"$ifNull": ["$$this.votes.upvotes", False]}, 
                            {"$size": "$$this.votes.upvotes"}, 
                            0
                        ]}
                    }
                }},
                "user.downvote_count": {"$sum":{
                    "$map":{
                        "input":"$user.reviews",
                        "in":{"$cond": [ 
                            {"$ifNull": ["$$this.votes.downvotes", False]}, 
                            {"$size": "$$this.votes.downvotes"}, 
                            0
                        ]}
                    }
                }},
                "vote_state": {"$switch": {
                    "branches": [
                        {"case": { "$and" : [
                            {"$ifNull": ["$votes.upvotes", False]}, 
                            {"$in": [user_id, "$votes.upvotes"]}
                        ]}, "then": 1
                        },
                        {"case": { "$and" : [
                            {"$ifNull": ["$votes.downvotes", False]}, 
                            {"$in": [user_id, "$votes.downvotes"]}
                        ]}, "then": -1
                        },
                    ],
                    "default": 0
                }},
            }},
            {"$project":{
                "user.password": 0,
                "user.email": 0,
                "user_id": 0,
                "review_id" : 0,
                "votes": 0,
                "user.reviews": 0 
            }},
            {"$sort": {"created": -1}},
            {"$match": {"page_id": page_id}},
        ]

Note: User can make multiple reviews for same page_id, but only the latest will be shown

I'm using pymongo btw, that's why operators have quotation mark

My questions are:

  1. Is there any room to optimize my aggregation pipeline?

  2. Is it considered as a good practice to have multiple small aggregate execution to get datas like above, or its always better to have 1 big aggregation (or as less as possible) to get the data that i want?

  3. As you can see, every time i want to access votes.upvotes or votes.downvotes from a document on review collection, i checked whether the field is null or not, that's because the field votes.upvotes and votes.downvotes isn't being made when user make a review, instead it's being made when an user gives a vote to that review. Should i make an empty field on votes.upvotes and votes.downvotes when user make a review and remove the $ifNull? Will that increase the performance of the aggregation?

Thanks

aggregation-framework pymongo mongodb python
1 Answers

Check if this aggregation has better performance.

Create these indexes if you don't have already:

db.reviews.create_index([("page_id", 1)])

Note: We can improve even more the performance avoiding $lookup reviews again.

db.reviews.aggregate([
  {
    $match: {
      page_id: page_id
    }
  },
  {
    $addFields: {
      request_user_id: user_id
    }
  },
  {
    $group: {
      _id: {
        page_id: "$page_id",
        user_id: "$user_id",            
        request_user_id: "$request_user_id"
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $lookup: {
      "from": "users",
      "let": {
        root_user_id: "$_id.user_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$root_user_id",
                "$_id"
              ]
            }
          }
        },
        {
          $lookup: {
            "from": "reviews",
            "let": {
              root_user_id: "$$root_user_id"
            },
            "pipeline": [
              {
                $match: {
                  $expr: {
                    $eq: [
                      "$$root_user_id",
                      "$user_id"
                    ]
                  }
                }
              },
              {
                $project: {
                  user_id: 1,
                  downvote_count: {
                    $size: "$votes.downvotes"
                  },
                  upvote_count: {
                    $size: "$votes.upvotes"
                  }
                }
              },
              {
                $group: {
                  _id: null,
                  review_count: {
                    $sum: {
                      $cond: [
                        {
                          $eq: [
                            "$$root_user_id",
                            "$user_id"
                          ]
                        },
                        1,
                        0
                      ]
                    }
                  },
                  upvote_count: {
                    $sum: "$upvote_count"
                  },
                  downvote_count: {
                    $sum: "$downvote_count"
                  }
                }
              },
              {
                $unset: "_id"
              }
            ],
            "as": "stats"
          }
        },
        {
          $project: {
            tmp: {
              $mergeObjects: [
                {
                  _id: "$_id",
                  name: "$name"
                },
                {
                  $arrayElemAt: [
                    "$stats",
                    0
                  ]
                }
              ]
            }
          }
        },
        {
          $replaceWith: "$tmp"
        }
      ],
      "as": "user"
    }
  },
  {
    $addFields: {
      first: {
        $mergeObjects: [
          "$$ROOT",
          {
            $arrayElemAt: [
              "$data",
              0
            ]
          },
          {
            user: {
              $arrayElemAt: [
                "$user",
                0
              ]
            },
            created: {
              $toDate: {
                $toLong: {
                  $arrayElemAt: [
                    "$data.created",
                    0
                  ]
                }
              }
            },
            downvote_count: {
              $reduce: {
                input: "$data.votes.downvotes",
                initialValue: 0,
                in: {
                  $add: [
                    "$$value",
                    {
                      $size: "$$this"
                    }
                  ]
                }
              }
            },
            upvote_count: {
              $reduce: {
                input: "$data.votes.upvotes",
                initialValue: 0,
                in: {
                  $add: [
                    "$$value",
                    {
                      $size: "$$this"
                    }
                  ]
                }
              }
            },
            vote_state: {
              $cond: [
                {
                  $gt: [
                    {
                      $size: {
                        $filter: {
                          input: "$data.votes.upvotes",
                          cond: {
                            $in: [
                              "$_id.request_user_id",
                              "$$this"
                            ]
                          }
                        }
                      }
                    },
                    0
                  ]
                },
                1,
                {
                  $cond: [
                    {
                      $gt: [
                        {
                          $size: {
                            $filter: {
                              input: "$data.votes.downvotes",
                              cond: {
                                $in: [
                                  "$_id.request_user_id",
                                  "$$this"
                                ]
                              }
                            }
                          }
                        },
                        0
                      ]
                    },
                    -1,
                    0
                  ]
                }
              ]
            }
          }
        ]
      }
    }
  },
  {
    $unset: [
      "first.data",
      "first.votes",
      "first.user_id",
      "first.request_user_id"
    ]
  },
  {
    $replaceWith: "$first"
  },
  {
    "$sort": {
      "created": -1
    }
  }
])

MongoPlayground

user_37104900
Related