MongoDB Query to Find Records with Keys Containing Dots

In MongoDB, field names (or keys) in documents are strings that uniquely identify values stored within those documents. While MongoDB allows field names to contain dots (e.g., “user.name”), using keys with dots can be challenging when querying documents due to the way MongoDB interprets dots in query operations.

In this article, We will learn about How to Perform MongoDB Query to Find Records with Keys Containing Dots by understanding various examples in detail.

MongoDB Query to Find Records with Keys Containing Dots

• In MongoDB, keys (or field names) in documents are strings that uniquely identify values stored within documents.
• While MongoDB allows keys to contain dots (e.g. “user.name”), using keys with dots can pose challenges when querying documents due to the way MongoDB interprets dots in query operations.
• When querying documents that have keys containing dots MongoDB interprets these dots as indicating nested fields.
• This can lead to issues when we’re trying to query these keys directly. Due to the interpretation of dots, querying keys with dots directly may not work as expected.
• MongoDB may interpret the dot as a separator for nested fields which could lead to unexpected behavior in queries.

Examples of MongoDB Query to Find Records with Keys Containing Dots

Let’s set up an Environment:

To understand MongoDB Query to Find Records with Keys Containing Dots we need a collection and some documents on which we will perform various operations and queries. Here we will consider a collection called users which contains the information shown below:

[
  {
    _id: ObjectId('60f3fa239b8f2900702301a1'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Chris', surname: 'Doe', lastname: 'Smith' }
        }
      },
      Country: 'Canada'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a2'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'David', surname: 'Doe', lastname: 'Smith' }
        }
      },
      Country: 'Australia'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a3'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Alice', surname: 'Smith', lastname: 'Johnson' }
        }
      },
      Country: 'Germany'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a4'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Bob', surname: 'Johnson', lastname: 'Brown' }
        }
      },
      Country: 'France'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a5'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Eve', surname: 'Brown', lastname: 'Williams' }
        }
      },
      Country: 'Japan'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a6'),
    user: {
      details1: {
        otherdetails: {
          Name: {
            FirstName: 'Frank',
            surname: 'Williams',
            lastname: 'Jones'
          }
        }
      },
      Country: 'Brazil'
    }
  }
]

Example 1: Find documents where the user’s name is “Alice Smith Johnson”

Find users whose first name is “Alice” and surname is “Smith” in a nested document structure.

db.users.find({ "user.details1.otherdetails.Name.FirstName": "Alice", "user.details1.otherdetails.Name.surname": "Smith" });

Output:

{
    "_id": ObjectId("60f3fa239b8f2900702301a3"),
    "user": {
        "details1": {
            "otherdetails": {
                "Name": {
                    "FirstName": "Alice",
                    "surname": "Smith",
                    "lastname": "Johnson"
                }
            }
        },
        "Country": "Germany"
    }
}

Explanation: This query searches for documents in the “users” collection where the nested field “user.details1.otherdetails.Name.FirstName” is equal to “Alice” and the nested field “user.details1.otherdetails.Name.surname” is equal to “Smith”.

Example 2: Find Documents Where otherdetails is Present or Not

Find the first two documents in the “users” collection that have nested field “user.details1.otherdetails” present

 db.users.find({ "user.details1.otherdetails": { $exists: true } }).limit(2)

Output:

[
  {
    _id: ObjectId('60f3fa239b8f2900702301a1'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Chris', surname: 'Doe', lastname: 'Smith' }
        }
      },
      Country: 'Canada'
    }
  },
  {
    _id: ObjectId('60f3fa239b8f2900702301a2'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'David', surname: 'Doe', lastname: 'Smith' }
        }
      },
      Country: 'Australia'
    }
  }
]

Explanation: This query finds documents in the “users” collection where the nested field “user.details1.otherdetails” exists and limits the result to 2 documents.

Example 3: Find Documents Where the surname is “Brown”

Retrieve documents from the “users” collection where the surname in the nested “Name” field under “otherdetails” is “Brown”.

db.users.find({ "user.details1.otherdetails.Name.surname": "Brown"});

Output:

[
  {
    _id: ObjectId('60f3fa239b8f2900702301a5'),
    user: {
      details1: {
        otherdetails: {
          Name: { FirstName: 'Eve', surname: 'Brown', lastname: 'Williams' }
        }
      },
      Country: 'Japan'
    }
  }
]

Conclusion

Overall, Querying MongoDB documents with keys containing dots requires special handling of field names to ensure accurate retrieval of data. By using quotes or bracket notation, MongoDB users can effectively query documents with nested or complex field structures that include keys with dots. In this article, we explored how to query MongoDB to find records with keys containing dots, providing detailed explanations, examples, and outputs to assist beginners in mastering this aspect of MongoDB querying.