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

Positional path component in match language should not act as both an array index and a field name for a single document

    XMLWordPrintable

Details

    • ALL

    Description

      Issue Status as of April 29, 2020

      ISSUE SUMMARY

      Mongo Query Language (MQL) allows referencing both subfields and array positional elements in a convenient but ambiguous way. When the data model varies between array elements or when field names inside of arrayed subdocuments are numbers (e.g., "2"), this can make it difficult to:

      • check for the existence or non-existence of an array element.
      • filter on values at a specific position in an array

      When querying for numeric field paths on arrays that contain subdocuments, more results are returned than may be expected.

      For example, the query {"a.2": 99} matches all of:

      {"a": { "2": 99}}
      {"a": [1, 32, 99]}
      {"a" : [{ "2" : 99}, 1, 2]}
      

      Note how {"a": [{"2": 99}, 1, 2]} matches because a.2 is 99, independent of the subdocument's position in the array.

      The following documents would all match {"a.2": {$eq: null}}:

      {"a": [{"2":null}]}
      {"a": [{}]}
      {"a": [1, 2, {}]}
      

      That is, a document matches the {"a.2": {$eq: null}} query if ANY of the following are true:

      • the second element of the a array does not exist
      • the second element of the a array exists and is explicitly null
      • ANY document in the a array does not have a "2" field
      • ANY document in the a array has a 2 field that is explicitly null.

      There isn't currently a way to disambiguate this behavior without backwards-breaking changes to MQL, but this issue is under investigation.

      WORKAROUNDS

      If an array will contain any subdocuments, use caution when storing field names that are numbers. Consider avoiding the combination of subfield queries and positional queries on arrays. As always, test that query results are correct for your application's logic.

      If it is necessary for application logic to mix both query types, and unexpected results occur, the following workarounds are available:

      • If testing the existence of fields in array subdocuments, use $elemMatch even though it is technically not supposed to be necessary for query filters on single array fields.
      • If querying array elements by position, use the aggregation framework to take advantage of the $arrayElemAt operator.

      AFFECTED VERSIONS

      This ambiguity affects all release versions of MongoDB.

      Original description

      Suppose we have the following documents in our collection:

      {
           "_id" : ObjectId("585399ed6ba942932efdc4ce"), 
           "item" : [ 1, 2, 3, null ]
      },
      {
           "_id" : ObjectId("585399ed6ba942932efdc4cf"), 
          "item" : [ 4, 5, 6, 7 ] 
      },
      {
          "_id" : ObjectId("58539a3c6ba942932efdc4d0"), 
         "item" : [ 8, 9, 10, { "another_item" : 11 } ] 
      }
      

      The following query:

      db.coll.find({"item.3": null})
      

      yields:

      {
           "_id" : ObjectId("585399ed6ba942932efdc4ce"), 
          "item" : [ 1, 2, 3, null ] 
      }
      { 
          "_id" : ObjectId("58539a3c6ba942932efdc4d0"), 
          "item" : [ 8, 9, 10, { "another_item" : 11 } ] 
      }
      

      As we can see the document with sub-document in the array at index 3 is returned as well.

      Of course to get the expected result, one has to use the `$type` operator to match the BSON type.

      Is this the expected result? If yes can we add it to the documentation?

      source: MongoDB not querying `null` within a array at a given posision correctly

      Attachments

        Issue Links

          Activity

            People

              backlog-query-optimization Backlog - Query Optimization
              styvane Styvane Soukossi
              Votes:
              0 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated: