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

Multikey sorting of nested array element, selected with $elemMatch

    • Type: Icon: Question Question
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None

      Given 2 documents and index:

      db.elsort.drop();
      db.elsort.insert({_id: 1, b: [{c: 2, d: 3}, {c: 3, d: 1}]});
      db.elsort.insert({_id: 2, b: [{c: 2, d: 2}, {c: 3, d: 2}]});
      db.elsort.ensureIndex({'b.c': 1, 'b.d': 1})
      

      Need to get descending sorted documents using multikey index on array elements:

      db.elsort.find({b: {$elemMatch: {c: 2}}}).sort({'b.c': -1, 'b.d': -1})
      
      { "_id" : 2, "b" : [ { "c" : 2, "d" : 2 }, { "c" : 3, "d" : 2 } ] }
      { "_id" : 1, "b" : [ { "c" : 2, "d" : 3 }, { "c" : 3, "d" : 1 } ] }
      

      Documents are sorted not as desired. Sorting is wrapped globally:

      db.elsort.find({b: {$elemMatch: {c: 2}}}).sort({'b.c': -1, 'b.d': -1}).explain().queryPlanner.winningPlan
      
      {
        "stage" : "SORT",
        "sortPattern" : {
          "b.c" : -1,
          "b.d" : -1
        },
        "inputStage" : {
          "stage" : "SORT_KEY_GENERATOR",
          "inputStage" : {
            "stage" : "FETCH",
            "filter" : {
              "b" : {
                "$elemMatch" : {
                  "c" : {
                    "$eq" : 2
                  }
                }
              }
            },
            "inputStage" : {
              "stage" : "IXSCAN",
              "keyPattern" : {
                "b.c" : 1,
                "b.d" : 1
              },
              "indexName" : "b.c_1_b.d_1",
              "isMultiKey" : true,
              "multiKeyPaths" : {
                "b.c" : [
                  "b"
                ],
                "b.d" : [
                  "b"
                ]
              },
              "isUnique" : false,
              "isSparse" : false,
              "isPartial" : false,
              "indexVersion" : 2,
              "direction" : "forward",
              "indexBounds" : {
                "b.c" : [
                  "[2.0, 2.0]"
                ],
                "b.d" : [
                  "[MinKey, MaxKey]"
                ]
              }
            }
          }
        }
      }
      

      The desired effect for ascending sort can be reached by specifying a hint:

      db.elsort.find({b: {$elemMatch: {c: 2}}}).hint({'b.c': 1, 'b.d': 1})
      
      { "_id" : 2, "b" : [ { "c" : 2, "d" : 2 }, { "c" : 3, "d" : 2 } ] }
      { "_id" : 1, "b" : [ { "c" : 2, "d" : 3 }, { "c" : 3, "d" : 1 } ] }
      
      db.elsort.find({b: {$elemMatch: {c: 2}}}).hint({'b.c': 1, 'b.d': 1}).explain().queryPlanner.winningPlan
      
      {
        "stage" : "FETCH",
        "filter" : {
          "b" : {
            "$elemMatch" : {
              "c" : {
                "$eq" : 2
              }
            }
          }
        },
        "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {
            "b.c" : 1,
            "b.d" : 1
          },
          "indexName" : "b.c_1_b.d_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "b.c" : [
              "b"
            ],
            "b.d" : [
              "b"
            ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "b.c" : [
              "[2.0, 2.0]"
            ],
            "b.d" : [
              "[MinKey, MaxKey]"
            ]
          }
        }
      }
      

      It seems there is no way to force "direction" : "backward" for achieving an effect of descending sort.
      Is there a way to achieve the desired effect?

            Assignee:
            nick.brewer Nick Brewer
            Reporter:
            tarquas Taras
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: