title: Query document count by multiple ranges returning range start/end with matching element count
body: <p>I've been trying to create a query on documents like these:</p>
  <pre><code>[
      {
          &quot;timestamp&quot;: new ISODate('2020-01-01T00:00:00'),
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_A&quot;
      },
      {
          &quot;timestamp&quot;: new ISODate('2021-01-01T00:00:00'),
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_A&quot;
      },
      {
          &quot;timestamp&quot;: new ISODate('2022-01-01T00:00:00'),
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_B&quot;
      },
      {
          &quot;timestamp&quot;: new ISODate('2021-01-01T00:00:00'),
          &quot;objectId&quot;: &quot;Id_B&quot;,
          &quot;locationId&quot;: &quot;Location_B&quot;
      },
      {
          &quot;timestamp&quot;: new ISODate('2022-01-01T00:00:00'),
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_A&quot;
      }
  ]
  </code></pre>
  <p>given multiple &quot;and&quot; queries, I want to count the matching documents per range</p>
  <pre><code>[$or: [
      { $and: [{
          &quot;timestamp&quot;: {$gte: new ISODate('2020-01-01T00:00:00'),
                        $lt: new ISODate('2020-12-31T00:00:00'),
          },
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_A&quot;}]},
      { $and: [{
          &quot;timestamp&quot;: {$gte: new ISODate('2020-01-01T00:00:00'),
                        $lt: new ISODate('2022-12-31T00:00:00'),
          },
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_A&quot;}]},
      { $and: [{
          &quot;timestamp&quot;: {$gte: new ISODate('2022-01-01T00:00:00'),
                        $lt: new ISODate('2022-12-31T00:00:00'),
          },
          &quot;objectId&quot;: &quot;Id_A&quot;,
          &quot;locationId&quot;: &quot;Location_B&quot;}]}
      ]
  ]
  </code></pre>
  <p>I want to map the counts to a result structure that looks like this</p>
  <pre><code>[
      {&quot;objectId&quot;: &quot;Id_A&quot;, &quot;locationId&quot;: &quot;Location_A&quot;, &quot;rangeStart:&quot;: new ISODate('2020-01-01T00:00:00'), &quot;rangeEnd&quot;: new ISODate('2020-12-31T00:00:00'), &quot;count&quot;: 1},
      {&quot;objectId&quot;: &quot;Id_A&quot;, &quot;locationId&quot;: &quot;Location_A&quot;, &quot;rangeStart:&quot;: new ISODate('2020-01-01T00:00:00'), &quot;rangeEnd&quot;: new ISODate('2022-12-31T00:00:00'), &quot;count&quot;: 3},
      {&quot;objectId&quot;: &quot;Id_A&quot;, &quot;locationId&quot;: &quot;Location_B&quot;, &quot;rangeStart:&quot;: new ISODate('2022-01-01T00:00:00'), &quot;rangeEnd&quot;: new ISODate('2022-12-31T00:00:00'), &quot;count&quot;: 1},
  ]
  </code></pre>
  <p>Aggregations I looked at so far:</p>
  <ul>
  <li>bucket</li>
  <li>facet</li>
  <li>group</li>
  </ul>
  <p>But I still can't figure it out.
  What would your approach be?</p>
  <p><strong>Update 1</strong>
  The rangeKeys are not assigned correctly with the solution I derived from @Takis suggestion:</p>
  <p>I get the following result:</p>
  <pre><code>{ 
      &quot;_id&quot; : {
          &quot;objectId&quot; : &quot;objectA&quot;, 
          &quot;locationId&quot; : &quot;locationA&quot;, 
          &quot;rangeKey&quot; : &quot;UUID2&quot;, 
          &quot;count&quot; : 1.0
      }
  }
  </code></pre>
  <p>while I expect the following result:</p>
  <pre><code>{ 
      &quot;_id&quot; : {
          &quot;objectId&quot; : &quot;objectA&quot;, 
          &quot;locationId&quot; : &quot;locationA&quot;, 
          &quot;rangeKey&quot; : &quot;UUID1&quot;,
          &quot;count&quot; : 1.0
      }
  }, {
      
      &quot;_id&quot; : {
          &quot;objectId&quot; : &quot;objectB&quot;, 
          &quot;locationId&quot; : &quot;locationA&quot;, 
          &quot;rangeKey&quot; : &quot;UUID2&quot;,
          &quot;count&quot; : 0.0
      }
  }
  </code></pre>
  <p>This is the query I built</p>
  <pre><code>db.createCollection(&quot;object_location_tracking&quot;)
  db.getCollection(&quot;object_location_tracking&quot;).insertMany([
      {
          _id: &quot;1&quot;,
          locationId: &quot;locationA&quot;,
          objectId: &quot;objectA&quot;,
          timestamp: ISODate(&quot;2020-01-01T00:00:00Z&quot;)
      },
      {
          _id: &quot;2&quot;,
          locationId: &quot;locationB&quot;,
          objectId: &quot;objectA&quot;,
          timestamp: ISODate(&quot;2020-01-01T00:00:00Z&quot;)
      },
      {
          _id: &quot;3&quot;,
          locationId: &quot;locationA&quot;,
          objectId: &quot;objectB&quot;,
          timestamp: ISODate(&quot;2019-01-01T00:00:00Z&quot;)
      },
      {
          _id: &quot;4&quot;,
          locationId: &quot;locationB&quot;,
          objectId: &quot;objectB&quot;,
          timestamp: ISODate(&quot;2020-01-01T00:00:00Z&quot;)
      }
  ]);
  db.getCollection(&quot;object_location_tracking&quot;).aggregate(
      [
          { 
              &quot;$match&quot; : { 
                  &quot;locationId&quot; : &quot;locationA&quot;, 
                  &quot;$or&quot; : [
                      { 
                          &quot;$and&quot; : [
                              { 
                                  &quot;objectId&quot; : &quot;objectA&quot;
                              }, 
                              { 
                                  &quot;timestamp&quot; : { 
                                      &quot;$gte&quot; : ISODate(&quot;2020-01-01T00:00:00.000+0000&quot;)
                                  }
                              }, 
                              { 
                                  &quot;timestamp&quot; : { 
                                      &quot;$lt&quot; : ISODate(&quot;2022-01-01T00:00:00.000+0000&quot;)
                                  }
                              }
                          ]
                      }, 
                      { 
                          &quot;$and&quot; : [
                              { 
                                  &quot;objectId&quot; : &quot;objectB&quot;
                              }, 
                              { 
                                  &quot;timestamp&quot; : { 
                                      &quot;$gte&quot; : ISODate(&quot;2020-01-01T00:00:00.000+0000&quot;)
                                  }
                              }, 
                              { 
                                  &quot;timestamp&quot; : { 
                                      &quot;$lt&quot; : ISODate(&quot;2022-01-01T00:00:00.000+0000&quot;)
                                  }
                              }
                          ]
                      }
                  ]
              }
          }, 
          { 
              &quot;$group&quot; : { 
                  &quot;_id&quot; : { 
                      &quot;objectId&quot; : &quot;$objectId&quot;, 
                      &quot;locationId&quot; : &quot;$locationId&quot;, 
                      &quot;rangeKey&quot; : { 
                          &quot;$switch&quot; : { 
                              &quot;branches&quot; : [
                                  { 
                                      &quot;case&quot; : { 
                                          &quot;$and&quot; : [
                                              { 
                                                  &quot;$gte&quot; : [
                                                      &quot;$timestamp&quot;, 
                                                      ISODate(&quot;2020-01-01T00:00:00.000+0000&quot;)
                                                  ]
                                              }, 
                                              { 
                                                  &quot;$lt&quot; : [
                                                      &quot;$timestamp&quot;, 
                                                      ISODate(&quot;2022-01-01T00:00:00.000+0000&quot;)
                                                  ]
                                              }, 
                                              { 
                                                  &quot;objectId&quot; : &quot;objectB&quot;
                                              }, 
                                              { 
                                                  &quot;locationId&quot; : &quot;locationA&quot;
                                              }
                                          ]
                                      }, 
                                      &quot;then&quot; : &quot;UUDI2&quot;
                                  }, 
                                  { 
                                      &quot;case&quot; : { 
                                          &quot;$and&quot; : [
                                              { 
                                                  &quot;$gte&quot; : [
                                                      &quot;$timestamp&quot;, 
                                                      ISODate(&quot;2020-01-01T00:00:00.000+0000&quot;)
                                                  ]
                                              }, 
                                              { 
                                                  &quot;$lt&quot; : [
                                                      &quot;$timestamp&quot;, 
                                                      ISODate(&quot;2022-01-01T00:00:00.000+0000&quot;)
                                                  ]
                                              }, 
                                              { 
                                                  &quot;objectId&quot; : &quot;objectA&quot;
                                              }, 
                                              { 
                                                  &quot;locationId&quot; : &quot;locationA&quot;
                                              }
                                          ]
                                      }, 
                                      &quot;then&quot; : &quot;UUID2&quot;
                                  }
                              ], 
                              &quot;default&quot; : &quot;0&quot;
                          }
                      }, 
                      &quot;count&quot; : { 
                          &quot;$sum&quot; : 1.0
                      }
                  }
              }
          }
      ], 
      { 
          &quot;allowDiskUse&quot; : true
      }
  );
  </code></pre>