title: Cosmos DB Aggregation Pipeline incompatible (Mongo API)?
body: <p>Perhaps somebody has a good idea how we can fix or workaround something which looks like a bug in the current implementation of Aggregation Pipelines for MongoDB in Azure Cosmos DB (so yes: We have switched on the feature on our instances).</p>
  
  <p>Short version is: It seems to us that a <code>$match</code> aggregation stage <strong>after</strong> a <code>$group</code> stage does not work. It never returns any results.</p>
  
  <p>This can be reproduced in the following way using any Mongo Console, assuming you are already in a valid DB (using <code>use &lt;some db&gt;</code>):</p>
  
  <p>Paste the following JavaScript (this line is optional if you created the collection via the Azure Portal; it assumes your collection is called <code>bug</code>)</p>
  
  <pre><code>db.createCollection("bug");
  </code></pre>
  
  <p>The add some documents to the collection:</p>
  
  <pre><code>db.bug.insert({ _id: 1, owner: "a", _class: "History" });
  db.bug.insert({ _id: 2, owner: "a", _class: "History" });
  db.bug.insert({ _id: 3, owner: "a", _class: "DocumentBookmark" });
  db.bug.insert({ _id: 4, owner: "a", _class: "Recyclebin" });
  db.bug.insert({ _id: 5, owner: "b", _class: "History" });
  </code></pre>
  
  <p>As you can see, the <code>owner: "a"</code> has a duplicate <code>History</code> record, which we want to query for.</p>
  
  <p>Now do the following:</p>
  
  <pre><code>db.bug.aggregate([
      { $match: { _class: "History"} }
  ]);
  </code></pre>
  
  <p>This renders the correct result:</p>
  
  <pre><code>globaldb:PRIMARY&gt; db.bug.aggregate([
  ...     { $match: { _class: "History"} }
  ... ]);
  {
      "_t" : "AggregationPipelineResponse",
      "ok" : 1,
      "waitedMS" : NumberLong(0),
      "result" : [
          {
              "_id" : 1,
              "owner" : "a",
              "_class" : "History"
          },
          {
              "_id" : 2,
              "owner" : "a",
              "_class" : "History"
          },
          {
              "_id" : 5,
              "owner" : "b",
              "_class" : "History"
          }
      ]
  }
  </code></pre>
  
  <p>Now add a <code>$group</code> stage with a <code>count</code> to find the number of records per owner:</p>
  
  <pre><code>db.bug.aggregate([
      { $match: { _class: "History"} },
      { $group: { _id: "$owner", count: { $sum: 1 }}}
  ]);
  </code></pre>
  
  <p>This also returns a correct result:</p>
  
  <pre><code>globaldb:PRIMARY&gt; db.bug.aggregate([
  ...     { $match: { _class: "History"} },
  ...     { $group: { _id: "$owner", count: { $sum: 1 }}}
  ... ]);
  {
      "_t" : "AggregationPipelineResponse",
      "ok" : 1,
      "waitedMS" : NumberLong(0),
      "result" : [
          {
              "_id" : "a",
              "count" : NumberLong(2)
          },
          {
              "_id" : "b",
              "count" : NumberLong(1)
          }
      ]
  }
  </code></pre>
  
  <p>Now we want to match for records with a <code>count</code> greater than 1:</p>
  
  <pre><code>db.bug.aggregate([
      { $match: { _class: "History"} },
      { $group: { _id: "$owner", count: { $sum: 1 }}},
      { $match: { count: { $gt: 1 }}}
  ]);
  </code></pre>
  
  <p>This returns an <strong>empty</strong> result set:</p>
  
  <pre><code>globaldb:PRIMARY&gt; db.bug.aggregate([
  ...     { $match: { _class: "History"} },
  ...     { $group: { _id: "$owner", count: { $sum: 1 }}},
  ...     { $match: { count: { $gt: 1 }}}
  ... ]);
  {
      "_t" : "AggregationPipelineResponse",
      "ok" : 1,
      "waitedMS" : NumberLong(0),
      "result" : [ ]
  }
  </code></pre>
  
  <h3>The same on Mongo DB</h3>
  
  <p>Now, to verify these queries are actually correct, I tried it using the <code>mongo:3.4</code> docker image. The following code will start a new Mongo DB instance on your local machine, so that you can try it out yourself:</p>
  
  <pre><code>$ docker run --name mongobug -d mongo:3.4
  ad3010da255b7c15a464fa21ff6519799a5c16cb8af62a0ea564a95780900491
  $ docker exec -it mongobug mongo
  MongoDB shell version v3.4.10
  connecting to: mongodb://127.0.0.1:27017
  MongoDB server version: 3.4.10
  Welcome to the MongoDB shell.
  &gt;
  </code></pre>
  
  <p>Then we'll do the same as with Cosmos above; inside the Mongo Shell, run the following commands:</p>
  
  <pre><code>db.createCollection("bug")
  </code></pre>
  
  <p>Then insert the test data:</p>
  
  <pre><code>db.bug.insert({ _id: 1, owner: "a", _class: "History" });
  db.bug.insert({ _id: 2, owner: "a", _class: "History" });
  db.bug.insert({ _id: 3, owner: "a", _class: "DocumentBookmark" });
  db.bug.insert({ _id: 4, owner: "a", _class: "Recyclebin" });
  db.bug.insert({ _id: 5, owner: "b", _class: "History" });
  </code></pre>
  
  <p>And now you can see that the query which returned an empty set in fact returns a non-empty aggregate result when running the following aggregate query:</p>
  
  <pre><code>db.bug.aggregate([
      { $match: { _class: "History"} },
      { $group: { _id: "$owner", count: { $sum: 1 }}},
      { $match: { count: { $gt: 1 }}}
  ]);
  </code></pre>
  
  <p>The result is the expected:</p>
  
  <pre><code>&gt; db.bug.aggregate([
  ...     { $match: { _class: "History"} },
  ...     { $group: { _id: "$owner", count: { $sum: 1 }}},
  ...     { $match: { count: { $gt: 1 }}}
  ... ]);
  { "_id" : "a", "count" : 2 }
  </code></pre>
  
  <h3>Extra bonus reading</h3>
  
  <p>I also tried to first group by <code>owner</code> and <code>_class</code> in a joint group and then <code>$match</code>; this is obviously by far a much more expensive operation, as Mongo has to group over the entire collection, and not only over already filtered items.</p>
  
  <p>But, unfortunately, this also rendered an empty result, while it worked on the local Mongo docker image:</p>
  
  <pre><code>db.bug.aggregate([
      { $group: { _id: { owner: "$owner", _class: "$_class" }, count: { $sum: 1 } } },
      { $match: { "_id._class": "History", count: { $gt: 1 } } }
  ]);
  </code></pre>
  
  <p>Result on Cosmos:</p>
  
  <pre><code>globaldb:PRIMARY&gt; db.bug.aggregate([
  ...     { $group: { _id: { owner: "$owner", _class: "$_class" }, count: { $sum: 1 } } },
  ...     { $match: { "_id._class": "History", count: { $gt: 1 } } }
  ... ]);
  {
      "_t" : "AggregationPipelineResponse",
      "ok" : 1,
      "waitedMS" : NumberLong(0),
      "result" : [ ]
  }
  </code></pre>
  
  <p>Result on Mongo DB:</p>
  
  <pre><code>&gt; db.bug.aggregate([
  ...     { $group: { _id: { owner: "$owner", _class: "$_class" }, count: { $sum: 1 } } },
  ...     { $match: { "_id._class": "History", count: { $gt: 1 } } }
  ... ]);
  { "_id" : { "owner" : "a", "_class" : "History" }, "count" : 2 }
  </code></pre>
  
  <p>Weird.</p>
  
  <h3>tl;dr</h3>
  
  <p>Does Cosmos DB have a bug which does not allow for running <code>$match</code> aggregates after <code>$group</code> stages?</p>