Aggregation: Perform countBy on multiple $lookup arrays in a single query

user_2498782 0
Viewed 37

Reproduction: https://mongoplayground.net/p/f9BraX4emor

The playground query does the job halfway. It finds the matching documents, but it doesn't aggregate bounties and emotes with a COUNT BY operation.

My desired result is this:

[
  {
    "_id": ObjectId("5e7d7bf7d86d85088863f4de"),
    "channelId": "23435553",
    "bounties": {
        "user1": 150, // user1 had 2 bounties with 100 + 50 amounts
        "user2": 200, // user2 had 1 bounty with 200 amount
    },
    "emotes": {
        "A": 2, // There were 2 docs with emoteId == A
        "B": 1, // There was 1 doc with emoteId == B
    },
  }
]

I actually don't care about the specific data structure of the aggregated result. For ex. I'd be fine if bounties and emotes were arrays instead of dictionaries. I think the $unwind operator can be used here, but I'm just not sure how.

Currently, I'm making way too many queries to fulfill a REST API request.

For ex, my API receives a request to find 10 suggestions for a channel. First, I run 1 query that returns 10 suggestions. Then FOR EACH suggestion, I run 1 query to aggregate bounties, and another to aggregate emotes. That's 21 queries...... in a single REST API call.. :(. I want to run 1 query, not 21.

mongodb
1 Answers

To create an object based on the document values, we need to use $arrayToObject operator.

Elegant solution

Explanation

  1. We perform $map operator to get [k:"...", v:"..."] structure.
  2. Also, we need to count how many times emotes / bounties repeats
db.suggestions.aggregate([
  {
    $match: {
      "channelId": "23435553"
    }
  },
  {
    $lookup: {
      from: "emotes",
      localField: "_id",
      foreignField: "suggestionId",
      as: "emotes"
    }
  },
  {
    $lookup: {
      from: "bounties",
      localField: "_id",
      foreignField: "suggestionId",
      as: "bounties"
    }
  },
  {
    $project: {
      _id: 1,
      channelId: 1,
      emotes: {
        $arrayToObject: {
          $map: {
            input: "$emotes",
            as: "emote",
            in: {
              k: "$$emote.emoteId",
              v: {
                $size: {
                  $filter: {
                    input: "$emotes",
                    as: "e",
                    cond: {
                      $eq: [
                        "$$emote.emoteId",
                        "$$e.emoteId"
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      },
      bounties: {
        $arrayToObject: {
          $map: {
            input: "$bounties",
            as: "bounty",
            in: {
              k: "$$bounty.userId",
              v: {
                $sum: {
                  $map: {
                    input: {
                      $filter: {
                        input: "$bounties",
                        as: "b",
                        cond: {
                          $eq: [
                            "$$bounty.userId",
                            "$$b.userId"
                          ]
                        }
                      }
                    },
                    in: "$$this.bountyAmount"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

Classic solution

Explanation

  1. We need to flatten emotes / bounties after $lookup.
  2. We perform $group stages to reduce duplicated values
  3. We create [{k:"...", v:"..."}] structure to convert it into Object with $arrayToObject operator.
db.suggestions.aggregate([
  {
    $match: {
      "channelId": "23435553"
    }
  },
  {
    $lookup: {
      from: "emotes",
      localField: "_id",
      foreignField: "suggestionId",
      as: "emotes"
    }
  },
  {
    $unwind: {
      path: "$emotes",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        "_id": "$_id",
        "emoteId": "$emotes.emoteId"
      },
      channelId: {
        $first: "$channelId"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $push: {
          k: "$_id.emoteId",
          v: "$count"
        }
      }
    }
  },
  {
    $lookup: {
      from: "bounties",
      localField: "_id",
      foreignField: "suggestionId",
      as: "bounties"
    }
  },
  {
    $unwind: {
      path: "$bounties",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        "_id": "$_id",
        "userId": "$bounties.userId"
      },
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $first: "$emotes"
      },
      bountyAmount: {
        $sum: "$bounties.bountyAmount"
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $first: "$emotes"
      },
      bounties: {
        $push: {
          k: "$_id.userId",
          v: "$bountyAmount"
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      channelId: 1,
      emotes: {
        $arrayToObject: "$emotes"
      },
      bounties: {
        $arrayToObject: "$bounties"
      },
      
    }
  }
])

MongoPlayground

user_37104900
Related