Filtering the output of a faceted aggregation pipeline

MongoDB Atlas MongoDB Charts
#1

We have a collection of ‘messages’ documents. I’d like to use Charts to present my customers with a visualisation of the number of messages that were delivered. I’ve used answers in this forum to put together the aggregation pipeline shown below, which gives me the percentage delivered for ALL customers. I would like to be able to use an injected function to filter the documents by the existing customerid field, so that each customer can see just their own delivered percentage. How can I do that?

[
  {
    $facet: {
      delivered: [
        {
          $match: {
            status: "DELIVERED"
          }
        },
        {
          $count: "count"
        }
      ],
      total: [{ $count: "count" }]
    }
  },
  {
    $unwind: "$delivered"
  },
  {
    $unwind: "$total"
  },
  {
    $set: {
      deliveredPercentage: {
        $divide: ["$delivered.count", "$total.count"]
      }
    }
  }
]
#2

Hi @Phil_Warner, thanks for using Charts!
The injected filter is applied after the query bar pipeline, which makes this a little tricky but hopefully not unsolvable. Rather than use a $facet, you could change the query to something like this to count the totals per customer ID:

[
  {
    $group: {
      _id: "$customerId",
      total: { $sum: 1 },
      delivered: { $sum: { $cond: [ { $eq: ["$status", "DELIVERED"]}, 1, 0 ] } }
    }
  },
  {
   $set: {
      deliveredPercentage: {
        $divide: ["$delivered", "$total"]
      }
    }
  }
]

This will give you a row per customer ID (see my example below although I’m using different fields). From here you can use an injected filter so that each viewer only sees their own customerid document.

image
image1128×863 51.3 KB

#3

Right on the button Tom! Thank you very much :grinning:

Charts was a big factor in the decision to move the Atlas. Thank you.

1 Like