Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-73253

Better path tracking when renaming nested/compound grouping fields

    • Query Optimization
    • Fully Compatible
    • v7.0
    • QO 2023-05-15, QO 2023-05-29, QO 2023-06-12, QO 2023-06-26, QO 2023-07-10, QO 2023-07-24
    • 38

      Via SERVER-34741, all of the following pipelines will have the $match predicate pulled ahead of the $group to leverage an index and minimize the amount of work performed:

      //Match directly on single field group
      db.foo.aggregate([
            { '$group': { _id: '$city', c: { '$sum': 1 } } },
            { '$match': { _id: 'Austin' } }
      ]);
      //Match on directly single field nested group value
      db.foo.aggregate([
            { '$group': { _id: { c: '$city' }, c: { '$sum': 1 } } },
            { '$match': { '_id.c': 'Austin' } }
      ]);
      //Match directly on one field in a compound grouping
      db.foo.aggregate([
            {
              '$group': { _id: { c: '$city', s: '$state' }, c: { '$sum': 1 } }
            },
            { '$match': { '_id.c': 'Austin' } }
      ]);
      //Match on a renamed single field grouping
      db.foo.aggregate([
            { '$group': { _id: '$city', c: { '$sum': 1 } } },
            { '$project': { cityName: '$_id', c: 1 } },
            { '$match': { cityName: 'Austin' } }
      ]);
      

      Introducing a $project specifically when the there is a nested or compound grouping prevents the optimization from applying:

      //Match on a renamed single field grouping, but with nested names
      db.foo.aggregate([
            { '$group': { _id: { c: '$city' }, c: { '$sum': 1 } } },
            { '$project': { cityName: '$_id.c' } },
            { '$match': { cityName: 'Austin' } }
      ]);
      //Match on a renamed compound grouping (necessarily with nested names)
      db.foo.aggregate([
            {
              '$group': { _id: { c: '$city', s: '$state' }, c: { '$sum': 1 } }
            },
            { '$project': { cityName: '$_id.c' } },
            { '$match': { cityName: 'Austin' } }
      ]);
      

      This ticket will enhance our projection analysis to handle projections that rename nested field names from the grouping _id field.

            Assignee:
            matt.olma@mongodb.com Matt Olma
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: