You made a valid points.
It is right, usually more efficient, but I am not sure it is in this case. Since the pipeline
you must likely incur a collection scan which might fetch all documents. But we know nothing about the dataset and the indexes. My claim is that with proper indexes you may end up with 2 index scans rather than 1 collection scan. In some cases, 2 index scans might be better than 1 collection scan. Specially in this case since the dataset of the 2 facets are mutually exclusives.
That is why testing should be done by original poster with its own dataset.
Another point about the $facet, is that it returns only 2 top documents which might breaks the BSON limit. See $facet (expression operator) - Database Manual - MongoDB Docs.
And testing should also try without the $unionWith and do 2 completely different aggregations (yes 2 round trips to the server) that would run in parallel in the server. You combine the output in the application. All this could be done because nothing is done before and after $facet.
It might be even possible to improve the $facet.
I would try to move $sort of category order and advance_option… before the $facet.
Then I would have 4 $facet rather than 2, one for each CategoryOrder, this would limit the risk of busting the BSON limit. Each $facet would only $sort on start_time which could improve in-memory sort if the indexes cannot be used.
Thanks for pitching in, we all get better when we oppose opinions and ideas.
This was your first post. Keep it going that how we like it.